joining two tables via multiple fields

[PAULCALLAGHAN]

I have Access 2000. I would like to create a query based on data from two tables but having trouble joining them up.

In table 1 I have ORDNUM which is 10 characters in length. In table 2 I have 3 fields that relate back to this, ORDER (the first 6 characters), LINE (the next 2 characters) and DELIV (the final 2 characters).

Can someone please help me to write the SQL code to properly "join" these tables by connecting these fields together?

 

[lespaul]

this may work:

SELECT * FROM tbl1 INNER JOIN (SELECT ORDER & LINE & DELIV As ORDERNUM) As B on tbl1.ORDNUM = B.ORDERNUM

Leslie

 

[PAULCALLAGHAN]

Thanks lespaul, but now I'm getting a syntax error. Can you please explain what it is you are doing? Maybe I can test something slightly different.

Any suggestions?

 

[PHV]

Something like this ?
SELECT *
FROM tbl1,tbl2
WHERE tbl1.ORDNUM=tbl2.ORDER & tbl2.LINE & tbl2.DELIV
;

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

 

[lespaul]

I forgot the FROM clause in the INNER JOIN SELECT:

SELECT * FROM tbl1 INNER JOIN (SELECT ORDER & LINE & DELIV As ORDERNUM FROM tbl2) As B on tbl1.ORDNUM = B.ORDERNUM

Leslie