Populate Table with Fields from Another Table?

[Ed]

I have 2 tables, Claims and Transactions. They have a 1 to many
relationship, 1 claim can have many transactions. The issue I am having is
that we are working backwards. Instead of inputting a claim and linking the
transactions to the claim, I am receiving a data feed of the transactions and
need to populate the claim table. I have PK ClaimNumber in my claim table
and have the same field within the transaction table.

I am thinking I would need a Find Dups query to get the unique claim numbers
from the transaction table and then append to claim table. Getting multiple
feeds a day, I am not sure how I would go about this properly to prevent
duplicating or missing claims.

Any info is greatly appreciated.

Thanks,
Ed

 

[Bob Barrows]

I have 2 tables, Claims and Transactions. They have a 1 to many
relationship, 1 claim can have many transactions. The issue I am
having is that we are working backwards. Instead of inputting a
claim and linking the transactions to the claim, I am receiving a
data feed of the transactions and need to populate the claim table.
I have PK ClaimNumber in my claim table and have the same field
within the transaction table.

I am thinking I would need a Find Dups query to get the unique claim
numbers from the transaction table and then append to claim table.
Getting multiple feeds a day, I am not sure how I would go about this
properly to prevent duplicating or missing claims.

Any info is greatly appreciated.

Ummm ... you've already received two replies to your original post.

 

[John Spencer]

A query like this would identify the new claim numbers

SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

So a query like this should insert any new claims
INSERT INTO Claims (ClaimNumber )
SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

I have 2 tables, Claims and Transactions. They have a 1 to many
relationship, 1 claim can have many transactions. The issue I am
having is that we are working backwards. Instead of inputting a
claim and linking the transactions to the claim, I am receiving a
data feed of the transactions and need to populate the claim table.
I have PK ClaimNumber in my claim table and have the same field
within the transaction table.

I am thinking I would need a Find Dups query to get the unique claim
numbers from the transaction table and then append to claim table.
Getting multiple feeds a day, I am not sure how I would go about this
properly to prevent duplicating or missing claims.

Oh, I see, you've changed the question. You probably should have replied
to your original post rather than starting a new thread. That would have
caused less confusion.

 

[Ed]

Sorry. Actually we were having some server issues here at the office and I
didn't see either question post to the forums.

 

[Ed]

Thanks, this worked perfectly.

A query like this would identify the new claim numbers

SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

So a query like this should insert any new claims
INSERT INTO Claims (ClaimNumber )
SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.

 

[Ed]

Thanks, this worked perfectly.

A query like this would identify the new claim numbers

SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

So a query like this should insert any new claims
INSERT INTO Claims (ClaimNumber )
SELECT DISTINCT TransActions.ClaimNumber
FROM Transactions LEFT JOIN Claims
ON Transactions.ClaimNumber = Claims.ClaimNumber
WHERE Claims.ClaimNumber is NULL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.