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 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
|
|||
|
|||
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 | |
|
|