Group by 2 different fields

[Rich24]

I have a report that lists all children who received a hearing or vision test within a prompted date range. I would like to create another report using the same criteria, but group by Facility from the Vision_Results and Hearing_Results tables. I can’t figure out how to group by 2 different fields.

Here’s my table setup:

Table Client – ClientNum(Key), Lname, FName, DOB, and assorted parent and address fields
Table Vision_Results – Result, Date, Comments, Facility, ClientNum, RecNum(Key)
Table Hearing_Results – Result, Date, Comments, Facility, ClientNum, RecNum(Key)

Query the report runs off of:
SELECT Tbl_Client.LName, Tbl_Client.FName, Tbl_Client.DOB, Tbl_Vision_Results.Result, Tbl_Vision_Results.Date, Tbl_Vision_Results.Comments, Tbl_Hearing_Results.Result, Tbl_Hearing_Results.Date, Tbl_Hearing_Results.Comments, Tbl_Vision_Results.Facility, Tbl_Hearing_Results.Facility
FROM (Tbl_Client INNER JOIN Tbl_Hearing_Results ON Tbl_Client.ClientNum = Tbl_Hearing_Results.ClientNum) INNER JOIN Tbl_Vision_Results ON Tbl_Client.ClientNum = Tbl_Vision_Results.ClientNum
WHERE (((Tbl_Vision_Results.Date) Between [Enter Start Date: ] And [Enter End Date: ])) OR (((Tbl_Hearing_Results.Date) Between [Enter Start Date: ] And [Enter End Date: ]));

Any help would be great! Thanks!

 

[ssecca]

Form the design view of the report go to report properties and recordsource. click on the elispe button define the sorting there and then click on the "X" to close when asked to save say yes. This will save the query in the report as a sql call with the sorting you want. Do the same for the other report (using the other sorting needs) and one query drives to reports with different needs. Good luck

 

[Rich24]

Thanks for getting back to me. I'm still confused. I'm not sure what to do when I get to "click on the elispe button define the sorting there". I'm in the Query Builder, click on the Totals button to get the "Total:" row. All of the fields change to "Group By". I'm guessing I only want the 2 Facility fields to have "Group By", so I remove "Group By" on the other fields(?). When I do that an error message pops-up, "...does not include the specified expression 'LName' as part of an aggregate function". Where did I go wrong?

Thanks again.

 

[ssecca]

When grouping ALL of the choosen fields in the query need to have some type of grouping designation. That designation could be "Group By", but might also be "First", "LAst", "Expression", or "Where". All of the options are listed in the pull down on the totals line of the QBE.

 

[Rich24]

Thanks ssecca!! Sorry for the delay in getting back to you, but I had other fires burning that were higher priority. It worked!