append query creating new records

[tina]

Hi
I have created an append query to add serial number to table where LotNumber
in table matches lot in the query where serial number is found.
My append query sql is as follows

INSERT INTO dbo_DataLots ( SerialNumber )
SELECT [SERIAL SINGLE LOT].MaxOfSerial
FROM dbo_DataLots INNER JOIN [SERIAL SINGLE LOT] ON dbo_DataLots.LotNumber =
[SERIAL SINGLE LOT].Lot
WHERE ((([dbo_DataLots]![LotNumber])=[SERIAL SINGLE LOT]![Lot]));

when I run append it creates new records instead of appending data to
exsisting records .When i run query as select lot numbers do match
Any Ideas
thanks
Tina

 

[tina]

Hi
Thank you I was having a very blonde momemt, sorry to waste your time
I still can't quite get it right though how do I write update to bit
ie update SerialNumber in dbo_DataLots table with MaxOfSerial from Serial
single lot query where Lotnumber matches
Thanks
Tina

Hi Tina,

You need to make it an UPDATE query which updates records. An APPEND query
adds new records.

Bonnie
http://www.dataplus-svc.com

Hi
I have created an append query to add serial number to table where LotNumber
in table matches lot in the query where serial number is found.
My append query sql is as follows

INSERT INTO dbo_DataLots ( SerialNumber )
SELECT [SERIAL SINGLE LOT].MaxOfSerial
FROM dbo_DataLots INNER JOIN [SERIAL SINGLE LOT] ON dbo_DataLots.LotNumber =
[SERIAL SINGLE LOT].Lot
WHERE ((([dbo_DataLots]![LotNumber])=[SERIAL SINGLE LOT]![Lot]));

when I run append it creates new records instead of appending data to
exsisting records .When i run query as select lot numbers do match
Any Ideas
thanks
Tina

 

[John Spencer]

Perhaps what you need is the following. Warning this can be slow with large
sets of data.

UPDATE dbo_DataLots
SET dbo_DataLots.SerialNumber = DMAX("Serial","Serial Single Lot","Lot='" &
LotNumber & "'")

If LotNumber is not a text field, then remove the apostrophes from the DMAX
function call.

By the way, it is usually not a good idea to store this type of data since you
can calculate it when it is needed. Calculating it ensures that the value is
always current.

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

Hi
Thank you I was having a very blonde momemt, sorry to waste your time
I still can't quite get it right though how do I write update to bit
ie update SerialNumber in dbo_DataLots table with MaxOfSerial from Serial
single lot query where Lotnumber matches
Thanks
Tina

Hi Tina,

You need to make it an UPDATE query which updates records. An APPEND query
adds new records.

Bonnie
http://www.dataplus-svc.com

Hi
I have created an append query to add serial number to table where LotNumber
in table matches lot in the query where serial number is found.
My append query sql is as follows

INSERT INTO dbo_DataLots ( SerialNumber )
SELECT [SERIAL SINGLE LOT].MaxOfSerial
FROM dbo_DataLots INNER JOIN [SERIAL SINGLE LOT] ON dbo_DataLots.LotNumber =
[SERIAL SINGLE LOT].Lot
WHERE ((([dbo_DataLots]![LotNumber])=[SERIAL SINGLE LOT]![Lot]));

when I run append it creates new records instead of appending data to
exsisting records .When i run query as select lot numbers do match
Any Ideas
thanks
Tina