Table design help

[Guest]

We are moving an Excel spreadsheet to Access.

The spreadsheet tracks data on stores. Each store has a main supplier.
Some stores have a main supplier and 1 or more secondary(Sec) supplier.

I need to create a report that shows:

Main Supplier Supplier Acct # Store Name Address
1st Sec Supplier 1st Sec Acct # Same Store Name Same Address
2nd Sec Supplier 2nd Sec Acct # Same Store Name Same Address

The table currently has a Separate field for the Main Supplier and the
Account # for that Main Supplier. The Secondary suppliers have a Field for
their name and the Account # is place in that field position of the record
for the Store. There are currently 5 Secondary suppliers. If a store has 5
secondary suppliers, all five field will have account #.

How do I create another table that is linked to the Store table so that I
can print my report the way I want it. And what should that table look like.
Or do I need to create another table.

Thank you for your help.

 

[Allen Browne]

You have multiple stores, and multiple suppliers. These 3 tables should do
it:

Store table:
StoreID primary key
StoreName
StoreAddress
...

Supplier table:
SupplierID primary key
SupplierName
SupplierAddress
...

StoreSupplier table:
StoreID relates to Store.StoreID
SupplierID relates to Supplier.SupplierID
Account the account number for this supplier at this store.

You can then create a query that uses all 3 tables, and use it as the source
for a report. The report will group by StoreID, and list the relevant
suppliers and account numbers under each store.

Technically, you have a many-to-many relation between stores and suppliers.
The 3rd table is called a junction table, and this is the standard way of
breaking a many-to-many down into a pair of one-to-many relations. For
another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[Guest]

Thanks for the example and I printed off the example you included. I may
have additional question but for now I appreciate the input.