Group by eliminates duplicates

[RenaG]

Hi,

I am working in Access 2007.

I have a report that runs off this query:

SELECT Program.ProgramName, 
       Territory.Territory, 
       [FirstName] & ' ' & [MI] & ' ' & [LastName] AS FullName,
       Patient.MRNum, 
       Ethnicity.Ethnicity, 
       Race.Race, 
       Patient.NavigatorDt
FROM Race 
  RIGHT JOIN (Program 
     INNER JOIN (Ethnicity 
        RIGHT JOIN (Patient 
           LEFT JOIN Territory 
           ON Patient.Territory = Territory.ID) 
        ON Ethnicity.ID = Patient.EthnicityID) 
     ON Program.ID = Patient.ProgramID) 
  ON Race.ID = Patient.RaceID
GROUP BY Program.ProgramName, 
      Territory.Territory, 
      [FirstName] & ' ' & [MI] & ' ' & [LastName], 
      Patient.MRNum, 
      Ethnicity.Ethnicity, 
      Race.Race, 
      Patient.NavigatorDt
HAVING (((Patient.NavigatorDt) Between [Forms]![frmRaceEthnicityDateParm]![BegDate] And [Forms]![frmRaceEthnicityDateParm]![EndDate]))
ORDER BY Program.ProgramName, Territory.Territory, Race.Race;

I need to group by Program and territory but Access forces me to group by all the other fields too. The problem with this is a duplicate record is not displayed on the report. I need to see that record. How do I work around this?

TIA!
~RLG

 

[plog]

Is there another field that makes these duplicates not duplicates? If so, you could bring that field in, not show it, but GROUP BY it.

As a simple example, use this data:

first, last, age
John, Smith, 26
John, Smith, 26

If you GROUPED BY every field, you are left with one record, even if we are talking about 2 different John Smiths. Suppose we have more data available that allows us to distinguish between the 2 John Smiths:

first, last, age, state
John, Smith, 26, New York
John, Smith, 26, California

If we grouped on every field, but only selected, first, last and age we would be left with two rows that are identical. You have to GROUP BY every field you SELECT, but you don't have to SELECT every field you GROUP BY.

 

[RenaG]

Hi plog,

Thanks for your quick reply.

I understand what you are saying. The problem is that this is a duplicate record. I may be able to find a field that works for this situation but not for the next one (does that make sense?). So I need a solution that allows me to display truly duplicate records.

~RLG

 

[plog]

Then why do you need to GROUP BY at all?

 

[RenaG]

Because I need to group by Program name and territory. Is there a way to group by those two fields and not the rest of the selected fields?

~RLG

 

[plog]

No.

Why do you need to group by Program name and territory?

 

[RenaG]

Thank you for asking questions and getting me to thinking. I don't need to group. I just added a sort on Program and then Territory. Then in the report I group by Program, Territory and sort the patient name (for some reason, adding a sort in the expression FullName (a concatenation of firstName and lastName) in the query doesn't work). Now it works fine.

Thanks for working with me on this!

~RLG