Append query duplicates records

  • Thread starter Thread starter Jason Morin
  • Start date Start date
J

Jason Morin

Hello. A friend of mine has the following situation:

She has database with a linked table that is actually a
link to an Excel spreadsheet. When you click a form
button, it does an append query of the data in the XL
worksheet into an Access table with the same field names.
It also adds a date/time stamp into an additional field
after every append.

The problem: If you press the button *3* times (ie run
the append query), the records get duplicated. The 4th
time they get quadrupled!

Example: XL worksheet has 34 data rows + headers. Press
form button. 34 rows are appended with date/time stamp.
Press form button 2nd time. 34 same rows + new date/time
stamp = 68 total rows in table. So good so far. Again a
*third* time and *68* rows get appended, not 34! Press
the button a 4th time, 136 rows get appended!

What's happening?

TIA
Jason
 
My guess (and it's strictly a guess, as you didn't tell us any information
about the append query's SQL statement) is that her query is using some type
of join with another table (perhaps the table to which the data are being
appended?), and that join makes the query return a record for each record in
that joined table... thus, you get "multiple" records if the joined table
has more records after the append query is run than it did when it was first
run (which is why I wonder if that joined table is the table to which the
data are being appended).

My guess, then, is that the append query needs to be modified. If you can
post the SQL statement that she's using, and tell us what she wants to
accomplish, I'm sure we can suggest how to change it.
 
Thanks Ken. Sorry for the lack of info. Here's more:

1. The append query (called "Append to Total") has the
Excel table (called "Productivity Report")joined to 2
other tables, one of them named "Total Productivity", the
*same* table it is appending to. Hmmm...

2. The linked field is called "User ID".

3. Here's the SQL statment:

INSERT INTO [Total Productivity] ( [USER ID], ACTIVATED,
DEACTIVATED, [STGD FR RCVD], [STGD FR LDD], [LDD FR
RCVD], [LDD FR STGD], [TOTAL MOVED], [SCANNED TO MSTR],
[MSTR%], [DIRECT LOAD%], [PO's UNRCVD], [OS&D PO's], [IB
CARTONS], [OB LDS REOPENED], Shift, [Date], [Time] )
SELECT [Productivity Report].[USER ID], [Productivity
Report].ACTIVATED, [Productivity Report].DEACTIVATED,
[Productivity Report].[STGD FR RCVD], [Productivity
Report].[STGD FR LDD], [Productivity Report].[LDD FR
RCVD], [Productivity Report].[LDD FR STGD], [Productivity
Report].[TOTAL MOVED], [Productivity Report].[SCANNED TO
MSTR], [Productivity Report].[MSTR%], [Productivity
Report].[DIRECT LOAD%], [Productivity Report].[PO's
UNRCVD], [Productivity Report].[OS&D PO's], [Productivity
Report].[IB CARTONS], [Productivity Report].[OB LDS
REOPENED], Associates.Shift, Forms!frmLoad!txtToday AS
Datefield, Forms!frmLoad!txtime AS Timefield
FROM [Total Productivity] RIGHT JOIN (Associates RIGHT
JOIN [Productivity Report] ON Associates.EmployeeID =
[Productivity Report].[USER ID]) ON [Total Productivity].
[USER ID] = [Productivity Report].[USER ID];
 
To answer your last question, she simply wants to keep
appending the Excel table into the main table. The Excel
file is a report generated 3 times per day. The fields
are always the same and in the same order, but the no. of
records can fluctuate. She wants to append this report
each time and stamp a date/time to each record when the
form button is pressed.

Jason
 
As I had suspected, the append query contains a join to the table to which
it's appending the data.

The query is appending to [Total Productivity] table:
INSERT INTO [Total Productivity]

And the query is selecting records from [Total Productivity] table.
FROM [Total Productivity]


This is where the "multiple" records arise.

Change the query to this:

INSERT INTO [Total Productivity] ( [USER ID], ACTIVATED,
DEACTIVATED, [STGD FR RCVD], [STGD FR LDD], [LDD FR
RCVD], [LDD FR STGD], [TOTAL MOVED], [SCANNED TO MSTR],
[MSTR%], [DIRECT LOAD%], [PO's UNRCVD], [OS&D PO's], [IB
CARTONS], [OB LDS REOPENED], Shift, [Date], [Time] )
SELECT [Productivity Report].[USER ID], [Productivity
Report].ACTIVATED, [Productivity Report].DEACTIVATED,
[Productivity Report].[STGD FR RCVD], [Productivity
Report].[STGD FR LDD], [Productivity Report].[LDD FR
RCVD], [Productivity Report].[LDD FR STGD], [Productivity
Report].[TOTAL MOVED], [Productivity Report].[SCANNED TO
MSTR], [Productivity Report].[MSTR%], [Productivity
Report].[DIRECT LOAD%], [Productivity Report].[PO's
UNRCVD], [Productivity Report].[OS&D PO's], [Productivity
Report].[IB CARTONS], [Productivity Report].[OB LDS
REOPENED], Associates.Shift, Forms!frmLoad!txtToday AS
Datefield, Forms!frmLoad!txtime AS Timefield
FROM Associates RIGHT JOIN [Productivity Report] ON Associates.EmployeeID =
[Productivity Report].[USER ID];

--

Ken Snell
<MS ACCESS MVP>




Jason Morin said:
Thanks Ken. Sorry for the lack of info. Here's more:

1. The append query (called "Append to Total") has the
Excel table (called "Productivity Report")joined to 2
other tables, one of them named "Total Productivity", the
*same* table it is appending to. Hmmm...

2. The linked field is called "User ID".

3. Here's the SQL statment:

INSERT INTO [Total Productivity] ( [USER ID], ACTIVATED,
DEACTIVATED, [STGD FR RCVD], [STGD FR LDD], [LDD FR
RCVD], [LDD FR STGD], [TOTAL MOVED], [SCANNED TO MSTR],
[MSTR%], [DIRECT LOAD%], [PO's UNRCVD], [OS&D PO's], [IB
CARTONS], [OB LDS REOPENED], Shift, [Date], [Time] )
SELECT [Productivity Report].[USER ID], [Productivity
Report].ACTIVATED, [Productivity Report].DEACTIVATED,
[Productivity Report].[STGD FR RCVD], [Productivity
Report].[STGD FR LDD], [Productivity Report].[LDD FR
RCVD], [Productivity Report].[LDD FR STGD], [Productivity
Report].[TOTAL MOVED], [Productivity Report].[SCANNED TO
MSTR], [Productivity Report].[MSTR%], [Productivity
Report].[DIRECT LOAD%], [Productivity Report].[PO's
UNRCVD], [Productivity Report].[OS&D PO's], [Productivity
Report].[IB CARTONS], [Productivity Report].[OB LDS
REOPENED], Associates.Shift, Forms!frmLoad!txtToday AS
Datefield, Forms!frmLoad!txtime AS Timefield
FROM [Total Productivity] RIGHT JOIN (Associates RIGHT
JOIN [Productivity Report] ON Associates.EmployeeID =
[Productivity Report].[USER ID]) ON [Total Productivity].
[USER ID] = [Productivity Report].[USER ID];

---
Thanks for your assistance.
Jason
-----Original Message-----
My guess (and it's strictly a guess, as you didn't tell us any information
about the append query's SQL statement) is that her query is using some type
of join with another table (perhaps the table to which the data are being
appended?), and that join makes the query return a record for each record in
that joined table... thus, you get "multiple" records if the joined table
has more records after the append query is run than it did when it was first
run (which is why I wonder if that joined table is the table to which the
data are being appended).

My guess, then, is that the append query needs to be modified. If you can
post the SQL statement that she's using, and tell us what she wants to
accomplish, I'm sure we can suggest how to change it.
--

Ken Snell
<MS ACCESS MVP>





.
Click to expand...
Click to expand...
 
Back
Top