Merging two tables with identical fields using specific criteria

[natetheblade2]

Hi All,

I am not a super-star SQL writer or database programmer, so I am trying to figure out how to do something in Access.

I have two tables. Quarter 1 and Quarter 2. They both contain the same number and type of fields.

I would like a query that does the following:

1. Return the data from both tables without duplicates based on the fields for Business Unit and UniqueID#. The other fields are irrelevant for returning without duplicates.
2. When there is a conflict between what is in a field for Quarter 1 and what is in Quarter 2, the unique record returned should reflect Quarter 2 since it is the most recent.

I thought that one way to do that might be an unmatched query, but there is only an option in Access for using one variable for criteria and I want unique values based on the business unit AND the ID# in order to aggregate based on business unit since sometimes the unique ID# will change business units between quarters.

I then thought that a join might work, but it seems that it might leave out records from one quarter that did not appear in the other.

I then thought that a union might be appropriate, although how would that work for keeping what is in the fields of Quarter 2 in the event of a duplicate record match on business unit and ID# between the two?

Any suggestions?

 

[xenou]

I think you will need to be more specific about what fields you want returned (its confusing because you've described them as irrelevant but they also seem to be something you want as part of the results at the same time).

Also by definition duplicate means not the same. So you need to define what you mean by duplicate and whether there can be two or more than two "duplicates" (one record from each table -> two dupes possible, more than one record from one or both of the tables -> more than two dupes possible). Possibly, this just means answering the question "how are the two tables related?" and "is that relationship one-to-one, one-to-many, or many-to-many?".

If I were to try to suggest a line of thought for this it would be to create two datasets:
first, the records where there are no "dupes"
second, the records where there are "dupes" (taking only the unique one you want)
the result is the union of these two data sets. If you can find even one of them the other is everything minus the one you found. But this is off the top of my head.

 

[natetheblade2]

I think you will need to be more specific about what fields you want returned (its confusing because you've described them as irrelevant but they also seem to be something you want as part of the results at the same time).

Also by definition duplicate means not the same. So you need to define what you mean by duplicate and whether there can be two or more than two "duplicates" (one record from each table -> two dupes possible, more than one record from one or both of the tables -> more than two dupes possible). Possibly, this just means answering the question "how are the two tables related?" and "is that relationship one-to-one, one-to-many, or many-to-many?".

If I were to try to suggest a line of thought for this it would be to create two datasets:
first, the records where there are no "dupes"
second, the records where there are "dupes" (taking only the unique one you want)
the result is the union of these two data sets. If you can find even one of them the other is everything minus the one you found. But this is off the top of my head.

Sorry, I thought that I was clear. Let me try again.

Match based on Business Unit (BU) and ID# like so... (the rest of the fields are only irrelevant for the match, but the other fields are important if there is a difference between those fields on the same record if BU and ID# match)

Fake dataset example

Quarter 1 Quarter 2
BU -------- BU
ID# -------- ID#
Location Location*
Score Score*
Balance Balance*

*Keeps Quarter 2 value if the value of the field based on the record determined by the match between BU and ID# is different between quarters.

Returns data without duplicates in Quarter 1 and Quarter 2 based on BU and ID# match. If a record has the same business unit and ID#, then don't duplicate that record. Otherwise, return any records in either Quarter 1 or Quarter 2.

Does that help?

 

[xenou]

Hi,
I haven't tested (too lazy to create test data). But here is a try.

In order to keep it simple and hopefully perform well, I will create four queries separately. Once you create the first three, you just run the last one to see your results.

Assume the tables are called Quarter1 and Quarter2 and we have removed special characters and spaces from all table and field names since that's how I roll.

Query1: All BU/IDNum combinations

SELECT BU, IDNum FROM Quarter1
UNION
SELECT BU, IDNum FROM Quarter2

Query2: All Quarter2 records, including those that are in Quarter1

SELECT a.BU, a.IDNum, b.Location, b.Score, b.Balance
FROM
	Query1 a
	INNER JOIN 
	Quarter2 b
	ON a.BU = b.BU
	and a.IDNum = b.IDNum

Query3: All Quarter1 records where there is not a record in Quarter2

SELECT a.BU, a.IDNum, b.Location, b.Score, b.Balance
FROM
	((Query1 a
	LEFT JOIN 
	Quarter1 b
	ON a.BU = a.BU
	and a.IDNum = b.IDNum)
	LEFT JOIN 
	Quarter2 c
	ON b.BU = c.BU
	and b.IDNum = c.IDNum)
WHERE
	c.BU Is Null

Query4: The result

SELECT * from Query2 
UNION ALL 
SELECT * FROM Query3