Append Writing Duplicate Records... 1

[chriscriswell]

Hi folks, I know I must be just not seeing something that's wrong but I just can't see it...

I have three tables... master gage, gagehistory, and gageout. All have deviceID field. Mastergage and gageout have deviceID as primary w/no dup, gagehistory deviceID as foreign with dups allowed (this table stores the data for archiving only; needs to have multiple records with same deviceID as each record is a different event recorded).

Process goes like this... user logs out gage, written to gageout. Gage comes back, user logs gage in, adds event info, then hits button. Button fires macro that saves completed record to gageout, opens append query which uses criteria of "date in" to filter only updated records to append. Appends to gagehistory, then deletes record from gageout.

It seems to work well except for one confusing thing... the append writes the exact same record multiple times (two,three times at a shot) to gagehistory. That is, at any time there will be only one record being appended... but that same record will appear duplicated several times in the target table.

What am I doing wrong?

 

[JonFer]

Hard to say without seeing the append query. Why are you filtering on the "date in" instead of the deviceID for the append? If you have a multi-user system, you could have situations where two records with the same "date in" date exist in gageout for a short time, especially if the user saves a record without using your button.

 

[MichaelRed]

It would seem like an update query would be more appropiate.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[chriscriswell]

Hi guys, thanks for responding. First of all, here is the sql from the query:

INSERT INTO tbl_Calibration_Record
SELECT tbl_GageOut.*
FROM tbl_GageOut LEFT JOIN tbl_Calibration_Record ON tbl_GageOut.[Device ID] = tbl_Calibration_Record.[Device ID]
WHERE (((tbl_GageOut.[Date In]) Is Not Null));

My reasons for this logic (so far..) is that I needed an append query to add the record because there will be multiple records with the same DeviceID in the calrecord table (but with different results, dates, and such); each of these shows the discrete results of calibration for that gage deviceID. For instance, then, looking up "99-999" in the calrecord table will show you all individual calibration results, dates, and comments on "99-999". This makes it easy to pull up "99-999" and scroll through that device's calibration history. I am filtering on "Date In" because there will only ever be one session open at a time, when the gage is being logged back in. The macro logic is that since there will only ever be one record in the gageout table with a return date at a time, that record will be the only one chosen to be appended to the history (calrecord) table before it is deleted. This way, there are never records with "datein" populated in gageout for more than the time it takes to query, append, then delete. This also makes it easy to report or explain the gageout; only gages actually "out" live there.

This sequence seems to work, except for this strange multiple append problem. For instance, "99-999" might come back and be logged in with today's date. However, the query might write that exact same "99-999" record with all it's data (dates, results, etc) anywhere from two to eight times to the calrecord table-there doesn't seem to be any pattern to how may times it duplicates the same record.

Needless to say, I'm stumped. Any suggestions or hints will be appreciated, and if I'm off on my logic or table design please call me on it too!

 

[MichaelRed]

looks like you shoud get one new record in tbl_Calibration_Record for each existing record in tbl_GageOut. You would need additional criteria in the where clause, such as having the tbl_GageOut [DateIn] be greater than some value (or field in tbl_Calibration_Record)




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JonFer]

You shouldn't be joining to the calibration record table in your select statement. That is causing you to append as many new records for a device as there are already in the calibration table. It doesn't look like you use any fields from it in your insert so just do this:

INSERT INTO tbl_Calibration_Record
SELECT tbl_GageOut.*
FROM tbl_GageOut
WHERE tbl_GageOut.[Date In] Is Not Null;

 

[chriscriswell]

Doh! Thats it, JonFer! Thanks for the Christmas present! Now I can start pasting back in the hair i've pulled out....