DB Design & Table Setup Help

[Zanzibarjones]

tblPartColors is only the destination table, so you want a unique ID to be created for the primary key field. But in using the DISTINCT key word, you should not have more than one incoming record with the same combination of partID and colorID.

I do though. i will have to look at the code closer to make sure I am not making any mistakes. Maybe there is something wrong with the data I am importing, or the structure of the data.

[jzwp11]

Can you post the data?

[Zanzibarjones]

Yup, I sure can.
What data would you like to see? And how should I post it?

[jzwp11]

If you have the imported data in a table already in the database, it would be best to post the database and I can see exactly what you have. Of course, you should remove/change any sensitive info. I would run the compact & repair utility on the database and then zip it prior to attaching it. To attach a file, just click on the paperclip icon above where you type your response.

[Zanzibarjones]

Ok, Here ya go. The tbl_PartsList_Import already has data in it ready to go.

[jzwp11]

I'll have to take a look at it this evening. I only have Access 2003 here at work.

[Zanzibarjones]

Ok, and just so you know, I left out the DISTINCT from qry_PartsList_Import just because I was messing around with it. Forgot to put it back in.

[Zanzibarjones]

Ok, So I think I may have figured it out, logically not programatically.
The query selects distinct records from the tbl_PartsList_Import and puts them into the tblPartColors.

The problem lies in the query. Every time I run it, it is only select the distinct record in the import table. It does not compare the distinct records to the records already imported into the TblPartColors. Therefore, duplicates can and will happen.

So, is there anyway to rewrite that query so it looks for dupes in the tbl it is being appended to?

[jzwp11]

The problem lies in the query. Every time I run it, it is only select the distinct record in the import table. It does not compare the distinct records to the records already imported into the TblPartColors. Therefore, duplicates can and will happen.

You can create an index for tblPartColors based on both partID and colorID and make it unique (no duplicates).

It sounds like you are doing multiple appends from different source data, correct?

[Zanzibarjones]

Yes I will have to. The only way to get the parts lists for each set into the tables correctly is to either import each set individually, or to enter each part and color separately via a form.
I think it will be quicker to do each set individually and just check for dupes as I am appending.

[jzwp11]

Setting up the index will weed out any duplicates for you as you do each append.

[Zanzibarjones]

Ok cool, working on that now

[jzwp11]

If you have your data in spreadsheets, I would probably create 1 large spreadsheet with all the part/color/set info for all sets (in 1 sheet) then import that sheet into Access, then select the distinct part number/colors and use that for the append rather than doing each set separately.

[Zanzibarjones]

Ok, that is not a bad idea... I will work on that.