Re: Need help in Query -

Try this query:

SELECT
a.ContractNo
, a.SerialNo
, a.JobNo
, a.PNo
FROM TableA a
LEFT JOIN TableB b
ON ( a.ContractNo = b.ContractNo
AND a.SerialNo = b.SerialNo
AND a.JobNo = b.JobNo
AND a.PNo = b.PNo
)
WHERE b.ContractNo IS NULL

This will locate rows in TableA without a counterpart in TableB. Change it to a RIGHT JOIN and WHERE a.ContractNo IS NULL to find any rows that are in TableB without counterparts in TableA.

And, stay tuned, you may get a better offer.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


"Me" <Me@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F106867F-EB47-498A-B771-CAF3321D5171@xxxxxxxxxxxxxxxx
I have two tables each with concatenated key - ContractNo, SerialNo, JobNo, PNo

I would like to find out missing records in the other table, something like

select ContractNo, SerialNo, JobNo, PNo from table a
where not exists ( ContractNo, SerialNo, JobNo, PNo from table b)

when I do the query with exists in it, it shows me matching records ( I
think so as most of the recs are matching, so I can't say for sure)
however, when I change it to not exists, it doesn't .

Thank you,
-Me

Relevant Pages

  • Re: Advanced query issue
    ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... and then write a query linking just by your new ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Finding matching records
    ... >> An inner join in a query will return only records where the join ... Suppose you have two tables, TableA and TableB, with these fields: ... A_ShouldMatch has a match in the B_ShouldMatch field in TableB. ... create a new query in Design View and add both TableA and TableB to the ...
    (microsoft.public.access.modulesdaovba)