Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-23-2004, 08:29 AM   #1
PHILIP SIVYER
Guest
 
Posts: n/a
Unhappy Cross Tab

BEGINNER .....
Is it possible to cumulate numbers in a cross tab query?? for example

Actuals per month

Jan Feb Mar Apr
Customer 100 50 75 200

What Iwould like to achieve is:

Jan Feb Mar Apr
Customer 100 150 225 425

Help!!

  Reply With Quote
Old 02-23-2004, 01:27 PM   #2
Malcy
Newly Registered User
 
Join Date: Mar 2003
Location: Edinburgh, Scotland
Posts: 586
Thanks: 1
Thanked 0 Times in 0 Posts
Malcy
Hi
Why not try building a query to bring together your cumulative totals and then build the cross-tab query off that.
There may be neater ways of doing it (indeed there probably are) but this would be fairly simple to set up and would achieve what you want from what I can see.
Hope this helps

Malcy
Malcy is offline   Reply With Quote
Old 02-24-2004, 12:21 AM   #3
PHILIP SIVYER
Guest
 
Posts: n/a
I must learn to get my facts right!

Quote:
Originally Posted by Malcy
Hi
Why not try building a query to bring together your cumulative totals and then build the cross-tab query off that.
There may be neater ways of doing it (indeed there probably are) but this would be fairly simple to set up and would achieve what you want from what I can see.
Hope this helps

Malcy
Malcy

Thanks for the reply - one small omission I forgot to mention is that when grouping/cumulating values on the months - Jan - Feb - March etc - how do you sum data in those months when the the date field is not by month it's by day. Hope this makes sense.

Phil

  Reply With Quote
Old 02-24-2004, 01:25 AM   #4
Malcy
Newly Registered User
 
Join Date: Mar 2003
Location: Edinburgh, Scotland
Posts: 586
Thanks: 1
Thanked 0 Times in 0 Posts
Malcy
Phil
Grouping dates is one of the easier things. You can either build a calendar table with a date field and a month field or perhaps better use criteria in your query - thus
For January 2004 you would put this in the criteria line on the query
Between #01/01/2004# and #31/01/2004#
For cumulative Jan and Feb colum you would put
Between #01/01/2004# and #29/02/2004#
Etc.
Remember to put the hash signs on either side of the date to let Access know it is a date.
Does that help?
Best wishes

Malcy
Malcy is offline   Reply With Quote
Old 02-24-2004, 01:33 AM   #5
PHILIP SIVYER
Guest
 
Posts: n/a
Thanks!!

Quote:
Originally Posted by Malcy
Phil
Grouping dates is one of the easier things. You can either build a calendar table with a date field and a month field or perhaps better use criteria in your query - thus
For January 2004 you would put this in the criteria line on the query
Between #01/01/2004# and #31/01/2004#
For cumulative Jan and Feb colum you would put
Between #01/01/2004# and #29/02/2004#
Etc.
Remember to put the hash signs on either side of the date to let Access know it is a date.
Does that help?
Best wishes

Malcy
Malcy

Just the job - thanks for your help

Phil
  Reply With Quote
Old 02-24-2004, 01:37 AM   #6
Malcy
Newly Registered User
 
Join Date: Mar 2003
Location: Edinburgh, Scotland
Posts: 586
Thanks: 1
Thanked 0 Times in 0 Posts
Malcy
Phil
I am delighted to have been able to help.
I have received enormous help from this forum and it is good to finally feel in a bit of a position to repay!
Good luck

Malcy

Malcy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World