combine two tables with inconsistent data

[romanzero]

Hi,

I need to join two tables.

tbl_Budget has three fields that I need:
[Fund]
[Account]
[Budget_Amount]

tbl_Actuals also has three fields that I need:
[Fund]
[Account]
[Actual_Amount]

I need to compare like [Account] within like [Fund]. The problem is when an account exists in the first table, but not the second, and conversly, an account exists in the second and not the first.

I have tried an outer join, but I only get one side of the data. I need to create a query where if an account does not exist in the other table a "0" value is generated to fill in the proxy field value.

Thanks!

 

[lupins46]

You first need to create a union query that produces a list of all combinations of Fund and Account:

Select Fund, Account from tbl_budget
Union
Select Fund, Account from tbl_actuals

Call this qry1.

Now create a second table containing qry1 and both tables.
Link qry1 to the other tables on both fields.

Set the join properties of all links to select all from qry1.

Select qry1.*, tbl_budget.Budget_amount, tbl_actuals.Actual_amount
from
(qry1 left join tbl_budget
on qry1.fund=tbl_budget.Fund and qry1.account = tbl_budget.account)
left join tbl_actuals
on qry1.fund=tbl_actuals.Fund and qry1.account = tbl_actuals.account

 

[tubbers]

This is sort of what I'm having problems with. I tried using what you suggested above with my own table(s) but I get an error saying there's a problem with my Join.

Could you look at my code and see what I've done wrong? Should I start a new topic or just post my SQL here?

 

[lupins46]

Post it here.

 

[tubbers]

This is kind of hard to explain but here goes:

I have two tables: RRPHA and RRPHB

Each table has the fields ID(pk), Location, TAT, Filename.

The Filename consists of the Location (RRPHA) and TAT (1420600.tat) together (example: RRPHA1420600.tat). The TAT is actually the JulianDate and hour the data was collected from our system.

I need a complete listing of all Filenames in both RRPHA and RRPHB that match and do not match (if that makes sense).

So the results I'm looking for is a little something like this:
RRPHA_Filename RRPHB_Filename
RRPHA1420600.tat RRPHB1420600.tat
RRPHA1420700.tat RRPHB1420700.tat
RRPHA1420715.tat
RRPHA1420800.tat RRPHB1420800.tat
RRPHB1420854.tat
RRPHA1420900.tat RRPHB1420900.tat

I've managed to create two separate queries using an Outer Join but, like romanzero, I need the results to be in one query.

Does this make any sense at all?

 

[lupins46]

Sorry I should have told you to start a new Q. I didn't realise you were not the original poster.

The same approach is applicable in your case but the detail must be different because you need to use partial field matches.

Qry1:
Select mid(filename,6)as RKey from RRPHA
union
Select mid(filename,6) as RKey from RRPHB

Qry2:
Select mid(filename,6)as RKey, Filename as FA from RRPHA

Qry3:
Select mid(filename,6)as RKey, Filename As FB from RRPHB

Qry4:
Select rrpha.fa, rrphb.fb from qry1
(Left join qry2
on qry1.Rkey = Qry2.RKey)
Left join qry3
on qry1.Rkey = qry3.Rkey

 

[PHV]

Something like this ?
SELECT B.Fund,B.Account,Budget_Amount,Nz(Actual_Amount,0) As Actual_Amount
FROM tbl_Budget B LEFT JOIN tbl_Actuals A ON (B.Fund=A.Fund AND B.Account=A.Account)
UNION
SELECT A.Fund,A.Account,Nz(Budget_Amount,0),Actual_Amount
FROM tbl_Actuals A LEFT JOIN tbl_Budget B ON (A.Fund=B.Fund AND A.Account=B.Account)

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

 

[tubbers]

Lupins46,

I get an error message "Syntax error in FROM clause" when I try and run the query you suggested. I double checked for spelling errors, etc., but everything matches what you typed.

Any suggestions?

 

[romanzero]

Thanks for the responses!

lipins:

I got the query to work. So far it has tested out just fine, but man...it sure takes a long time to run. Any ideas why?

PHV:

I got your version to work (sort of). I keep getting duplicate lines. One with a budget amount and 0 for the actual, and a second with a 0 for the budget and the actual amount. Using Nz is something that I tried earlier, but the results were similar. As with lupins' version, it takes a very long time to run.

Nonetheless,

Thank you both.

Rom