compare and unmatched

[Primepixie]

I need help in creating a query that will find all unmatched records in a
table.

I have two tables:
The first table T1 has just has a record number
The second table T2 has a current record number, prior record number

EXAMPLE T2(text fields)
cur_rec Prior_rec
1234 0123
4569 7891

EXAMPLE T1(text field)
record
4569
7891
1020

output would be
cur_rec Prior_rec
1234 0123

I need to compare all the records from T1 to T2 both on current or prior
record numbers. If there is no match on both current or prior record numbers
to T1 record number then I need to creat a new table with the both current
and prior record numbers information from T2.

Thank you for the help,

 

[Jeff Boyce]

Open Access.

Launch the query wizard.

Select "unmatched".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Primepixie]

That will only do one and not compare to both fields in table two???

 

[Jeff Boyce]

I thought that the wizard allowed for more than one field.

If it doesn't, you can open the query in design view and follow the same
principle to compare the second.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Primepixie]

This is what the query wrote: The question is how do I change this do what I
need.

SELECT T2.cur_rec, T2.Prior_rec
FROM T2 LEFT JOIN T1 ON T2.cur_rec = T1.rec
WHERE (((T1.rec) Is Null));

This is only looking at T2.cur_rec not both T2 fields.

I also tryed to do:

SELECT T2.prior_rec, T2.cur_rec
FROM T2
WHERE NOT EXISTS
(SELECT * FROM T1 WHERE (T1.rec=T2.cur_rec) OR (T1.rec=T2.Prior_rec));

Need help. (I am not getting the records I need)

Thank you for your help.

 

[Jeff Boyce]

The use of "OR" is incorrect, if I understand your situation. You need to
know when BOTH are Null, so you'd use "AND".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Easiest might be a union query

SELECT T2.Cur_Rec, T2.Prior_Rec
FROM T2 LEFT JOIN T1
On T2.Cur_Rec = T1.Record
WHERE T1.Record Is Null
UNION
SELECT T2.Cur_Rec, T2.Prior_Rec
FROM T2 LEFT JOIN T1
On T2.Prior_Rec = T1.Record
WHERE T1.Record Is Null

That will return records where either Cur_Rec or Prior_Rec does not match.

IF you need to return records where NEITHER match then the query is a bit more
complex.

Step 1 get records where there IS a match and then eliminate those record

SELECT T2.Cur_Rec, T2.Prior_Rec
FROM T2 INNER JOIN T1
On T2.Cur_Rec = T1.Record
UNION ALL
SELECT T2.Cur_Rec, T2.Prior_Rec
FROM T2 INNER JOIN T1
On T2.Prior_Rec = T1.Record

Step two use that query in an unmatched query to get the final result.

SELECT Distinct T2.Cur_Rec, T2.Prior_Rec
FROM T2 LEFT JOIN TheSavedUnionQuery as Q
ON T2.Cur_rec = Q.Cur_rec
AND T2.Prior_Rec = Q.Prior_Rec
WHERE Q.Cur_Rec is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Primepixie]

Thank you but it pulled all the records from T2 and I know that there are a
few records that do match?

Any suggestions?
I used:
SELECT T2.cur_rec,T2.prior_rec
FROM T2
WHERE NOT EXISTS
(SELECT * FROM T1 WHERE (T1.rec=T2.cur_rec) AND (T1.rec=T2.prior_rec));

 

[Primepixie]

Thanks John,
The second option was exactly what I needed. It left me with all the
policies that did not match neither field in T2.