Join 2 "sub-tables" in a query 1

[Glad2000]

Hello guys,
How can I do this, please?
I have 3 tables: tblCases, tblLeft, tblRight. TblCases-tblLeft is one-to-many relationship (tblLeft is many). TblCases-tblRight is one-to-many relationship too (tblRight is many).

CaseID House Age
1 Red 70
2 Blue 60
3 Yellow 55
4 White 40


LeftID CaseID LDuctNo LPosition
1 1 5 A3
2 1 6 A4
3 3 3 B3

RightID CaseID RDuctNo RPosition
1 2 4 A3
2 2 5 A5
3 4 3 B2
4 1 3 A2


How can I make a query to get this:

CaseID House LductNO RductNo
1 Red 5
1 Red 6
1 Red 3
2 Blue 4
2 Blue 5
3 Yellow 3
4 White 3

Thanks a lot

 

[BNPMike]

Join tablCases to tblRight and declare a field LDuctNo as blank. Select CaseId, House, RDuctno and LDuctno. Union this with the same thing only done with the tblLeft where RDuctno is now blank.

 

[lespaul]

Like this:

SELECT C.CASEID, HOUSE, L.LDUCTNO, "" AS RDUCTNO FROM TBLCASES C INNER JOIN TBL_LEFT L ON C.CASEID = L.CASEID
UNION
SELECT C.CASEID, HOUSE, "", R.RDUCTNO FROM TBLCASES C INNER JOIN TBL_RIGHT R ON C.CASEID = R.CASEID

HTH

Leslie

 

[Glad2000]

Hi Leslie and BNPMike,
I think both of you are saying the same, but could you please be a little more explicit?
Thanks

 

[lespaul]

yes, we both said the same thing.

If you paste the query I wrote into the SQL view and run it, you should get what you are looking for (as long as the field and table names you gave are correct). How much more explicit can I get?

Leslie

 

[Glad2000]

Excellent!!!!! Leslie
It worked great.
But please tell me how can I do the same in the access query grid window, not SQL window.

 

[lespaul]

I don't think you can do a UNION query in the grid. In fact if you try to switch back to the grid you will probably get an message that it couldn't be represented graphically.



Leslie

 

[Glad2000]

Leslie, thanks a lot for your valuable help.
Get a deserved star!!!