Report - group line

[nianko]

Hi,

I have 2 small problems with my report. I have a list of Clients (header) and a list of factories. Each client can visit several factories, several times. The report template is as follows:

Client 1
usine X
- visit on 10/09/2010
- visit on 07/08/2010
- visit on le 08/05/2009
usine Z
- visit on 10/07/2010
client 2
usine X
- visit on 12/04/2010
- visit on 13/08/2010
usine Z
- visit on 16/09/2010
- visit on 30/09/2010

... and so on

How should I modify my query so that the only most recent visit only appears on the report?

Also, how to force a page-break before a new CLient name. I don't want to have just the client name a t the bottom of one page and the list of factories visited at the beginning of the next page.

My SQL query is :

[FONT=Verdana]SELECT tblCLIENTS.Date, tblCLIENTS.Client_ID, tblCLIENTS.Name, tblFACTORY.Factory_ID, tblVISITS.Currency, tblVISITS.Factory, tblVISITS.Contact, tblVISITS.Comments[/FONT]
[FONT=Verdana]FROM tblFACTORY RIGHT JOIN (tblCLIENTS RIGHT JOIN tblVISITS ON tblCLIENTS.Client_ID = tblVISITS.Client) ON tblFACTORY.Factory_ID = tblVISITES.Factory ORDER BY tblVISITS.Date DESC , tblCLIENTS.Name;[/FONT]

Also one more thing. My CLients are not sorted by name althought I have the "ORDER BY tblCLIENTS.Name". Why??
Thansk for your help

nianko

 

[pbaldy]

This

http://support.microsoft.com/kb/153747/en-us

or this

http://www.baldyweb.com/LastValue.htm

For a report, sorting should be set up in Sorting and Grouping. In your query, you have date before name, so the primary sort will be on date.

 

[nianko]

Many thx,

I'll try the second one which corresponds to my case and mark as "solved" when I'm done.