Summing on Crosstab 1

[pavewayammo]

Ok here is my problem. I have a table with personnel status. The status field can be either blank or have an l,t or o in it. I figured out how to get the totals for each but my problem is when no one has one of them. For example if at least 1 person has blank and l and o but none for t. I get an error everytime.

Here is the SQL statement for the first part. This one puts it in th columns with duty section, <>, L, O, T. For <>, L, O and T it put totals for each duty section.
TRANSFORM Count(tblPersonnel.SSN) AS CountOfSSN
SELECT tblPersonnel.[DUTY SECTION]
FROM tblPersonnel
GROUP BY tblPersonnel.[DUTY SECTION]
PIVOT tblPersonnel.STATUS;
Output Example:
Duty Section <> L O T
Flight 11 2
PGM 19 1 4 1
and so on.

Here is the second part that puts the data in to another table.
INSERT INTO tblFlightmanning ( Authorized, Assigned, Available, Leave, [Detail/Other], TDY )
SELECT qxtbPersonnelStatusCountByDutySection.SumOfAUTH, [Available]+[Leave]+[Other] AS Assigned, Sum(qryCountTLO.[<>]) AS Available, Sum(qryCountTLO.L) AS Leave, Sum(qryCountTLO.O) AS Other, Sum(qryCountTLO.T) AS TDY
FROM qryCountTLO, qxtbPersonnelStatusCountByDutySection
GROUP BY qxtbPersonnelStatusCountByDutySection.SumOfAUTH;

The second one is where the error comes from. It says that qryCountTLO.T doesn't exist(when no one has a status of "t").

I am not sure what to do now. Any ideas?

paveway

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[PHV]

You may try something like this in your Crosstab query:
PIVOT tblPersonnel.STATUS In ('<>','L','O','T');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pavewayammo]

Well the error doesn't come up any more. Now the when qryCountTLO.T is empty it makes Available empty also. I also looked over the SQL a little closer for the second part and [blue]found it was messed up[/blue] also [red]shorten a qry name too[/red]. This [green]is what you had me add/change[/green]. Here it is corrected:

INSERT INTO tblFlightmanning ( Authorized, Assigned, Available, Leave, [Detail/Other], TDY )
SELECT [red]qryManningCount[/red].SumOfAUTH, [red]qryManningCount[/red].CountOfSSN, [blue][CountOfSSN]-[L]-[O]-[T] AS Available,[/blue] Sum(qryCountTLO.L) AS L, Sum(qryCountTLO.O) AS O, Sum(qryCountTLO.T) AS T
[green]FROM qryCountTLO, [red]qryManningCount[/red]
GROUP BY [red]qryManningCount[/red].SumOfAUTH, [red]qryManningCount[/red].CountOfSSN, [CountOfSSN]-[L]-[O]-[T];[/green]


paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[PHV]

when qryCountTLO.T is empty it makes Available empty also
Play with the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pavewayammo]

Ok I have been messing with Access databases for 3 years and lately been digging in to the SQL part. I have no idea what Nz is. Can you point me to right place to understand it so I can get this working?



paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[PHV]

Replace this:
[CountOfSSN]-[L]-[O]-[T] AS Available
By this:
[CountOfSSN]-Nz([L],0)-Nz([O],0)-Nz([T],0) AS Available

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pavewayammo]

Ok I'll do that but what does Nz do?

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[PHV]

When in VBE (Alt-F11) open the debug window (Ctrl-g), type nz and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pavewayammo]

I'll have to do that when I get home because they have the computer security set so high here and not everything is installed for Access.

Oh and thank you very much. It works. Now I just got to figure out how to make time stop so I can finish this before the dead line.

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[pavewayammo]

Also note the star.

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.