Solved Total Sum? on a Query from Other Queries (1 Viewer)

[Malcolm17]

Hello,

I have created a query from 7 other queries; each one sums the data from a day by product and then it shows it on a 8th query where all the products are displayed with the daily\weekly\date periods selected from a form. - Not really explaining it well I know, its like a Crosstab query but I have used 7 different queries so each column can have different date ranges.

On the 8th Query (for the report) I am looking to show each column then the total of all 7 columns, however the total doesn't seem to add up. I also want to only show rows that are not 0, however when I try this then nothing shows up.

For Each Column

Day1Qty: Nz([D1Qty],0)

For Total Column

TotalQty: [Day1Qty]+[Day2Qty]+[Day3Qty]+[Day4Qty]+[Day5Qty]+[Day6Qty]+[Day7Qty]

At the moment I get:

Col 1 Col 2 Col 3 Total
1 5 3 153 (instead of 9)

Many thanks,

Malcolm

 

[bob fitz]

Perhaps:

TotalQty: Nz([Day1Qty],0)+Nz([Day2Qty],0)+Nz([Day3Qty],0)+Nz([Day4Qty],0)+Nz([Day5Qty],0)+Nz([Day6Qty],0)+Nz([Day7Qty],0)

 

[Malcolm17]

Fabulous! That worked, thank you

 

[bob fitz]

Fabulous! That worked, thank you

Glad you have a solution. Always pleased to help if I can

 

[plog]

You've made 8 complicated left turns instead of making 1 simple right turn. The results of that 8th query should be acheived by using 0 sub-queries. Why break out each individual day if you are just going to add them back together to get a total?

You should just take the table all the data comes from and use the SUM function to just add up all the available data in the range. Like so:

SELECT SUM([QtyField]) AS TotalQty FROM YourTable WHERE [DateField]>[INSERT YOUR START DATE HERE] AND [DateField]<[INSERT YOUR END DATE HERE]

That one query now accomplishes what you just used 8 for.

 

[Pat Hartman]

OR, if you want a column for each day, just use a crosstab query. Simple is always better than complex. The crosstab automatically generates a sum column