Append query adds way too many entries into table, instead of just

[BlueWolverine]

Hey,
Ms Access 2003 on XP Pro.

I have an append query that pulls a VIN, a Plate number, and today's date
and enters it into a history table.

t_PlateHistory contains VIN, Plate, Assigned, Unassigned with no primary key.

Here's the append query.
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN, Now()
AS [As]
FROM t_PlateHistory;


So, if the table had 10 records in it, this query runs, and enters 10
(correct) records into the table. If you do it again, it adds 20. (doubling
each time.)

Each run of the append query should only add 1 record.

I am going to try adding an autonumber as a unique key and see if that helps
but in the meantime, help!

 

[Lynn Trapp]

Hey,
Ms Access 2003 on XP Pro.

I have an append query that pulls a VIN, a Plate number, and today's date
and enters it into a history table.

t_PlateHistory contains VIN, Plate, Assigned, Unassigned with no primary key.

Here's the append query.
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN, Now()
AS [As]
FROM t_PlateHistory;


So, if the table had 10 records in it, this query runs, and enters 10
(correct) records into the table. If you do it again, it adds 20. (doubling
each time.)

Each run of the append query should only add 1 record.

I am going to try adding an autonumber as a unique key and see if that helps
but in the meantime, help!

This sounds like it is a bound form and you are selecting all records
from the table in your query. If it IS a bound form, then why do you
need to do the Insert query. If you still think you do, then you need
to add criteria to the query.

INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN,
Now()
AS [As]
FROM t_PlateHistory
WHERE Plate = <some value that identifies the record>;
Lynn Trapp
www.ltcomputerdesigns.com

 

[BlueWolverine]

How do I determine if the form is bound, and how do I undo it?

Thank you
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Hey,
Ms Access 2003 on XP Pro.

I have an append query that pulls a VIN, a Plate number, and today's date
and enters it into a history table.

t_PlateHistory contains VIN, Plate, Assigned, Unassigned with no primary key.

Here's the append query.
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN, Now()
AS [As]
FROM t_PlateHistory;


So, if the table had 10 records in it, this query runs, and enters 10
(correct) records into the table. If you do it again, it adds 20. (doubling
each time.)

Each run of the append query should only add 1 record.

I am going to try adding an autonumber as a unique key and see if that helps
but in the meantime, help!

This sounds like it is a bound form and you are selecting all records
from the table in your query. If it IS a bound form, then why do you
need to do the Insert query. If you still think you do, then you need
to add criteria to the query.

INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN,
Now()
AS [As]
FROM t_PlateHistory
WHERE Plate = <some value that identifies the record>;
Lynn Trapp
www.ltcomputerdesigns.com

 

[John Spencer]

Try the alternative syntax for an insert query.

INSERT INTO T_PlateHistory (Plate, Vin, Assigned)
VALUES (Forms!fAddPlate!LPN, Forms!fAddPlate!LPCIN, Now())

If you want just the date then use DATE() instead of Now()
Date() returns just the date
Now() returns the date and the time

Your query MIGHT work if you used the distinct keyword in the Select clause
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT DISTINCT Forms!f_AddPlate!LPN AS LPN
, Forms!f_AddPlate!LPVIN AS LPVIN
, Now() AS [As]
FROM t_PlateHistory;


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

 

[BlueWolverine]

Insert into
Values;

Worked fine, the distinct for some reason entered a single record with VIN
and Plate blank. SO I used values.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Try the alternative syntax for an insert query.

INSERT INTO T_PlateHistory (Plate, Vin, Assigned)
VALUES (Forms!fAddPlate!LPN, Forms!fAddPlate!LPCIN, Now())

If you want just the date then use DATE() instead of Now()
Date() returns just the date
Now() returns the date and the time

Your query MIGHT work if you used the distinct keyword in the Select clause
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT DISTINCT Forms!f_AddPlate!LPN AS LPN
, Forms!f_AddPlate!LPVIN AS LPVIN
, Now() AS [As]
FROM t_PlateHistory;


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

Hey,
Ms Access 2003 on XP Pro.

I have an append query that pulls a VIN, a Plate number, and today's date
and enters it into a history table.

t_PlateHistory contains VIN, Plate, Assigned, Unassigned with no primary key.

Here's the append query.
INSERT INTO t_PlateHistory ( Plate, Vin, Assigned )
SELECT Forms!f_AddPlate!LPN AS LPN, Forms!f_AddPlate!LPVIN AS LPVIN, Now()
AS [As]
FROM t_PlateHistory;


So, if the table had 10 records in it, this query runs, and enters 10
(correct) records into the table. If you do it again, it adds 20. (doubling
each time.)

Each run of the append query should only add 1 record.

I am going to try adding an autonumber as a unique key and see if that helps
but in the meantime, help!