TRANSFORM crosstab

[SweetDot]

Hi,
What does Transform do?
I have this query that is giving me the wrong data, I just can't figure out what's wrong:

i'm trying to figure out what's wrong with this query:

TRANSFORM Sum(tblSource.[A Pax]) AS Pax SELECT tblQtrHrs.QtrHrTime, 'A' AS
Bound FROM tblSource, tblQtrHrs WHERE ( (Not tblQtrHrs.QtrHrTime Is Null)
AND (IIf(Not tblSource.Format = "IATA",tblSource.[A Pax],1)>0) AND
(Nz([tblSource]![A Deleted],0)<>1) AND ((tblSource.[A Terminal] IN (1,2,3)
AND tblSource.[A Sect] in('D')) ) AND
((IIf([tblQtrHrs].[qtrHrTime]>=#23:15:0# And [tblSource].[A
Time]<#0:45:0#,DateAdd('d',1,[tblSource].[A Time]),[tblSource].[A
Time]))>=([tblQtrHrs].[QtrHrTime]) And
(IIf([tblQtrHrs].[qtrHrTime]>=#23:15:0# And [tblSource].[A
Time]<#0:45:0#,DateAdd('d',1,[tblSource].[A Time]),[tblSource].[A
Time]))<=DateAdd('s',59,DateAdd('n',59,[tblQtrHrs].[QtrHrTime]))) ) AND
((([tblSource].[Format]="ATB" and tblSource.Name='ATB_db-S05S02V6-Base
Case') or ([tblSource].[Format]="DAPS" and
tblSource.Name='ATB_db-S05S02V6-Base Case' and ([tblSource].[A
Date]+[tblSource].[A Time]>=#1/1/2000 4:00:00 AM# and [tblSource].[A
Date]+[tblSource].[A Time]<=#1/2/2000 4:00:00 AM#)) or
([tblSource].[Format] = "IA
TA" and tblSource.Name='ATB_db-S05S02V6-Base Case' and ([tblSource].[A
Date]+[tblSource].[A Time]>=dateadd("h",-4,#1/1/2000 4:00:00 AM#) and
[tblSource].[A Date]+[tblSource].[A Time]<=dateadd("h",-4,#1/2/2000 4:00:00
AM#)))) ) GROUP BY tblQtrHrs.QtrHrTime, 'A'PIVOT [tblSource]![Name] & '
Pax';

the result looks like this:..i've only shown the first few
entries...basically it looks at the flight schedule and group the flights by
quarter of an hour ..summing up the passenger numbers in each quarter
hour...

QtrHrTime Bound ATB_db-S05S02V6-Base Case Pax
5:15 A 297
5:30 A 502
5:45 A 962
6:00 A 1084
6:15 A 1298
6:30 A 1538
6:45 A 1424
7:00 A 1636
7:15 A 1303




here's the source table:

tblSource Name Format Airline ACType Terminal Seats A Fl # A Sect A Date A
Time A Pax A Route A Deleted D Fl # D Sect D Date D Time D Pax D Route D
Deleted Load A Terminal D Terminal
ATB_db-S05S02V6-Base Case ATB EGF EM3 3 37 4747 T
7:42:00 PM 24 BOS 0





0 0 3 3


sample of another source table:

tblQtrHrs QtrHrNum QtrHrTime
0 0:00
15 0:15
30 0:30
45 0:45
100 1:00



the problem is that if you look at the first table where at 5:15 there are
297 passengers, the time should actually be 6:00
I've been trying to play around with it...but don't really know why it'd put
it into the wrong time slot~~~

 

[BobJacksonNCI]

Transform is the VBA instruction that actually performs a crosstab query - which means turn the results 90 degrees and use one of the column's values as the new column headers.

One of the resident guru's can provide better assistance as to why you're not getting the desired results.

You could search Tek-Tips for either "crosstab" or "transform" to see threads of others with questions.

Also, if someone doesn't jump right in, a common technique is to reduce the complexity of the query until you find what you're looking for. Make a backup of the original, of course.


HTH,
Bob

 

[SweetDot]

I've tried to do that...but it's still not doing what i want it to do:...this query was written by someone else, and I don't know who that person is, I've just been told to fix it

so here is what i'm trying to do:
I am given a schedule of flights, each record has both arrival and departure times and passenger
what I want to do is to break down the time into quarter hours, and if the flight belongs to a certain quarter hour time slot, then the sum of the passenger numbers in that slot will be displayed as the value next to that time slot. In the end, I want to columns: one for the quarter hour time slots, from 12:00am to 11:45pm. In total 96 slots. The second column is the passenger numbers of each slot. What the SQL query in my first post does have the correct number of passengers. But somehow, the time slot that it corresponds to is wrong. I don't really know what I should do to fix it. If somehow I can just shift the passenger numbers down a few rows then it'd be correct. Please help!!

 

[BobJacksonNCI]

Dot,

Sorry, your problem exceeds my skill level.

Hopefully, the talented folks here can sort it out.
I reposted the SQL with blank lines and indents for readability.

The only thing I can recommend is to use the wizard to create a crosstab query. It may not run until you resolve Nulls, but you have this code to use as an example. And then the need for logic to handle midnight, etc, may become apparent.

Good Luck!
Bob

TRANSFORM Sum(tblSource.[A Pax]) AS Pax

SELECT tblQtrHrs.QtrHrTime, 'A' AS Bound

FROM tblSource, tblQtrHrs

WHERE ( (Not tblQtrHrs.QtrHrTime Is Null)

AND (IIf(Not tblSource.Format = "IATA",tblSource.[A Pax],1)>0)
AND (Nz([tblSource]![A Deleted],0)<>1)
AND ((tblSource.[A Terminal] IN (1,2,3)
AND tblSource.[A Sect] in('D')) )

AND ((IIf([tblQtrHrs].[qtrHrTime]>=#23:15:0#
And [tblSource].[ATime]<#0:45:0#,DateAdd('d',1,[tblSource].[A Time]),[tblSource].[ATime]))
>=([tblQtrHrs].[QtrHrTime])
And (IIf([tblQtrHrs].[qtrHrTime]>=#23:15:0#
And [tblSource].[ATime]<#0:45:0#,DateAdd('d',1,[tblSource].[A Time]),[tblSource].[ATime]))
<=DateAdd('s',59,DateAdd('n',59,[tblQtrHrs].[QtrHrTime]))) )

AND ((([tblSource].[Format]="ATB" and tblSource.Name='ATB_db-S05S02V6-Base Case')
or ([tblSource].[Format]="DAPS" and tblSource.Name='ATB_db-S05S02V6-Base Case'
and ([tblSource].[ADate]+[tblSource].[A Time]>=#1/1/2000 4:00:00 AM#
and [tblSource].[ADate]+[tblSource].[A Time]<=#1/2/2000 4:00:00 AM#))
or ([tblSource].[Format] = "IATA" and tblSource.Name='ATB_db-S05S02V6-Base Case'
and ([tblSource].[ADate]+[tblSource].[A Time]>=dateadd("h",-4,#1/1/2000 4:00:00 AM#)
and [tblSource].[A Date]+[tblSource].[A Time]<=dateadd("h",-4,#1/2/2000 4:00:00 AM#)))) )

GROUP BY tblQtrHrs.QtrHrTime, 'A'PIVOT [tblSource]![Name] & 'Pax';

 

[dhookom]

SweetDot,
What do you mean by "passenger numbers"? Are you looking for a count of the number of passengers arriving during that time slot?
Are you displaying only one day or several?
Is this just for the Arrival Times?
What do the records in your table tblQtrHrs look like?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SweetDot]

yeah, i'm counting the passenger numbers within a time slot...
tblQtrHrs contains just a list of time slot of a 24 hr period ...so it goes like this
0:00
0:15
0:30
0:45
1:00
....and so on
I've actually just solved the problem with the time slot....so it is now giving me correct numbers...
but there is a second part to this problem....(which I just found out today)
After calculating the total number of passengers within that time slot, I need to do a running total of the passenger numbers..
my query from the first part has the time slot as the first column, and the total number of passengers in that time slot in the 2nd column. However, now I need to use that result, and do a runnning total on the passenger numbers for every hour period....
i.e.

the table of the first part:

0:00 0
0:15 0
0:30 0
0:45 4
1:00 5
1:15 3
1:30 5
1:45 16
2:00 1

now the running total should look like this:

0:00 0
0:15 0
0:30 0
0:45 4 = 0+0+0+4
1:00 9 = 0+0+4+5
1:15 12 = 0+4+5+3
1:30 17 = 4+5+3+5
1:45 29 = 5+3+5+16
2:00 25 = 3+5+16+1


how do i do the running total??


thanks so much!!

 

[dhookom]

You can use a text box in a report with the Running Sum property set to Over All.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SweetDot]

I don't get it. A report? The result from the first part is in a query right now. So I just have a table with two columns. How do I use the textbox?

 

[dhookom]

Use a report to display the results of your query. Then refer to my previous reply.

If you don't want to use a report then you will have to reply with the SQL of your query or answer the questions I asked earlier.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SweetDot]

i need to use the result from the 2nd part (the running total) to plot a chart...so i think i'll need to use SQL instead of the report..
here is the query:


TRANSFORM Sum(tblSource.[A Pax]) AS Pax
SELECT tblQtrHrs.QtrHrTime, 'A' AS Bound
FROM tblSource, tblQtrHrs
WHERE ((Not (tblQtrHrs.QtrHrTime) Is Null) AND ((IIf(Not [tblSource].[Format]="IATA",[tblSource].[A Pax],1))>0) AND ((Nz([tblSource]![A Deleted],0))<>1) AND ((tblSource.[A Terminal]) In (1,2,3)) AND ((tblSource.[A Sect]) In ('D')) AND ((tblSource.[A Time])>=[tblQtrHrs].[QtrHrTime] And (tblSource.[A Time])<DateAdd('n',15,[tblQtrHrs].[QtrHrTime])) AND ((tblSource.Format)="ATB") AND ((tblSource.Name)='ATB_db-S05S02V6-Base Case')) OR (((tblSource.Format)="DAPS") AND ((tblSource.Name)='ATB_db-S05S02V6-Base Case') AND (([tblSource].[A Date]+[tblSource].[A Time])>=#1/1/2000 4:0:0# And ([tblSource].[A Date]+[tblSource].[A Time])<=#1/2/2000 4:0:0#)) OR (((tblSource.Format)="IATA") AND ((tblSource.Name)='ATB_db-S05S02V6-Base Case') AND (([tblSource].[A Date]+[tblSource].[A Time])>=DateAdd("h",-4,#1/1/2000 4:0:0#) And ([tblSource].[A Date]+[tblSource].[A Time])<=DateAdd("h",-4,#1/2/2000 4:0:0#)))
GROUP BY tblQtrHrs.QtrHrTime, 'A'
PIVOT [tblSource]![Name] & ' Pax';

and the results from the first part looks like this:

QtrHrTime ATB_db-S05S02V6-Base Case Pax
6:00 297
6:15 205
6:30 460
6:45 122
7:00 615
7:15 445

so for each line of the QtrHrTime, the Pax number will be the sum of the current number + the previous three Pax numbers...is it possible to do that in SQL?

 

[dhookom]

Assuming this query is saved as qxtbA then you might try a second query with a sql of:
SELECT QtrHrTime, [ATB_db-S05S02V6-Base Case Pax],
(SELECT Sum([ATB_db-S05S02V6-Base Case Pax])
FROM qxtbA A WHERE A.QtrHrTime <=qxtbA.QtrHrTime) as RunSum
FROM qxtbA;



Duane
MS Access MVP
Find out how to get great answers faq219-2884.