Unique Records

[Bev]

I have a data base that has 2 tables. One is summarized by date and by
amount. Each date can have multiple amounts or the same amounts multiple
times (such as 10/19/2009: 10,500
10/19/2009: 10,500
10/19/2009: 15,000

The second table has a similar layout and can also have the same iuues:
10/19/2009: 10,500
10/19/2009: 15,000
10/19/2009: 20,000

No matter how I join the tables - even with an union join - I am getting
multiple records - for example:

Date table 1 table 2
10/19/2009 10,500 10,500
10/19/2009 10,500 10,500 (duplicate as 10,500
appears only once in table 2)

I need to compare these tables to see which records are in 1 table but not
the other .. I know there has to be a way, but I can not configure the joins.

 

[Jerry Whittle]

You could use the Find Unmatched Query Wizard. You might have to use it twice
to find unmatched records in both tables.

You could also do a UNION ALL (not just a Union query) to combine all the
records from both tables.

Make each part of the SQL statement start out with SELECT DISTINCT to not
show duplicates within the table.

Put in a field like TheTable: "TableA" in both parts of the SQL statement
so that you can tell which table the records came from.

Name the union query. Then create a Totals query based on the Union query.
Do a count of the records and make the criteria <2. Then do a Max on the
TheTable field and it will show the table that HAS a record not in the other
table.

 

[John W. Vinson]

I have a data base that has 2 tables. One is summarized by date and by
amount. Each date can have multiple amounts or the same amounts multiple
times (such as 10/19/2009: 10,500
10/19/2009: 10,500
10/19/2009: 15,000

The second table has a similar layout and can also have the same iuues:
10/19/2009: 10,500
10/19/2009: 15,000
10/19/2009: 20,000

No matter how I join the tables - even with an union join - I am getting
multiple records - for example:

Date table 1 table 2
10/19/2009 10,500 10,500
10/19/2009 10,500 10,500 (duplicate as 10,500
appears only once in table 2)

I need to compare these tables to see which records are in 1 table but not
the other .. I know there has to be a way, but I can not configure the joins.

Since there is nothing to uniquely identify a record in the first table, at
least not that you've posted, I don't know if there is ANY way to get the
result you want. Every table should have a Primary Key... for this very
reason, to uniquely identify the record. Is there such a key in your tables?

 

[Bev]

John -

no there is not a key - but what if I let access create a key - is there a
way then to ensure I do not get duplicate records.

I really do not want to match these records by hand in EXCEL

Thanks,
Bev

 

[John W. Vinson]

John -

no there is not a key - but what if I let access create a key - is there a
way then to ensure I do not get duplicate records.

You can put a unique Index on a combination of up to ten fields... but I
really don't understand! Your Table1 *ALREADY* has duplicate records; perhaps
I misunderstood the question, I thought that you were trying to match each
record in Table1 up to its corresponding record (or records) in Table2. That
cannot be done if you cannot uniquely identify the record in Table1, and - at
present - it seems that you cannot do so.

Could you step back a bit and describe the actual problem space? What do these
records represent, and what is your desired final product?

 

[Bev]

John -
this is my problem.
In Table 1, I have dates and amounts paid on that date. There could be a
situation where three seperate invoices for 10,500 are paid on the same date.
In table 2, from another source, I have similar data, dates and amount paid.
But in this table, I may only have 2 seperate invoices paid for 10,500.
What I want to see is on a particular date, which transactions in table 2 do
not match table 1.
Because I can not use date and amount as the key - since this would give me
duplicate records - even though they aren't duplicates, when I join the two
tables I get incorrect data.
If I had the invoice number, which I do not, I could use it as the key.

 

[John W. Vinson]

John -
this is my problem.
In Table 1, I have dates and amounts paid on that date. There could be a
situation where three seperate invoices for 10,500 are paid on the same date.

So is there ANY other information about these three payments, e.g. who paid
whom? Or are these three absolutely indistinguishable identical records?

In table 2, from another source, I have similar data, dates and amount paid.
But in this table, I may only have 2 seperate invoices paid for 10,500.

Give that information and NO other information, how can you determine which
one is which? Or does it matter?

What I want to see is on a particular date, which transactions in table 2 do
not match table 1.
Because I can not use date and amount as the key - since this would give me
duplicate records - even though they aren't duplicates, when I join the two
tables I get incorrect data.

But they ARE duplicates. You have two records with the same date, and the same
amount, and no other information. These two records are identical. They are
duplicates.

If I had the invoice number, which I do not, I could use it as the key.

True. But you don't. And if you have anything ELSE in the table which would
resolve the duplication, you haven't posted it.

Think about a non-computer situation: you have a stack of IOU's on a table
with "IOU $10,500, 5/12/2007" and "IOU $10,500, 5/12/2007". You have a sheet
of paper in your hand with "Owed $10,500, 5/12/2007".

Which of the two IOU's does it reference?