sorting by Date and Name 2

[bluegi]

I have been trying to figure out how to sort a report I created and having some difficulty. Hope someone can help.

This is the situation, I have a report that is currently sorted as follows.

Name1 Address1 Phone1 DateOfService
8/1/08
6/1/08
Name2 Address2 Phone2 DateOfService
9/1/08
7/1/08
Name3 Address3 Phone3 DateOfService
10/1/08
5/1/08

The above is not the sort order I really want. The following is the sort order i would like.

Name3 Address3 Phone3 DateOfService
10/1/08
5/1/08
Name1 Address1 Phone1 DateOfService
8/1/08
6/1/08
Name2 Address2 Phone2 DateOfService
9/1/08
7/1/08

Basically I would like to sort by DateOfService, would I be able to do that.

I tried using Sort and Grouping in Access but I can't figure out how to sort according to DateOfService without the report incorrect display.

For example, in the sort and grouping, if I sort according to DateOfService then followed by Name, the report I get will contain multiple names with DateOfService, What I really would like to acheive is have the report show the latest DateOfService first for each name without the name repeated later on in the report.

For Example, I don't want it like this:
Name3 Address3 Phone3 DateOfService
10/1/08
Name2 Address2 Phone2 DateOfService
9/1/08
Name1 Address1 Phone1 DateOfService
8/1/08
Name2 Address2 Phone2 DateOfService
7/1/08
Name1 Address1 Phone1 DateOfService
6/1/08
Name3 Address2 Phone2 DateOfService
5/1/08

Hope this is clear, if not please let me know
Thanks for any help.

 

[Remou]

Have you considered setting up your query grouped by Name, Address, Phone and Max(DateOfService)?

 

[dhookom]

Using Remou's suggestion, you would create the totals query and join it to your report's record source query. This would allow you to set the sorting and grouping to the MaxOfDateOfService.

Duane
Hook'D on Access
MS Access MVP

 

[bluegi]

thanks for the replies

how would you create a totals query, i have never done that

 

[dhookom]

A totals query is like a select query but you click the sigma icon on the command bar. This opens the totals line in the grid where you can select "totals" like Group By, Sum, Min, and Max. You would group by the field or fields that uniquely identify your entity that needs to be sorted by. Select the date field and choose Max.

Duane
Hook'D on Access
MS Access MVP

 

[bluegi]

thanks for that info, however when i selected the date field and selected Max the report still did not appear the way I want it.

I have posted a picture of a report that I currently have.

As you can see the report is currently sorted by Pharmacy Name Ascending order.

What I would like is to sort according to Date of Service in ascending order.

Is this possible?

Thanks for any help.

 

[dhookom]

You need to get the Max of the date field into the report's record source. Did you create a new, separate totals query that groups by the name (or whatever) field(s) and finds the max of the date?

If you can't figure out the solution, come back with the field names you are currently grouping by in the report, the field of the date, and your report's record source SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[hneal98]

I think you should try the following:

Group by DateOfService, Name, Address, Phone
Order by DateOfService desc

 

[bluegi]

i don't think I can figure it out dhookom,

Sorting and Grouping
----------------------------------------------
Field/Expression Sort Order

Pharmacy Ascending
DateOfService Ascending
ClientAccount# Ascending


SQL View

SELECT DISTINCTROW Scheduling.[ScheduleReference#], Scheduling.[ClientAccount#], Scheduling.DateOfService, Scheduling.TimeOfService, Scheduling.Name, Scheduling.DateOfScheduling, Scheduling.ScheduleName, Scheduling.Note, [Client Data].Pharmacy, [Client Data].Telephone, [Client Data].FaxNumber, [Client Data].State, [Client Data].Gender, [Client Data].ContactName, ([DateOfService]+180) AS Expr1
FROM [Client Data] INNER JOIN Scheduling ON [Client Data].[ClientAccount#] = Scheduling.[ClientAccount#]
GROUP BY Scheduling.[ScheduleReference#], Scheduling.[ClientAccount#], Scheduling.DateOfService, Scheduling.TimeOfService, Scheduling.Name, Scheduling.DateOfScheduling, Scheduling.ScheduleName, Scheduling.Note, [Client Data].Pharmacy, [Client Data].Telephone, [Client Data].FaxNumber, [Client Data].State, [Client Data].Gender, [Client Data].ContactName, ([DateOfService]+180)
HAVING (((Scheduling.DateOfService) Between [Forms]![frmClientsReportGenerator]![StartDate] And [Forms]![frmClientsReportGenerator]![EndDate]));

 

[dhookom]

Create a totals query that has a SQL statement like:

SELECT [ClientAccount#], Max(DateOfService) as MaxServDate
FROM Scheduling
GROUP BY [ClientAccount#];

Then add this query to your above query design and join the [ClientAccount#] fields. Remove the Totals from your main report since it shouldn't be necessary.

Your report will now have a MaxServDate that you can sort by. Your primary sorting I expect would be pharmacy, then MaxServDate, then [ClientAccount#], and finally DateOfService.

Duane
Hook'D on Access
MS Access MVP

 

[bluegi]

its fixed now, thanks for everyones help

 

[Remou]

A good way to thank someone in Tek-Tips is to give them a star. You can do this by clicking:


Thank dhookom
for this valuable post!

Which you will see above.

 

[Remou]

... and one from me for perseverance.