Perform an action on each record in a table in turn

[Bogzla]

OK, usually I can figure these things out for myself, but I'm realy stuck on this one and any help would be appreciated

background: I work in a small scale pilot production line, and we take a series of measurements for every part that we make, each of which has a unique ID. Sometimes we need to take the measurements more than once, so that the data output from the measurement system (which it can append straight to an access table) has a part ID and a measurement number, the combination of which is unique (for reasons that I won't go into it is not possible to combine them into a single field, otherwise we would do this)

problem: The measurement system can be temperamental and sometimes sends the same record more than once, and It is horribly time consuming to go through and check manually
It occured to me that we could send the measurements to table1 (where they would be stored temporarily), and write a sub to go through each record in table1 in turn, compare the part ID and measurement number to the records stored in table2 (where they would be stored permanently), and append the record to table2 only if the part ID and measurement number combo has not already been taken, before moving onto the next record in table1 and doing the same thing

The part I just can't figure out is how to go through the records in turn and compare them, I can do the append and deletion using SQL, that's not a problem.

if anyone can point in the right direction I'd be realy grateful (or tell me if I'm barking up the wrong tree completely)

regards
Bogzla

 

[Bodisathva]

make a compound primary key comprised of both the PartNumber and the Measurement...you cannot append the same combination twice because of the table structure. If you run an insert query with the warnings turned off, you will not be notified.

 

[Bogzla]

Thanks, I actually didn't realise that was possible (and now I feel stupid. Ah well). Cheers for the help

 

[Bogzla]

Well, I've got this working fine, and I'm also sending every record to a backup table with no primary key before I delete them (just in case). I was just wandering... as I have warnings switched off, is there an easy way to create a log of the records which could not be appended due to key violations?

 

[Bogzla]

Well, I've got this working fine, and I'm also sending every record to a backup table with no primary key before I delete them (just in case). I was just wandering... as I have warnings switched off, is there an easy way to create a log of the records which could not be appended due to key violations?

 

[The_Doc_Man]

The other way to do this is to NOT define a key for this table, but to copy it to a table with the keys defined with no duplication. Run the copy via a query. Allow Access to write a table with error messages that tell you when the duplications (actually, it would be logged as a KEY VIOLATION) occurred.

The table will be created AND you will get your log file.