I have a database containing invoice data imported from suppliers, which is updated each month.
I populate additional fields in the invoice table using an update query and a relationship to a lookup table. For example, products are categorised by product type. (The relationship is a match of supplier name and product description).
After the update I check the additional fields for blanks and if any exist, add a new record to the lookup table for that combination of supplier name and product description together with a product type. I then rerun the update query and the blank fields are then populated with values from the lookup table.
However I have noticed that of the 6,000 records in the lookup table there are 300 instances of duplicate records. When I inspect these duplicates they appear to be identical i.e. field lengths are the same and there are no leading or trailing spaces.
What might be happening here and what could I do to avoid it happening again?
Thanks.