Limiting Subreports

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have designed a report with a subreport, based on two differnt queries
from two different tables. The main report returns a record with data about
a single client. The subreport lists the last 10 orders received from that
client. Both the report and subreport are limited to the one client number
listed on a form. - This all works fine.
My problem is that when I try to design the same report to be able to be run
for several clients at on time (i.e. : run for every client that has bought
in the last 10 days - the 10 days being variable) Because the top 10 query
the subreport is based on no longer is limited to a specific client, it
returns the to last ten orders we received, without regard to who the client
is. This means that when I run the report for everyone who has purchased in
the last two weeks, my report may print off 31 reports (31 clients) but will
only return a sale detail for that client if it happened to fall in one of
the top overall orders. Consequently, many reports have no sales listed at
all, and most just one or two, instead of having the last ten orders for
each client.

Sorry for the lengthy question - any help is appreciated as always.

Terry Schawe
(e-mail address removed)
 
Terry said:
I have designed a report with a subreport, based on two differnt queries
from two different tables. The main report returns a record with data about
a single client. The subreport lists the last 10 orders received from that
client. Both the report and subreport are limited to the one client number
listed on a form. - This all works fine.
My problem is that when I try to design the same report to be able to be run
for several clients at on time (i.e. : run for every client that has bought
in the last 10 days - the 10 days being variable) Because the top 10 query
the subreport is based on no longer is limited to a specific client, it
returns the to last ten orders we received, without regard to who the client
is. This means that when I run the report for everyone who has purchased in
the last two weeks, my report may print off 31 reports (31 clients) but will
only return a sale detail for that client if it happened to fall in one of
the top overall orders. Consequently, many reports have no sales listed at
all, and most just one or two, instead of having the last ten orders for
each client.
Click to expand...


Base the subreport on a query like:

SELECT A.ClientID, A.PurchaseDate, . . .
FROM Purchases AS A
WHERE A.PurchaseID
IN (Select TOP 10 T.PurchaseID
From Purchases As T
Where T.PurchaseID = A.PurchaseID
Order By T.PurchaseDate DESC)

and use the Link Master/Child properties to restrict the
subreport to the main reort's current ClientID.
 
Back
Top