Main Report - Sub Report - Questions

[JK]

I have a workorders database (2003) that also stores each customer's list of
loaned equipment. If the customer has not purchased product in the last 90
day's I want to display that customer and all of the equipment they have on
loan so that we can go and pick it up...

I have a main report that displays all customer contact info & a subreport
that displays all loaned equipment.

I only want to display customers that have at least one piece of equipment
on loan (subreport has value.) Then I only want to display customers that
have not puchased product in the last 90 days (there is a field in the
customer header section called "Date Last Purchased."

Recap:
Subreport must have at least one piece of loaned equipment.
[DateLastPuchased] must be greater than 90 days.

The main report and subreport are all setup. I just don't know how to add
the criteria mentioned above. A point in the right direction would be a huge
help!!!

Thx so much...

 

[Allen Browne]

Presumably your main report shows the customer, and the subreport the
equipment they have on loan. So you need to limit the subreport to customers
who have something loaned out, and have not made a recent purchase.

There's a couple of ways to approach this. One would be to create a query
showing purchases limited to the last 90 days, and save. Then create the
query that uses this query as a source "table" as well as your Customers
table. Double-click the line joining these 2 tables in the upper pane of
query design. Access offers a dialog with 3 choices. Choose the one that
says:
All records from Customers, and any matches from ...
Then drag the primary key field from the query "table" into the grid, and in
the Criteria row under this field, enter:
Is Null

Technically, you made an outer join (so all customers get selected), and
then you excluded the ones where the PurchaseID in the last 90 days did not
exist.

The other approach is to use a subquery. This example assumes a piece of
equipment is unreturned if the ReturnDate is blank:
SELECT tblCustomer.*
FROM tblCustomer
WHERE EXISTS (SELECT HireID FROM tblHire
WHERE tblHire.CustomerID = tblCustomer.CustomerID
AND tblHire.ReturnDate Is Null);

If subqueries are new, here's an introduction:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

One or other of those techniques should get you there, or perhaps even the
combination of both. Ultimately, this query becomes the Record Source for
your main report.