<font face='Tahoma'>how can i join 2 recordsets so that it returns results that are not completly filterd? i know that doesn't make much sense( as you can tell i'm not an SQL guru!) but i don't know how else to put it. here's the code though...

Recordset 1:

SELECT *
FROM tblJob j, tblStage s, tblCustomer c
WHERE j.strStatusCode = s.strStatusCode
AND j.CustomerID =c.CustomerID
AND s.strStatusCode ='3'

Recordset 2:

SELECT *
FROM tblJob j , tblVendorJobSupp v
WHERE j.nbrJobNo = v.nbrJobNo AND v.strStatusCode = '3'


now i want to bring these 2 together because i need to write a report based on the data of these tables. they are all tied by the nbrJobNo field and i want to filter it based on the strStatusCode.

the problem is that when i try to bring it together into one query i get inaccurate results. i need to have it in one query so that i can repeat the results down the page.

any ideas or am i asking something out of left field?

appreciate any info you can supply.

thanks</font id='Tahoma'>

Please, post your question in one forum only.

Martha Graham

can you specify what you want to see in the results? which fields do you want to use from which records? also please specify the common fields between the tables so we can help you build a good query.

-- J

------------------------
Julio Mellado

PHP | MySQL | UD4

ICQ: 19735247
MSN:

thanks for helping!

yes, the report is dependent on 2 things, the nbrJobNo field which is the primary key in the tblJob table and the strStatusCode which is a foreign key in tblJob table.

nbrJobNo and strStatusCode are also foreign keys in the tblVendorJobSupp table.

i would like to display all Jobs from these tables that have a Status of '3'

however, it seems that if the Job doesn't have a Vendor assigned to it, it won't display. which is not accurate, a Job doesn't have to have a Vendor.

thanks again for the help.

you can try

SELECT *
FROM tblJob j, tblStage s, tblCustomer c
WHERE j.strStatusCode = s.strStatusCode OR j.strStatusCode is null
AND j.CustomerID =c.CustomerID
AND s.strStatusCode ='3'

this might work in Access

------------------------
Julio Mellado

PHP | MySQL | UD4

ICQ: 19735247
MSN: