Unable to import from Excel into table with Multiple-field primary key

[pdeleeuw930]

Hi folks,

I am learning Access by doing and may have missed some important theoretical point.

I have a table in my database that contains sets of lumber prices from different time periods. Any given size of lumber comes in several grades and different grades have different prices.

A complete set of prices consists of a price for each Size/Grade combination. My table is to contain a growing number of complete price sets each associated with a date.

So my fields are these: DateTime (a date/time field), SizeName (text 10), GradeGroup (text 10), and Price (Double).

None of the fields alone would serve as a primary key but each combination of DateTime/SizeName/GradeGroup will be unique. Accordingly I assigned those three fields as being the primary key.

When I try to import this historical data from an excel spreadsheet which contains one price set, everything looks like it's going well. It correctly interprets my column headings, etc.. But when I push the "Finish" button it complains that it is rejecting 72 records (that would be every single one of them) because the key is not unique.

I have stripped this table of its relationships to other tables but the symptom remains the same. The table is empty as I do the experiment so there's no chance of a duplicate already in there. If I say "Go ahead anyway", it imports all the price records into their correct fields and every Date/Size/Grade combination is indeed unique.

What am I missing in my inexperience? Grateful for any ideas!

 

[Bryan]

I would add an autonumber field to your table and use that as the PK. Import the data from the spreadsheet and Access will automatically populate the PK.

 

[Pat Hartman]

There is no theoretical reason why this does not work but I do it differently. I use an autonumber as my primary key because single field PKs are easier to work with. I then create a unique index that includes all three fields to enforce the business rule. To make a multi-field index - open the index dialog.
1. On the first completely blank row
2. Type a name for the index in the first column
3. Choose the first field name in the next column
4. In the options at the bottom of the form set the index to unique
5. On the next row, leave the index name field blank and enter the second field name in the second column
6. On the next row, leave the index name field blank and enter the third field name in the second column.

You can create indexes (or primary keys) containing up to 10 columns. Other relational databases such as SQL Server allow more columns in an index.

Access knows the second and subsequent columns belong to the index because you left the index name blank so it assumes the most previous name.

FYI, unless you need more than four decimal digits, I would suggest using the currency data type rather than single or double. The default format will include a dollar sign but you can change it to whatever you want. Floating point arithmetic can result in scaling errors that cause .01 to not = .01 There is an article at www.fmsinc.com called "When Access Math doesn't add Up" that makes for interesting reading.

 

[pdeleeuw930]

This is great. I'll try it. Thanks also for the advice on data type. I have adopted it.