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

Malcolm17

Member
Local time
Today, 21:17
Joined
Jun 11, 2018
Messages
107
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
Code:
Day1Qty: Nz([D1Qty],0)

For Total Column
Code:
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
 

Attachments

  • Pic.jpg
    Pic.jpg
    24.2 KB · Views: 52

bob fitz

AWF VIP
Local time
Today, 21:17
Joined
May 23, 2011
Messages
4,710
Perhaps:
Code:
TotalQty: Nz([Day1Qty],0)+Nz([Day2Qty],0)+Nz([Day3Qty],0)+Nz([Day4Qty],0)+Nz([Day5Qty],0)+Nz([Day6Qty],0)+Nz([Day7Qty],0)
 

Malcolm17

Member
Local time
Today, 21:17
Joined
Jun 11, 2018
Messages
107
Fabulous! That worked, thank you :)
 

plog

Banishment Pending
Local time
Today, 15:17
Joined
May 11, 2011
Messages
11,586
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:

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

Super Moderator
Staff member
Local time
Today, 16:17
Joined
Feb 19, 2002
Messages
42,741
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
 

Users who are viewing this thread

Top Bottom