J
johnboy7676
Recently posted this in access.reports, but didn't get any replies.
Since this is really a query question, decided to repost here. Hope
that doesn't violate any 'cross-posting' phohibitions.
--
I recently made a report where the subreport shows the transactions
for a person. It has a "previous balance' subreport, and then the
subreport that shows all transactions > 12/31/2008.
Works fine, but now boss has decided report must be one page only.
In order to have the report as one page only, I need to either: show
the last 35 transactions, or if there are < 35 transactions since
12/31/2008, then "all transactions since > 12/31/2008". And, the "prev
balance" subreport needs to reflect the difference.
If one person has only had 6 (or any number < 35) transactions since
12/31/2008, then the prev balance subreport will show balance as of
12/31/2008. But if a person has had 50 (or any number > 35)
transactions since 12/31/2008, then his previous balance will be as of
whatever the "current number of transactions - 35" is.
I figured out I need to use a TOP query; select in descending order,
then TOP 35, but how do I "communicate" (for lack of a better word) to
the Previous Balance query that it needs to sum all BUT what the TOP
query selects? A sort of "reverse TOP query".
For some people, the TOP 35 returned 38, I'm assuming that is because
some have the exact same same dates? I assume I can select TOP 35
TransactionDates and TransactionID to fix that.
Thanks, John
Since this is really a query question, decided to repost here. Hope
that doesn't violate any 'cross-posting' phohibitions.
--
I recently made a report where the subreport shows the transactions
for a person. It has a "previous balance' subreport, and then the
subreport that shows all transactions > 12/31/2008.
Works fine, but now boss has decided report must be one page only.
In order to have the report as one page only, I need to either: show
the last 35 transactions, or if there are < 35 transactions since
12/31/2008, then "all transactions since > 12/31/2008". And, the "prev
balance" subreport needs to reflect the difference.
If one person has only had 6 (or any number < 35) transactions since
12/31/2008, then the prev balance subreport will show balance as of
12/31/2008. But if a person has had 50 (or any number > 35)
transactions since 12/31/2008, then his previous balance will be as of
whatever the "current number of transactions - 35" is.
I figured out I need to use a TOP query; select in descending order,
then TOP 35, but how do I "communicate" (for lack of a better word) to
the Previous Balance query that it needs to sum all BUT what the TOP
query selects? A sort of "reverse TOP query".
For some people, the TOP 35 returned 38, I'm assuming that is because
some have the exact same same dates? I assume I can select TOP 35
TransactionDates and TransactionID to fix that.
Thanks, John