A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab query question



 
 
Thread Tools Display Modes
  #1  
Old March 29th, 2010, 04:51 PM posted to microsoft.public.access.tablesdbdesign
DMainland
external usenet poster
 
Posts: 26
Default 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  
Old March 29th, 2010, 06:33 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old March 29th, 2010, 09:27 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.