have three tables I need three totals from these tables

[sharonchapman7]

Hi,
I am using MS Access 2010 on Windows System 7.

I have three tables: A, B, C
Table A has the field Task NO
Table B has the fields Task No, PriorActuals
Table C has the fields Task No, HoldbackActuals

I need to create a query that tasks the Task No from Table A and matches it to Table B and Table C. I then need to show in the query the following:

Task no PriorActuals HoldbackActuals Total

My problem is that depending on how I connect the tables, different values are stored in the Total field. the Total field will either be all the data in the PriorActuals field or it will be all the data in the HoldbackActuals field. I can't get the total field to show PriorActuals + holdbackActuals.

Please help. i have tried everything I can think of and I can't get it to work.

Thanks

 

[Andrzejek]

Forget about the Total for a moment.

Show us the SQL you have without the Total, and a sample of data your SQL creates.

Have fun.

---- Andy

 

[PHV]

You wanted something like this ?

SELECT A.[Tak NO]
, Nz(Sum(B.PriorActuals,0)) AS totPrior
, Nz(Sum(C.HoldbackActuals,0)) AS totHoldback
, Nz(Sum(B.PriorActuals,0))+Nz(Sum(C.HoldbackActuals,0)) AS Total
FROM (TableA A
LEFT JOIN TableB B ON A.[Tak NO]=B.[Tak NO])
LEFT JOIN TableC C ON A.[Tak NO]=C.[Tak NO]
GROUP BY A.[Tak NO]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sharonchapman7]

Here is what my data looks like:
three tables: A, B, C
Table A has the field Task NO
Table B has the fields Task No, PriorActuals
Table C has the fields Task No, HoldbackActuals

Table A Table B Table C

Task NO Task No PriorActuals Task No HoldbackActuals
30009252013 30009252013 $14690.72 30009252013 $0
3102092013 3102092013 $5,079.85 3102092013 -$79.85

Sql Code:
SELECT [JPAS TASKS QRY].[TASK NO], Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) AS [SumOfPRIOR ACTUALS], Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS [SumOfPRIOR HOLDBACKS]
FROM ([JPAS TASKS QRY] LEFT JOIN [PRIOR HOLDBACKS (PID) QRY] ON [JPAS TASKS QRY].[TASK NO] = [PRIOR HOLDBACKS (PID) QRY].[TASK NO]) LEFT JOIN [PRIOR ACTUALS (PID) QRY] ON [JPAS TASKS QRY].[TASK NO] = [PRIOR ACTUALS (PID) QRY].[TASK NO]
WHERE ((([JPAS TASKS QRY].FY)=2013))
GROUP BY [JPAS TASKS QRY].[TASK NO];

 

[SkipVought]

I can't get the total field to show PriorActuals + holdbackActuals.

SELECT
  [JPAS TASKS QRY].[TASK NO]
, [b]Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) + Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS 'Sum Of ALL'[/b]
...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sharonchapman7]

I tried the code below. The PriorActuals, totPrior, HoldbackActuals and totHoldback are correct. however, the total column will calculate if there is a number in totHoldback but if there is 0 in totHoldback it won't add the totPrior to the total column.

SELECT A.[Tak NO]
, Nz(Sum(B.PriorActuals,0)) AS totPrior
, Nz(Sum(C.HoldbackActuals,0)) AS totHoldback
, Nz(Sum(B.PriorActuals,0))+Nz(Sum(C.HoldbackActuals,0)) AS Total
FROM (TableA A
LEFT JOIN TableB B ON A.[Tak NO]=B.[Tak NO])
LEFT JOIN TableC C ON A.[Tak NO]=C.[Tak NO]
GROUP BY A.[Tak NO]

 

[PHV]

What is the REAL code you tried ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sharonchapman7]

Thanks so much. the code below did the trick. Thanks for your help. I do have one problem though. When I try to sum up the total column, it won't sum it up. any suggestions?

SELECT A.[Tak NO]
, Nz(Sum(B.PriorActuals,0)) AS totPrior
, Nz(Sum(C.HoldbackActuals,0)) AS totHoldback
, Nz(Sum(B.PriorActuals,0))+Nz(Sum(C.HoldbackActuals,0)) AS Total
FROM (TableA A
LEFT JOIN TableB B ON A.[Tak NO]=B.[Tak NO])
LEFT JOIN TableC C ON A.[Tak NO]=C.[Tak NO]
GROUP BY A.[Tak NO]




Sharon

 

[PHV]

When I try to sum up the total column, it won't sum it up
Again, what is YOUR code (you keep posting mine) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sharonchapman7]

thank you.

Here is the code that works:

SELECT [JPAS TASKS].[TASK NO], Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) AS [SumOfPRIOR ACTUALS], Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) AS TOTPrior, Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS [SumOfPRIOR HOLDBACKS], Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS TOTHoldBacks, Sum(Nz([prior actuals],0)+Nz([prior Holdbacks],0)) AS Total
FROM ([JPAS TASKS] LEFT JOIN [PRIOR ACTUALS (PID) QRY] ON [JPAS TASKS].[TASK NO] = [PRIOR ACTUALS (PID) QRY].[TASK NO]) LEFT JOIN [PRIOR HOLDBACKS (PID) QRY] ON [JPAS TASKS].[TASK NO] = [PRIOR HOLDBACKS (PID) QRY].[TASK NO]
GROUP BY [JPAS TASKS].[TASK NO];


The only problem I have now is that I'm unable to sum the total column.

Sharon

 

[Andrzejek]

Is [red]that[/red] what you are after?

[pre]
Task totPrior totHoldback Total
12 100 100 200
13 50 150 200
15 150 200 350
[red] 750[/red]
[/pre]

Have fun.

---- Andy

 

[sharonchapman7]

Yes

 

[PHV]

What about this ?
SELECT T.[TASK NO]
, Sum(A.[PRIOR ACTUALS]) AS TOTPrior
, Sum(H.[PRIOR HOLDBACKS]) AS TOTHoldBacks
, Sum([prior actuals])+Sum([prior Holdbacks]) AS Total
FROM ([JPAS TASKS] T
LEFT JOIN [PRIOR ACTUALS (PID) QRY] A ON T.[TASK NO] = A.[TASK NO])
LEFT JOIN [PRIOR HOLDBACKS (PID) QRY] H ON T.[TASK NO] = H.[TASK NO]
GROUP BY T.[TASK NO]
UNION SELECT 0
, Sum(A.[PRIOR ACTUALS])
, Sum(H.[PRIOR HOLDBACKS])
, Sum([prior actuals])+Sum([prior Holdbacks])
FROM ([JPAS TASKS] T
LEFT JOIN [PRIOR ACTUALS (PID) QRY] A ON T.[TASK NO] = A.[TASK NO])
LEFT JOIN [PRIOR HOLDBACKS (PID) QRY] H ON T.[TASK NO] = H.[TASK NO]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886