Append query problem w/ duplicates

[John Daily]

I am having a problem with this append query and it
appending duplicate records. I have tried all sorts of
syntax to get it to not do that. I am in A2000 and that
is the only fields in the Payment Info table. I have even
tried to change the properties of the query and that still
didn't work.
-----------------
INSERT INTO [Payment Info] ( acct_num, total_charges,
total_pmts, total_adjst, acct_bal )
SELECT DISTINCT [CT Denials].pat_number, [CT
Denials].total_charges, [CT Denials].total_pmt_amt, [CT
Denials].total_adjst_amt, [CT Denials].acct_bal
FROM [CT Denials] INNER JOIN [Payment Info] ON [CT
Denials].pat_number = [Payment Info].acct_num;

 

[Ken Snell]

Duplicate records usually suggests that you need a second joining field
between the two tables. What you're seeing is a partial "cartesian" query
result because the query finds more than one record in the joined table that
matches the linked fields' values.

 

[John Daily]

How do I fix it? I don't really understand what you said
in your reply.

Thanks,

John

-----Original Message-----
Duplicate records usually suggests that you need a second joining field
between the two tables. What you're seeing is a partial "cartesian" query
result because the query finds more than one record in the joined table that
matches the linked fields' values.

--
Ken Snell
<MS ACCESS MVP>

I am having a problem with this append query and it
appending duplicate records. I have tried all sorts of
syntax to get it to not do that. I am in A2000 and that
is the only fields in the Payment Info table. I have even
tried to change the properties of the query and that still
didn't work.
-----------------
INSERT INTO [Payment Info] ( acct_num, total_charges,
total_pmts, total_adjst, acct_bal )
SELECT DISTINCT [CT Denials].pat_number, [CT
Denials].total_charges, [CT Denials].total_pmt_amt, [CT
Denials].total_adjst_amt, [CT Denials].acct_bal
FROM [CT Denials] INNER JOIN [Payment Info] ON [CT
Denials].pat_number = [Payment Info].acct_num;

.

 

[Ken Snell]

Does the joined table contain two foreign key fields, two fields that are
also in the main table? If yes, then make a join between these second
fields. Do this in the query design view by clicking on the key in the main
table, and dragging it on top of the corresponding foreign key in the joined
table.
--
Ken Snell
<MS ACCESS MVP>

How do I fix it? I don't really understand what you said
in your reply.

Thanks,

John

-----Original Message-----
Duplicate records usually suggests that you need a second joining field
between the two tables. What you're seeing is a partial "cartesian" query
result because the query finds more than one record in the joined table that
matches the linked fields' values.

--
Ken Snell
<MS ACCESS MVP>

I am having a problem with this append query and it
appending duplicate records. I have tried all sorts of
syntax to get it to not do that. I am in A2000 and that
is the only fields in the Payment Info table. I have even
tried to change the properties of the query and that still
didn't work.
-----------------
INSERT INTO [Payment Info] ( acct_num, total_charges,
total_pmts, total_adjst, acct_bal )
SELECT DISTINCT [CT Denials].pat_number, [CT
Denials].total_charges, [CT Denials].total_pmt_amt, [CT
Denials].total_adjst_amt, [CT Denials].acct_bal
FROM [CT Denials] INNER JOIN [Payment Info] ON [CT
Denials].pat_number = [Payment Info].acct_num;

.