Unmatch query help

[Angela]

Hi,

I have 2 tables with some numbers.

Both tables have duplicates.

I would like to return 2 unmatch queries.
First occurance of TableA number should match with first occurance of
TableB number.
First occurance of TableB number should match with first occurance of
TableA number.

TableA

09875464560
09875464561
09875464568
09875464560
09875464567
09875464560

------------------------
TableB

09875464560
09875464568
09875464560
09875464566
09875464561
09875464561

1st query Result: Remaining in A not in B

09875464567
09875464560

2st query Result: Remaining in B not in A

09875464566
09875464561

I hope I have explained it clearly as much as possible.
Looking forward for solutions.
Thanks.

 

[John W. Vinson]

First occurance of TableA number should match with first occurance of
TableB number.
First occurance of TableB number should match with first occurance of
TableA number.

One big problem here is that a relational database table is an unordered
"heap" of records. There's no such thing as "the first record", any more than
there is a "first" marble in a bag of marbles.

Do you have some way to specify the order of records in the tables, based on
the actual content of the table? Do the records come from some external source
which does impose an order?

 

[Ken Snell]

ACCESS has a built-in query wizard to assist you in building such unmatched
queries. Try using it as a starting point. Then you can see the SQL
statement that it builds for you, and that will guide you for how to build
such queries.

 

[Angela]

Hiee John,

Date column can be set to ascending order.. newest at last.

 

[John W. Vinson]

Hiee John,

Date column can be set to ascending order.. newest at last.

Please post your tablenames and fieldnames: a "frustrated outer join" query
will give you what you want but I'll need the names to give you the SQL.

 

[Angela]

Hey John,


TableA
TableB

In both tables
Field1 name: Date
Field2 name: NumberList

 

[John W. Vinson]

Hey John,


TableA
TableB

In both tables
Field1 name: Date
Field2 name: NumberList

SELECT TOP 1 TableA.*
FROM TableA LEFT JOIN TableB
ON TableA.[Date] = TableB.[Date]
WHERE TableB.[Date] IS NULL
ORDER BY TableA.[Date]

Then just reverse the roles of TableA and TableB.

Do note that Date is a reserved word and it can and will get you in trouble;
consider changing the fieldname.

 

[Angela]

Hey John,

The query is not returning the desired result.
Pls find below details:

TableA:

NumberList Datea
09875464568 04/10/2009
09875464567 04/10/2009
09875464560 03/10/2009
09875464561 02/10/2009
09875464566 03/10/2009
09875464566 08/10/2009

TableB:

NumberList Datea
09875464560 01/10/2009
09875464560 03/10/2009
09875464568 02/10/2009
09875464560 04/10/2009
09875464566 07/10/2009
09875464561 04/10/2009
09875464561 05/10/2009

-------------------------------------------------------------
1st query Result: Remaining in A not in B

NumberList Datea
09875464567 04/10/2009
09875464566 08/10/2009


2st query Result: Remaining in B not in A

NumberList Datea
09875464560 01/10/2009
09875464560 04/10/2009
09875464561 05/10/2009

Hope you can check this again.