Is there a way to autogenerate records from relationships in MS Access?-CodePudding

I have a table named Stores that has the key StoreID, and a table named Items that has the key ItemID, and I need to make a table called Stock that uses both StoreID and ItemID as a compound key.

Can I make it so records are automatically generated for each combination of StoreID and ItemID instead of having to manually create each record for every combination of the keys?

(Preferably without using code/queries/etc, but using code is fine too.)

CodePudding user response：

Can't do any kind of automation without some kind of code - VBA and/or SQL. Consider:

INSERT INTO Stock(StoreID, ItemID) SELECT StoreID, ItemID FROM Stores, Items

I would avoid compound key if at all possible. Can still set the two fields as compound index to prevent duplicate pairs and have an autonumber field as primary key.

CodePudding user response：

Here is an SQL Solution:

combine a cross-join and a make table query:

'resulting sql.  stock is set to have a composite primary key
SELECT CLng([Items.ItemID]) AS ItemID, CLng([Stores].[StoreID]) AS StoreID INTO Stock
FROM Items, Stores;

setting a composite primary key is a tiny bit tricky so here is one guide: https://www.techwalla.com/articles/creating-composite-keys-tutorial-for-ms-access

Shout out to Jeff Boyce for the fix for Access bugging about 2 autonumber columns. I cast the columns to long then alias them back to their proper names.