Crosstab query question

  • Thread starter Thread starter DMainland
  • Start date Start date
D

DMainland

I use a crosstab query in a database as a first step in recalling multiple
monetary transactions made on the same day that, when added together, exceed
$10,000.00 and then generate a report based on the results. The query
currently returns multiple transactions made on the current day as per the
design. The OrderDate field in the query's design view is as follows:

Field: OrderDate
Table: Transaction
Total: Where
Crosstab:
Sort:
Criteria: "Date( )"
Or:

I would like to be able to bring this data up from dates in the past and
between dates in the past. Is this possible? Can I modify the 'criteria' in
the OrderDate field to accept varying parameters? Any help would be greatly
appreciated.
 
It sounds like you need a Totals query rather than a crosstab query! Create
a query that contains the OrderDate and the other transcaction fields you
need in tour report. Click on the Sigma (looks like a capital E) button in
the menu at the top of the screen. Change GroupBy under TransactionAmount to
Sum.

If you run the Totals query as is, you will get the sum of TransactionAmount
for each unique order date.

To get only the sums that exceed $10000, you need to set the criteria for
SunOfTransactionAmount to:
$10000
Click to expand...

For the OrderDate criteria, you need to use the Between/And construct.
First, understand that Between Date() And Date() will limit the OrderDate to
the current date. So if that is what you want, use Between Date() And
Date(). If you want a date range, use Between StartDate And EndDate.

Steve
(e-mail address removed)
 
I use a crosstab query in a database as a first step in recalling multiple
monetary transactions made on the same day that, when added together, exceed
$10,000.00 and then generate a report based on the results. The query
currently returns multiple transactions made on the current day as per the
design. The OrderDate field in the query's design view is as follows:

Field: OrderDate
Table: Transaction
Total: Where
Crosstab:
Sort:
Criteria: "Date( )"
Or:

I would like to be able to bring this data up from dates in the past and
between dates in the past. Is this possible? Can I modify the 'criteria' in
the OrderDate field to accept varying parameters? Any help would be greatly
appreciated.
Click to expand...

Answered in the Queries forum. Please don't multipost the same question to
different newsgroups; instead, crosspost by adding one or two (NO MORE
PLEASE!) newsgroups to the To: line.
 
Back
Top