Compare two tables with 2 fields each, account from & account thru

[YANECKC]

Both tables have two fields
Account From & Account Thru
All four fields are 8 bytes

Table 1
Account From - Account Thru
00000001 00000004
00000006 00000011
00000018 00000018
00000019 00000023
00000024 00000100
00000101 00000103


Table 2
Account From - Account Thru
00000001 00000001
00000002 00000005
00000006 00000008
00000010 00000016
00000017 00000017
00000019 00000021
00000027 00000030

I need three results
Matched on both
On Table 1 only
On Table 2 only

YANECKC

 

[alansidman]

Given the example you have provided, what should the end result look like? I am not sure what you are asking for.

 

[Micron]

For the first requirement, create a select query with equal joins between table 1 and table 2 ([Account From] on [Account From] and [Account Thru] on [Account Thru]).
For the second and third, I would agree with alansidman. I'm guessing you want where the values in one are not in the other. If so, use the unmatched values query wizard to get what you need.

 

[YANECKC]

Account From - Account Thru / Results
00000001 00000004 / Matched on Both Table1 & Table2
00000005 00000005 / On Table2 Only
00000006 00000008 / Matched on Both Table1 & Table2
00000009 00000009 / On Table1 Only
00000010 00000011 / Matched on Both Table1 & Table2
00000012 00000017 / On Table2 Only
00000018 00000018 / On Table1 Only
00000019 00000021 / Matched on Both Table1 & Table2
00000022 00000026 / On Table1 Only
00000027 00000030 / Matched on Both Table1 & Table2
00000031 00000103 / On Table1 Only

YANECKC

 

[YANECKC]

See my previous post is a sample of what I want the output to look like.
Any help would be appreciated
YANECKC

 

[Micron]

I look at the first two examples from post #4 and see that 00000005 is only in one field of table 2 in post #1 yet you seem to be asking for it to be returned in two columns. Also in #4, you say 00000001 00000004 / Matched on Both Table1 & Table2, yet 00000004 is only in one table. So I'm confused by what you say you want and what you are trying to do. I think the lack of replies suggests mostly everybody else is too. Do you have any feedback from suggestions in #3?

 

[YANECKC]

Yes your three post is really what I am looking for unmatched from each table. Can the unmatched wizard find using the logic when a table has individual accounts as well as account ranges from this account thru (to) this account.
Meaning would it be able to tell me account 00000009 is only in table1?

 

[Micron]

Yes, if there is common data between two tables or query results, you can find missing values based on criteria where the data you're looking for is a field in both. Give it a try and follow the directions. You may not get what you want on the first attempt, but viewing results should give you more understanding to how it works. You can apply that and tweak the inputs as needed.