Data being Duplicated

[Guest]

Hello all,

I have a form I use to append the transactions from table 1 to table 2. On
the form I have a button to run an append query. For some reason, the
transactions are being duplicated in the table 2 from time to time after the
append.

Would anyone have any idea on what causing it to happen?

Thanks.

 

[Guest]

Without knowing the problem with the data you are appending, to avoid a
duplicate problem, first you should create a key in the table with no
dupliates.
That way even if you make a mistake the data wont be inserted in the table.

 

[Guest]

Transactions are the employee's t&e. I have two keys created. One is
Autonumber and the other one is a combination of date and employee #. The
Autonumber is my primary key. I use the combo of date and employee # to
catch duplicates. For example, if the combo already exists in table 2, my
append query would not append.

I also have a field for date modified. The strange thing is the date and
time in the date modified field are the same for all records. Apprently,
they all get appended at the same time.

Would Access creates duplicates if a network connection fails or is taking
longer than expected?

Thanks.

 

[Guest]

In this cases it shouldn't create a duplicate, if any thing it wont append
all the records.

In the SQL you use to append the records, do you have any criteria? mybe you
are not using any criteria as employee # to append the reords.

A use of another table in the select query beside table 1, might cause
duplicates, if the field you are using to link the two tables repeat it self
few times in the second table.

 

[Guest]

In my query, I join (LEFT JOIN) Table 1 to Table 2 (employee # and date) and
Table 1 to Table 3.

Table 3 is used to store the combo of date and employee #. I have an append
query to append the combo to Table 3 from Table 2 when I click on the button
(macro with mulitiple queries) to append the t&e transactions on the form.
Then my t&e append will check to make sure that the combo is not already in
Table 3. The combo is in all 3 tables.

The criteria that I have in the t&e append query are Is Not Null for both
date and employee # and Not In ([Table 3]) for the combo.

Based on my current set up, I don't see any duplicates would be created. I
append a lot of transactions every week. However, not all the transactions
that I append create duplicates, only a few. What I don't understand is
duplicates should not be created based on my current set up.

Thanks again for your help. Any more ideas?

 

[Guest]

In the select section if the SQL add distinct and see if it remove the
duplicates

INSERT INTO TableName1 ( Field1, Field2 )
SELECT Distinct TableName.Field1, TableName.Field2
FROM TableName

--
Good Luck
BS"D

In my query, I join (LEFT JOIN) Table 1 to Table 2 (employee # and date) and
Table 1 to Table 3.

Table 3 is used to store the combo of date and employee #. I have an append
query to append the combo to Table 3 from Table 2 when I click on the button
(macro with mulitiple queries) to append the t&e transactions on the form.
Then my t&e append will check to make sure that the combo is not already in
Table 3. The combo is in all 3 tables.

The criteria that I have in the t&e append query are Is Not Null for both
date and employee # and Not In ([Table 3]) for the combo.

Based on my current set up, I don't see any duplicates would be created. I
append a lot of transactions every week. However, not all the transactions
that I append create duplicates, only a few. What I don't understand is
duplicates should not be created based on my current set up.

Thanks again for your help. Any more ideas?

In this cases it shouldn't create a duplicate, if any thing it wont append
all the records.

In the SQL you use to append the records, do you have any criteria? mybe you
are not using any criteria as employee # to append the reords.

A use of another table in the select query beside table 1, might cause
duplicates, if the field you are using to link the two tables repeat it self
few times in the second table.