Prev: VSFLEX3.OCX install
Next: dumb data type question
From: DMainland on 29 Mar 2010 11:51
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.

From: Steve on 29 Mar 2010 13:33
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
santus(a)penn.com


"DMainland" <DMainland(a)discussions.microsoft.com> wrote in message
news:E7D2A156-183E-4FC3-BE11-AF5DBB45618C(a)microsoft.com...
>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.
>


From: John W. Vinson on 29 Mar 2010 16:27
On Mon, 29 Mar 2010 08:51:01 -0700, DMainland
<DMainland(a)discussions.microsoft.com> 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]
 | 
Pages: 1
Prev: VSFLEX3.OCX install
Next: dumb data type question