If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab query question
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. |
#2
|
|||
|
|||
Crosstab query question
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 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 "DMainland" wrote in message ... 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. |
#3
|
|||
|
|||
Crosstab query question
On Mon, 29 Mar 2010 08:51:01 -0700, DMainland
wrote: 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. 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|