Proper Primary Key Creation in Access Table

[Tambe257]

I'm looking for advice on the proper way to set up this table. This is a database for a chain of retail stores. I have a table called StorePrices, which creates a record of the price, cost, etc. for each item in each store. I have a primary key set up as an autonumber.

The issue that I have is that I get a lot of info that needs to either be appended or updated to that table from Excel sheets. My excel sheets would have the StoreID and ItemNum that both match up with corresponding StoreID and ItemID on the StorePrices table, but as of now I have not restricted the table to only allow one combo of storeID + ItemNum, so there could be multiple records. This makes it very difficult (impossible?) to do an update query.

My thought to remedy this is to change the primary key on StorePrices from the autonumber to a multi-column unique index on StoreID and ItemId, which I think I do by selecting both fields and then clicking the key icon in the toolbar. (I will always have that info when I update/append from another table) I also just read about composite key indexes, which might work.

Is this the proper way to set this table up long term? I've only been using Access for a month, so I don't know the future repercussions of some decisions.
Here is a visual image of the table layout form The StorePrices table and an example of the type of table I would be using to update it.





The other thought I had was to create a calculated field that adds StoreID + ItemID, since that I always have that info in other sheets. I couldn't restrict that field to indexed no duplicates, and that seems like redundant info, so I don't think that's correct. (in the image I show a field called ItemStoreCombo as an example)

[CJ_London]

I would not change the primary key as an autonumber, but give the store and item ids a composite index where duplicates are not allowed. You do this in table design, on the ribbon, click on indexes. The in the indexname column provide a name, anything you like (without spaces) - perhaps StoreItem. In the field name column, add StoreID and ItemID on two rows . Click back on the indexname and change Unique to Yes.

Calculated fields cannot be indexed

[Tambe257]

Ok, thanks. So I made that change to my StorePrices table, and as a test, I created this table called Summary that has some data that I want to try to add into the StorePrices table. I created the join from Summary to StorePrices, and created an update query. Viewing the query showed me the data I expected:



So I them hit "Run" to run the query. It said, "Updating 19 records", and I hit "ok". I went to look at the StorePrices table and nothing is updated. When I run the query again it's blank. Am I doing something wrong? Here is the query:

[CJ_London]

why do you expect the storeprices table to change? it is the summary table you are updating

[Tambe257]

why do you expect the storeprices table to change? it is the summary table you are updating

Sorry, rookie mistake. Thanks!