Two-way Unmatched Records Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 25 Nov 2007 15:27 GMT

I have two table linked on the field 'Range' and the following query
returns 'Field' values in one table that aren't in the other.

SELECT DISTINCT tblderivative_11thNov07.Range
FROM tblderivative_11thNov07 LEFT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE (((tblSector.Range) Is Null));

Is it possible to amend the query to *also* do the same thing but the
other way round i.e. values in tblSector but not in tblDerivative_11thNov07?

But also somehow to include a field which indicates which table is
missing which value?

Any help greatly appreciated....thanks, Jason

Reply to this Message

Douglas J. Steele - 25 Nov 2007 17:50 GMT

SELECT DISTINCT tblderivative_11thNov07.Range,
"Not in tblSector" As Comment
FROM tblderivative_11thNov07 LEFT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE tblSector.Range Is Null
UNION
SELECT DISTINCT tblSection.Range.
"Not in tblderivative_11thNov07" As Comment
FROM tblderivative_11thNov07 RIGHT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE tblderivative_11thNov07.Range Is Null
ORDER BY 2, 1

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I have two table linked on the field 'Range' and the following query
>returns 'Field' values in one table that aren't in the other.
[quoted text clipped - 12 lines]
>
> Any help greatly appreciated....thanks, Jason

Reply to this Message

Jay - 26 Nov 2007 22:45 GMT

*Man y* thanks for this Doug. I've never written a Union query - I know
'of them' as I've been teaching myself SQL, so to have a real-world
example is great. I've also not come across 'Comments' so this is a
fantastic learning example!

Does the 'ORDER BY 2, 1' refer to each query?

Regards.......Jason

> SELECT DISTINCT tblderivative_11thNov07.Range,
> "Not in tblSector" As Comment
[quoted text clipped - 8 lines]
> WHERE tblderivative_11thNov07.Range Is Null
> ORDER BY 2, 1

Reply to this Message

Marshall Barton - 25 Nov 2007 17:56 GMT

>I have two table linked on the field 'Range' and the following query
>returns 'Field' values in one table that aren't in the other.
[quoted text clipped - 9 lines]
>But also somehow to include a field which indicates which table is
>missing which value?

SELECT "11Nov07" As Tbl, tblderivative_11thNov07.Range
FROM tblderivative_11thNov07 LEFT JOIN tblSector
ON tblderivative_11thNov07.Range = tblSector.Range
WHERE tblSector.Range Is Null
UNION
SELECT "Sector", tblSector.Range
FROM tblderivative_11thNov07 RIGHT JOIN tblSector
ON tblderivative_11thNov07.Range = tblSector.Range
WHERE tblderivative_11thNov07.Range Is Null

Signature

Marsh
MVP [MS Access]

Reply to this Message