I have two patient lists with slightly different contents, as indicated by row three of the select statements shown below.   Each of the source lists has only one entry per patient, and the lists have many of the same patients.  I want to produce a single list that has all of the data from both lists, and one entry per patient.   This is my UNION query which loads the base data:

SELECT P.Patient_Number, P.First_Name, P.Middle_Initial, P.Last_Name, P.Suffix, 
P.Sex, P.Date_Of_Birth, P.Street1, P.Street2, P.City, P.State, P.Zip, P.Phone, 
P.Last_Appt, P.Provider, Null as First_Recall, Null as Recall
FROM Provider_Patient_List AS P
UNION ALL 
SELECT R.Patient_Number, R.First_Name, R.Middle_Initial, R.Last_Name, R.Suffix, 
R.Sex, R.Date_Of_Birth, R.Street1, R.Street2, R.City, R.State, R.Zip, R.Phone, 
Null as Last_Appt, R.Provider, R.First_Recall, R.Recall
FROM [Recall Patient List] AS R
ORDER BY 1;

Next, I do a GROUP BY query to consolidate the source query:

SELECT Provider_All_Patients1.Patient_Number, Provider_All_Patients1.First_Name, Provider_All_Patients1.Middle_Initial, Provider_All_Patients1.Last_Name, Provider_All_Patients1.Suffix, Provider_All_Patients1.Sex, Provider_All_Patients1.Date_Of_Birth, Provider_All_Patients1.Street1, Provider_All_Patients1.Street2, Provider_All_Patients1.City, Provider_All_Patients1.State, Provider_All_Patients1.Zip, Provider_All_Patients1.Phone, Max(Provider_All_Patients1.Last_Appt) AS Last_Appt, Max(Provider_All_Patients1.Provider) AS MaxOfProvider, Max(Provider_All_Patients1.First_Recall) AS First_Recall, Max(Provider_All_Patients1.Recall) AS Recall
FROM Provider_All_Patients1
GROUP BY Provider_All_Patients1.Patient_Number, Provider_All_Patients1.First_Name, Provider_All_Patients1.Middle_Initial, Provider_All_Patients1.Last_Name, Provider_All_Patients1.Suffix, Provider_All_Patients1.Sex, Provider_All_Patients1.Date_Of_Birth, Provider_All_Patients1.Street1, Provider_All_Patients1.Street2, Provider_All_Patients1.City, Provider_All_Patients1.State, Provider_All_Patients1.Zip, Provider_All_Patients1.Phone
ORDER BY Provider_All_Patients1.Date_Of_Birth;

In Access 2003, the duplicates are removed, because the MAX() entries combine the null values with the non-null values.   In Access 2010, rows with non-null values and null values are returned as distinct rows.   Why the difference?   Both queries are run against the same MDB file, which is in Access 2003 file format.

This raises questions about all of the databases that I had planned to convert from Access 2003 to Access 2010.

The second query is displayed below, because it does not display very well in the forum.   I used an alias to simplify it visually:

SELECT P1.Patient_Number, P1.First_Name, P1.Middle_Initial, P1.Last_Name, P1.Suffix, 
P1.Sex, P1.Date_Of_Birth, P1.Street1, P1.Street2, P1.City, P1.State, P1.Zip, P1.Phone, 
Max(P1.Last_Appt) AS Last_Appt, 
Max(P1.Provider) AS MaxOfProvider, 
Max(P1.First_Recall) AS First_Recall, Max(P1.Recall) AS Recall
FROM Provider_All_Patients1 AS P1
GROUP BY P1.Patient_Number, P1.First_Name, P1.Middle_Initial, P1.Last_Name, P1.Suffix, 
P1.Sex, P1.Date_Of_Birth, P1.Street1, P1.Street2, P1.City, P1.State, P1.Zip, P1.Phone
ORDER BY P1.Date_Of_Birth;

The source queries were pulling the same data from the same SQL database, but the GROUP BY was unreliable.   By using GROUP BY on only the Patient_number field, and using MAX() on all of the other fields, the problem went away.

Access 2003 seemed to be inconsistent on this when I did more testing, but the single GROUP BY produced the right results in both versions of the product.

Hi,

Thanks for your share, it'll be helpful to others.

Have a good time.

Regards,

George Zhao
TechNet Community Support