Limiting Report Results

  • Thread starter Thread starter Sheri Emery
  • Start date Start date
S

Sheri Emery

Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number. I
would like to put together a report that returns the last
5 contact data records for each client. I have everything
except how to query to return only the most recent 5
records, in date order, which would print out on my report.
Any help would be fantastic.
Thanks,
Sheri
 
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY [MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then shows the most
recent 5 contacts.
 
Thanks - This worked beautifully.
Sheri
-----Original Message-----
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY [MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then shows the most
recent 5 contacts.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number. I
would like to put together a report that returns the last
5 contact data records for each client. I have everything
except how to query to return only the most recent 5
records, in date order, which would print out on my report.
Any help would be fantastic.
Thanks,
Sheri
Click to expand...


.
Click to expand...
 
Hi Allen,
Upon further testing, this works, except I cannot use the
word "FROM" for some reason and it splits the most 5
recent records up among the different clients. Instead of
returning 5 records for each client, it returns 4 for 1
client and 1 for another client - a total of 5. Did I
leave something out? Thanks for your help :)
Sheri
-----Original Message-----
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY [MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then shows the most
recent 5 contacts.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number. I
would like to put together a report that returns the last
5 contact data records for each client. I have everything
except how to query to return only the most recent 5
records, in date order, which would print out on my report.
Any help would be fantastic.
Thanks,
Sheri
Click to expand...


.
Click to expand...
 
Did you try creating a subreport based on that query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sheri Emery said:
Hi Allen,
Upon further testing, this works, except I cannot use the
word "FROM" for some reason and it splits the most 5
recent records up among the different clients. Instead of
returning 5 records for each client, it returns 4 for 1
client and 1 for another client - a total of 5. Did I
leave something out? Thanks for your help :)
Sheri
-----Original Message-----
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY [MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then shows the most
recent 5 contacts.


Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number. I
would like to put together a report that returns the last
5 contact data records for each client. I have everything
except how to query to return only the most recent 5
records, in date order, which would print out on my report.
Any help would be fantastic.
Thanks,
Sheri
Click to expand...
Click to expand...
Click to expand...
 
Yes - but the query seems to be grabbing the last 5
records and them dividing them among the various clients.
This subreport will only show four records for one client
and one record for another. I was hoping to have it show
the most recent 5 records for each client. Do you have
any other thoughts?
Sheri :)
-----Original Message-----
Did you try creating a subreport based on that query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Upon further testing, this works, except I cannot use the
word "FROM" for some reason and it splits the most 5
recent records up among the different clients. Instead of
returning 5 records for each client, it returns 4 for 1
client and 1 for another client - a total of 5. Did I
leave something out? Thanks for your help :)
Sheri
-----Original Message-----
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY [MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then shows the most
recent 5 contacts.


"Sheri Emery" <[email protected]>
Click to expand...
wrote
in message
Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number. I
would like to put together a report that returns the last
5 contact data records for each client. I have everything
except how to query to return only the most recent 5
records, in date order, which would print out on my report.
Any help would be fantastic.
Thanks,
Sheri
Click to expand...
Click to expand...


.
Click to expand...
 
Okay, for more detailed instructions, see:
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208822

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sheri said:
Yes - but the query seems to be grabbing the last 5
records and them dividing them among the various clients.
This subreport will only show four records for one client
and one record for another. I was hoping to have it show
the most recent 5 records for each client. Do you have
any other thoughts?
Sheri :)
-----Original Message-----
Did you try creating a subreport based on that query?

Hi Allen,
Upon further testing, this works, except I cannot use the
word "FROM" for some reason and it splits the most 5
recent records up among the different clients. Instead of
returning 5 records for each client, it returns 4 for 1
client and 1 for another client - a total of 5. Did I
leave something out? Thanks for your help :)
Sheri
-----Original Message-----
Simplest way is to use a subreport:

- Main report bound to the Client Data Table.

- Sub report bound to this query:
SELECT TOP 5 FROM [Contact Data] ORDER BY
[MyDateField] DESC, [MyID];

Under each client in the main report, the subreport then
shows the most
recent 5 contacts.


in message
Hi,
I have two tables: (1)Client Data Table and (2) Contact
Data, which are joined together by a client ID number.
I
would like to put together a report that returns the
last
5 contact data records for each client. I have
everything
except how to query to return only the most recent 5
records, in date order, which would print out on my
report.
Any help would be fantastic.
Thanks,
Sheri
Click to expand...
Click to expand...
Click to expand...
 
Back
Top