Need help conbining 3 tables

[tamaro]

I have 3 tables that need to be conbined the only comon field is the cust # they look like this
Table 1
Patient # Current 30-60 Days 60-90 Days 90 & Over Primary
69742 $8.95 $120.00 $20.00 $0.00 $8.95
34424 $10.00 $0.00 $50.00 $0.00 $10.00
77623 $12.80 $0.00 $62.00 $0.00 $12.80

Table2
Patient # Current 30-60 Days 60-90 Days 90 & Over coins
69742 $8.95 $10.00 $0.00 $5.00 $8.95
34424 $10.00 $0.00 $0.00 $40.00 $10.00
77623 $12.80 $20.00 $6.00 $0.00 $12.80

table3
Patient # Current 30-60 Days 60-90 Days 90 & Over trins
69742 $8.95 $0.00 $0.00 $0.00 $8.95
34424 $10.00 $0.00 $4.00 $20.00 $10.00
77623 $12.80 $80.00 $0.00 $0.00 $12.80

I want the end result to be

Patient # Current 30-60 Days 60-90 Days 90 & Over Total
34424 $30.00 $0.00 $54.00 $60.00 $30.00
69742 $26.85 $130.00 $20.00 $5.00 $26.85
77623 $38.40 $100.00 $68.00 $0.00 $38.40

wich is the sum of each field by patient # any help would be appreciated.

 

[lespaul]

SELECT Patient#, SUM(A.Current), SUM([A.30-60 Days]), SUM([A.60-90 Days]), SUM([A.90 & Over])
FROM
(SELECT * FROM Primary
UNION ALL
SELECT * FROM Coins
UNION ALL
SELECT * FROM Trins) As A GROUP BY Patient#

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

One way (SQL code):
SELECT U.[Patient #], SUM(U.[Current]) AS Total1, SUM(U.[30-60 Days]) AS Total2, SUM(U.[60-90 Days]) AS Total3, SUM(U.[90 & Over]) AS Total4
FROM (
SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over] FROM [Table 1]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over] FROM [Table 2]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over] FROM [Table 3]
) AS U
GROUP BY U.[Patient #]
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tamaro]

how would i add the the fifth sum Primary+coins+trins? i tried just adding at the end but it gives me errors.

SELECT U.[Patient #], SUM(U.[Current]) AS Total1, SUM(U.[30-60 Days]) AS Total2, SUM(U.[60-90 Days]) AS Total3, SUM(U.[90 & Over]) AS Total4, SUM(U.[Total]) AS Totals
FROM (
SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [primary] FROM [Table 1]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [coins] FROM [Table 2]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [trins] FROM [Table 3]
) AS U
GROUP BY U.[Patient #]
ORDER BY 1

I also tried to change the field name to be the same on the 3 tables but it didnt work either.

 

[lespaul]

SELECT U.[Patient #], SUM(U.[Current]) AS Total1, SUM(U.[30-60 Days]) AS Total2, SUM(U.[60-90 Days]) AS Total3, SUM(U.[90 & Over]) AS Total4, SUM(U.[Current] + U.[30-60 Days] + U.[60-90 Days] + U.[90 & Over]) AS Totals
FROM (
SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [primary] FROM [Table 1]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [coins] FROM [Table 2]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [trins] FROM [Table 3]
) AS U
GROUP BY U.[Patient #]
ORDER BY 1

 

[tamaro]

Thank you Lespaul and PH. I'm getting a data type mismach error. I verify and all are the same type, any ideas?

 

[Golom]

You may have NULLs in some of your fields. Try

SUM(NZ(U.[Current]) + NZ(U.[30-60 Days]) + 
    NZ(U.[60-90 Days]) + NZ(U.[90 & Over]))

 

[PHV]

SELECT U.[Patient #], SUM(U.[Current]) AS Total1, SUM(U.[30-60 Days]) AS Total2, SUM(U.[60-90 Days]) AS Total3, SUM(U.[90 & Over]) AS Total4, SUM(U.[Total]) AS Totals
FROM (
SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [primary] [!]AS Total[/!] FROM [Table 1]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [coins] FROM [Table 2]
UNION ALL SELECT [Patient #], [Current], [30-60 Days], [60-90 Days], [90 & Over], [trins] FROM [Table 3]
) AS U
GROUP BY U.[Patient #]
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tamaro]

Finally get to try this and I still have some problems. if I use the following code everyting works fine.

SELECT U.[Patient #], Sum(U.Current) AS Total1, Sum(U.[30-60]) AS Total2, Sum(U.[60-90]) AS Total3, Sum(U.[90+]) AS Total4
FROM [SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_PR]
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_CO]
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_TR]
]. AS U
GROUP BY U.[Patient #]
ORDER BY 1;

The only problem is that I dont get a grand total.

then I modify the code as per PHV and all hell brakes lose.

the code changes as follows after saving it:

SELECT U.[Patient #], Sum(U.Current) AS Total1, Sum(U.[30-60]) AS Total2, Sum(U.[60-90]) AS Total3, Sum(U.[90+]) AS Total4, Sum(U.open) AS Totals 
FROM [SELECT [Patient #], [Current], [30-60], [60-90], [90+], [open] AS Total FROM [AGING_PR] 
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+], [open] FROM [AGING_CO] 
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+], [open] FR] AS U GROUP BY U.[Patient #]
ORDER BY 1;

and it gives me an error when ran:
invalid bracketing of name 'SELECT [Patient #'

also the type of query changes from Select to a Union query.

any ideas? and thank you very much for your help.

 

[lespaul]

do you have fields named 'Open' in Aging_PR and Aging_CO and the FROM clause is missing from the third part of the Union query.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Access has the unpleasent habit of changing (...) to [...]. in a from clause. You often need to either edit it back to the syntax using parentheses or make the sub-query a separate query.

Either

SELECT U.[Patient #], Sum(U.Current) AS Total1, Sum(U.[30-60]) AS Total2, Sum(U.[60-90]) AS Total3, Sum(U.[90+]) AS Total4
FROM [COLOR=red][b]([/b][/color]SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_PR]
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_CO]
UNION ALL SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_TR]
[COLOR=red][b])[/b][/color] AS U
GROUP BY U.[Patient #]
ORDER BY 1;

or

SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_PR]
UNION ALL 
SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_CO]
UNION ALL 
SELECT [Patient #], [Current], [30-60], [60-90], [90+] FROM [AGING_TR]

saved as qryUnion and then

SELECT U.[Patient #], Sum(U.Current) AS Total1, Sum(U.[30-60]) AS Total2, Sum(U.[60-90]) AS Total3, Sum(U.[90+]) AS Total4
FROM qryUnion AS U
GROUP BY U.[Patient #]
ORDER BY 1;