Simple Totals query only showing one group

[thechazm]

Good day everyone,

I have litterally :banghead:b/c I have a simple totals query that does not want to total for any groups except for one of them. So I'll try to explain.

The data getting returned by the query it's trying to total looks like this:

Fields:
Charged Date | Home Shop | Borrow Shop | Work Hour | Nuke
1/14/2003 11 17 8 Non
1/15/2003 17 26 8 Nuclear
1/16/2003 11 72 8 Non

So when I try to run a totals query on this type of data I only get the nuclear number like this:

Home Shop | Nuclear | Total Hours
17 Nuclear 8

I would have expected:

Home Shop | Nuclear | Total Hours
11 Non 16
17 Nuclear 8

For the nuclear field I tried using 0 or -1 for true or false and I also tried actual boolean types finally I tried text and for some reason it only totals the Nuclear numbers....

Any idea's would be appreciated. Also the Nuclear field is a calculated field that contains Nuke: IIf(IsNull([Nuclear]),"Non","Nuclear") in a prior query.

Thanks

 

[pbaldy]

What's the SQL of your query?

 

[thechazm]

Prior Query that the Totals query tries to use:

SELECT [(Temp) Charges Master].[IBB Date], 
[(Temp) Charges Master].[Home Shop], 
[(Temp) Charges Master].[Borrow Shop], 
[(Temp) Charges Master].[Work Hour], 
[(Code) SDSK - SPF List].Nuke, 
[(Temp) Charges Master].[Charge Cat]

FROM [(Temp) Charges Master] INNER JOIN [(Code) SDSK - SPF List] ON [(Temp) Charges Master].Badge = [(Code) SDSK - SPF List].Badge

WHERE ((([(Temp) Charges Master].[IBB Date]) Between DateAdd("d",-7,cweekdate()) And DateAdd("d",-3,cweekdate())) AND (([(Temp) Charges Master].[Home Shop]) In ("11","14","17","26","31","38","41","51","56","57","64","67","71","72","99")) AND (([(Temp) Charges Master].[Borrow Shop]) Is Not Null) AND (([(Temp) Charges Master].[Charge Cat])=3));

Totals Query:

SELECT [(Code) Loaned List].[Home Shop], [(Code) Loaned List].Nuke, Sum([(Code) Loaned List].[Work Hour]) AS TotalWork
FROM [(Code) Loaned List]
GROUP BY [(Code) Loaned List].[Home Shop], [(Code) Loaned List].Nuke;

 

[pbaldy]

That should work; do the other values appear in the base query?

 

[thechazm]

Yes all the data in the prior query is correct with non and nuclear entries all throughout. I'm glad that were on the same page because I was feeling very stupid there for a while.. I mean come on how hard is it to total up numbers while keeping the groups in tact...

 

[pbaldy]

Can you post the db here?

 

[thechazm]

No sorry it's to large. Anyway here was another weird reaction. I thought if I took the queries data and put it into a table by changing the query that shows the good data into an append query, it marks all of the records as nuclear which makes sence that the totals query would be showing that. Very weird that when I am looking at the data in the query it's right but when you try to do something with it in access it almost seems like that field is not be read correctly.

 

[thechazm]

Ok I finally figured out what is going on and still I really don't understand why but here goes. When a boolean field is either null or true within the same field and you use something like this Nuke: IIf(IsNull([Nuclear]),"Non","Nuclear") as a field to show a value instead of just leaving it null.

Then it will not seperate the value's. It just markes them all as true if you try to total them or put it into a temp table. But if I leave it alone and not use the iif statement and run a totals query or push it to a table then any of the fields that were null it counts them as false and the true ones are true.

This does not make any sense to me why a query would respond like this but there ya have it...

Very weird.

 

[thechazm]

On another note I can't do a count on the query only a sum to do a count I have to switch that boolean field to like I had it before....

This sucks.