Sum a Distinct Value in a Coulmn (1 Viewer)

[tsmithjr]

Hi All,

I'm running Access 2016 with a SQL back end containing 2 tables CCR & DCR.

The query I have successfully pulls the data I need for a single day but when I need more than one day I can't figure out how to Sum the Distinct Values in the DCR table. The main issue I believe is while the CCR table will have multiple values for a particular day the DCR table will only have one value per day and the query displays it multiple times.

Here is what my query look like...

SELECT dbo_CCR.[Trans Date], dbo_CCR.[OFFICE CODE], Sum(dbo_CCR.[EDL Retention]) AS [SumOfEDL Retention], Sum(([Retention Fee]+[Fee Backup]+[Fee Manual])+[SALES TAX RETN FEE]) AS [Adj DMV/S Tax TOTAL]
FROM dbo_CCR LEFT JOIN dbo_DCR ON (dbo_CCR.[OFFICE CODE] = dbo_DCR.OFFICE) AND (dbo_CCR.[Trans Date] = dbo_DCR.[Work of Date])
GROUP BY dbo_CCR.[Trans Date], dbo_CCR.[OFFICE CODE], dbo_DCR.ID, dbo_CCR.ID
HAVING (((dbo_CCR.[Trans Date]) Between [Start Date] And [End Date]) AND ((dbo_CCR.[OFFICE CODE])="buf") AND ((Sum(dbo_CCR.[EDL Retention]))>0));

Here are a sample of my results...
Trans Date|OFFICE CODE|SUMOfEDL Retention|Adj DMV/s Tax Total
3/1/2017 | BUF | $27.00 | $3,278.69
3/1/2017 | BUF | $18.00 | $3,278.69
3/1/2017 | BUF | $9.00 | $3,278.69
3/2/2017 | BUF | $27.00 | $2,657.18
3/2/2017 | BUF | $18.00 | $2,657.18
3/2/2017 | BUF | $18.00 | $2,657.18

So I need the Sum of the [SUMOfEDL Retention] column but only the distinct value sum of the [Adj DMV/s Tax Total] in the right column...I'm looking to display this in a access report and the results should be $117.00 & $5,935.87.

I hope this was through and clear...
Thank you for your time
Tom

 

[plog]

You are going to need 2 more queries to do this. Let's call your existing query YourQuery. And because you have awfully named columns I am substituting S for the SUMofEDL... field and A for Adj DMV... fields you currently have.

The SQL for the first query is this:

SELECT SUM(A) AS SubTotalA, S
FROM YourQuery
GROUP BY S

Paste that into a new query, substitute names for the appropriate fields and query and name that query 'Totals_sub1'. Then to get the results you desire, use this SQL:

SELECT SUM(SubTotalA) AS TotalA, SUM(S) AS TotalS
FROM Totals_sub1

 

[tsmithjr]

plog,

Thank you for your quick reply.

Sticking with your field names...if I'm following correctly...I don't need the full Sum of [A]. What I need is for the query to only ADD the Distinct Values returned for that day from column [A]. Column [A] is returning duplicate data because column has multiple values for that day.

If I run a query for 2 days it looks like this....The value for column A on the particular day will always be the same. The value on column S is unique and is what is driving the results in column A to be duplicated. How do I sum only one day per distinct value in column A to get to the $4.00 I need?

DATE | S | A
3/1/17 | 1.00 | 3.00
3/1/17 | 5.00 | 3.00
3/2/17 | 2.00 | 1.00
3/2/17 | 1.00 | 1.00

 

[plog]

Sorry, I mixed up the fields. It should be this:

SELECT SUM(S) AS SubTotalS, A
FROM YourQuery
GROUP BY A

SELECT SUM(SubTotalS) AS TotalS, SUM(A) AS TotalA
FROM Totals_sub1

The top query gets the distinct A values. The second query then adds everything up.

 

[tsmithjr]

Thank you Plog! I got it figured out now!