Best way to format table of 1 to multiple objects?

[hagamablabla]

Hello, I am a completely beginner when it comes to Access. I am trying to convert an Excel worksheet to Access. The worksheet contains a list of item IDs, and multiple aliases of those item IDs. For example, the item ID I use is ABC123, the ID of a supplier is LS-ABC123, and the ID of a customer is MZ-999. Each alias of an item has its own row, so the two aliases for ABC123 would each have their own rows. This means that I can't use my item ID as a unique key, because there are multiple copies of the same item ID in the column. What is the best way to arrange this data in an Access table so that it can still recognize that the multiple aliases belong to a single item ID?

[ranman256]

Import the table and the existing IDs.
create a table and key it so you only have unique records.
add an autoNum to the table to give them an ID.
using the common field in both, update your data with the newly made autonums.

[Preston]

You can't squeeze your Excel tab into a working Access database. You need to transform your data so that it fits normalization rules. Your entities are Item, Supplier, and Customer. Do you have multiple suppliers and customers for a single Item_ID? If so, you need a table for Item_ID, a table for Supplier_ID and and table for Customer_ID. You then need to tie Item_IDs to Supplier_IDs with a table containing ITem_ID relating to your Items table and Supplier_ID relating to your supplier table. You need another separate table to tie the items to customers in the same fashion.

If there is one supplier and one customer for each Item_ID, it's simpler, you can have a table with Item_ID as PK, and Supplier_ID and Customer_ID as separate fields. You'll still want separate reference tables for Suppliers and Customers.

If you can't do this, you're better off leaving it in Excel.

[hagamablabla]

You can't squeeze your Excel tab into a working Access database. You need to transform your data so that it fits normalization rules. Your entities are Item, Supplier, and Customer. Do you have multiple suppliers and customers for a single Item_ID? If so, you need a table for Item_ID, a table for Supplier_ID and and table for Customer_ID. You then need to tie Item_IDs to Supplier_IDs with a table containing ITem_ID relating to your Items table and Supplier_ID relating to your supplier table. You need another separate table to tie the items to customers in the same fashion.

If there is one supplier and one customer for each Item_ID, it's simpler, you can have a table with Item_ID as PK, and Supplier_ID and Customer_ID as separate fields. You'll still want separate reference tables for Suppliers and Customers.

If you can't do this, you're better off leaving it in Excel.

So if I have multiple suppliers and customers, would the best option be to make one table for my item IDs, and then multiple tables for each supplier and customer? Would searching for an alias become more time-consuming with all these different tables, since the search would have to go through each supplier and customer's table to see if there is an alias.

Also, I have been reading a little and I learned about queries. Could I use a query to consolidate all the aliases of each item ID into one location?

[Preston]

3 main tables: Items, Suppliers, Customers. Then a join table, assuming that the Supplier and Customer are interdependent (you have an Item with a supplier and customer combination rather than an iindependent list of customers and an independent list of suppliers. The join table would contain Item_ID, Supplier_ID, and Customer_ID. If Supplier and Customer are independent, then you would simply have two join tables, ItemsSuppliers and ItemsCustomers.
If they are dependent, you wouldn't need a query. The join table would contain what you need. Of course, you should want more information about your entities, so you would use a query joining back to the entity tables to pull in Item descrip, Supplier Name, Customer Name, etc.

Item1......Supplier1......Cust1
Item1......Supplier1......Cust2
Item1......Supplier2......Cust3
Item2......Supplier3......Cust2

etc.
Alternatively, you could create a report with a subreport, that listed each item, then below it listed the various supplier and customer combos with that item.

[hagamablabla]

Forgot to mention, suppliers and customers are independent.

Would a single table for item ID/supplier ID work? There can be multiple suppliers for each item ID, so I wouldn't be able to use the item ID as a key in that table. Same problem with customers obviously.

[Preston]

The ItemID would be a foreign key in that table, pointing toward the Items table where the ItemID would be the Primary. If you are storing no other information about the Item except an ID, the Items table would be moot.

If they are completely independent of one another, normalization would dictate that you have a table relating Item to Supplier and a table relating Item to Customer. You could also have a table with ItemID, RecordType, and RelatedID. RecordType would be Supplier or Customer.

So you would have

Item1.....Supplier.....Supp1
Item1.....Customer...Cust1
Item2.....Supplier.....Supp2
Item2.....Supplier.....Supp3
Item2.....Customer...Cust2

[hagamablabla]

Ok, I see what to do now. Thank you for the help.