How to filter report to show certain subgroups

G

Guest

I am a new, self taught, Access user, and I am wondering if someone could
walk me through how to filter a report to show only the subgroups that meet a
certain criteria?

For example I have a report that is grouped by sales person, then by
contractor, then the sales are listed by date under that. I want to be able
to have a report that shows by sales person which contractors have not had
sales with us in the last six months. So in other words if we have had a
sale/sales to them since 10/1/05 I do not want them to show up on the report,
but if we have not than I want to see the contractor and when we last sold to
them.

Sample Report

Sales Person 1
Contractor A
9/30/05 Sold Materials $500

Contractor C
3/15/04 Sold Materials $400
 
D

Duane Hookom

I would start by creating a group by query that list the contractors with
their Max sales date.

SELECT ContractorID, Max([OrderDate]) as LastOrder
FROM tblSales
HAVING Max([OrderDate]) < DateAdd("m",-6,Date());

You can then use this query in your report's record source.
 
M

Marshall Barton

AverageUser said:
I am a new, self taught, Access user, and I am wondering if someone could
walk me through how to filter a report to show only the subgroups that meet a
certain criteria?

For example I have a report that is grouped by sales person, then by
contractor, then the sales are listed by date under that. I want to be able
to have a report that shows by sales person which contractors have not had
sales with us in the last six months. So in other words if we have had a
sale/sales to them since 10/1/05 I do not want them to show up on the report,
but if we have not than I want to see the contractor and when we last sold to
them.

Sample Report

Sales Person 1
Contractor A
9/30/05 Sold Materials $500

Contractor C
3/15/04 Sold Materials $400


In the report's record source query, try just setting the
saledate field's criteria to:
<#10/1/05#

Your situation is probably more complex than that, but you
will have to explain more about what you need to do before
anyone will be able to provide specific suggestions. Try
the above first and then figure out what else you need once
you get that working.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top