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 » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab query with same VALUE in multiple columns



 
 
Thread Tools Display Modes
  #1  
Old June 17th, 2004, 10:41 PM
ChrisJ
external usenet poster
 
Posts: n/a
Default Crosstab query with same VALUE in multiple columns


Hi David,
Rather than running an export for Access to Excel, and
then getting others to sum columns, try this single query
that should do what you require


SELECT TEMP_StudyTourCalendar.DayNumber, DSum
("[pax]","[qryST_Calendar]","[DateIn] =#" &
Format$([DayNumber],"dd mmm yyyy") & "# And [DateOut]=#"
& Format$([DayNumber],"dd mmm yyyy") & "#") AS [Total Pax]
FROM TEMP_StudyTourCalendar
ORDER BY TEMP_StudyTourCalendar.DayNumber;


-----Original Message-----
Hello database gurus and developers.
I have a problem that I cannot seem to solve...

Picture a spreadsheet something like the following - tabs

could muck
up the display a bit:


2004
Name PAX DATEIN DATEOUT Jun-01 Jun-02 Jun-

03 Jun-04
Jun-05 Jun-06
Fred's Tours 12 Jun-01 Jun-05 12

12 12
12 12
Bob's Trips 20 Jun-03 Jun-

06 20
20 20 20
Mary's Tours 41 Jun-02 Jun-05

41 41
41 41


I have data for the first four columns in a table in a

database (Name,
PAX, DATEIN, DATEOUT) The remaining column headers come

from a single
field in a table that is created everyday from a query.

See sample
data further down.

I can interrogate the database to find the min date in

the DATEIN
field and the max date in the DATEOUT field in the entire

table. I use
these two values to create a temporary table of dates

from the min
date to the max date. These dynamic values will become

the column
headers for a crosstab query where I am attempting to re-

create the
above spreadsheet.

I can get the PAX to appear in the correct column *only*

for the first
date, but not *each* date in the range from DATEIN to

DATEOUT (as
shown by the spreadsheet sample). The idea is that the

crosstab query
will export to EXCEL and the user just has to sum each

column to know
how many PAX they are dealing with on a given date (eg.

On Jun 1st
they have 12 PAX, but on Jun 3rd they have to deal with

73 PAX, on Jun
6th only 20 PAX) As you can see the PAX is just copied

from the PAX
column. We never know what the minimum or maximum date

will be on any
day. The Crosstab query picks up the dates from the TEMP

table (which
just has one field of date type)

Below is the actual SQL that works, but only puts the PAX

in the
column where the *first* date column matches the DateIn

for that Tour.
I want the PAX number to appear in every column from the

DateIn to the
DateOut, as shown in the sample spreadsheet section above.

TRANSFORM First(qryST_Calendar.PAX) AS FirstOfConfirmedPAX
SELECT qryST_Calendar.TourName, qryST_Calendar.DateIn,
qryST_Calendar.DateOut, qryST_Calendar.PAX
FROM qryST_Calendar RIGHT JOIN TEMP_StudyTourCalendar
ON qryST_Calendar.DateIn =

TEMP_StudyTourCalendar.DayNumber
GROUP BY qryST_Calendar.TourName, qryST_Calendar.DateIn,
qryST_Calendar.DateOut, qryST_Calendar.PAX
PIVOT TEMP_StudyTourCalendar.DayNumber;

The TEMP_StudyTourCalendar.DayNumber is the field in the

created table
which holds all the dates from the lowest DateIn to the

highest
DateOut and forms the column headers. These will vary

every day. We
will only know the first date and the last date in the

table called
TEMP_StudyTourCalendar, on the day the crosstab query is

run. One day
it might have only 6 dates in it, the next day it might

have 23 dates
in it... The query must work for all dates in the table.

Sample data in TEMP_StudyTourCalendar for the spreadsheet

above would
be:
DayNumber
01-Jun-2004
02-Jun-2004
03-Jun-2004
04-Jun-2004
05-Jun-2004
06-Jun-2004

Am I missing something simple here?
Any gurus like to sink their teeth into this one?

Regards
David Fenton
Brisbane
Australia

.

  #2  
Old June 20th, 2004, 10:41 PM
Davdi Fenton
external usenet poster
 
Posts: n/a
Default Crosstab query with same VALUE in multiple columns

Sorry ChrisJ, my news server seems to have removed your message too
early. Can you please re-post?
Cheers
David
Australia
 




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 05:56 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.