Use records from one table to define fields in another

[chris01252]

I am putting together a supplier and product database and have a slight problem.

We have a large number different delivery locations and each of our suppliers deliver to a different combination of locations. I want to record where each supplier delivers.

I have an idea of how how this could be achieved but just not sure how to do it:

Can I have a table named 'delivery locations', then the records from this table define the fields in the another table, for which I can have some tick boxes to indicate where each supplier delivers. Then as old locations close and new ones open, I just update the delivery locations table which will automatically update the fields in the table which holds the specific supplier delivery locations.

If anyone can point me the right direction of how this can be achieved or perhaps a different way of doing it. It would be much appreciated!

Chris

 

[James Dudden]

I would link the locations table to the suppliers table with a one-to-many relationship. When creating your forms, create a locations subform to go on the suppliers form where you can add locations. You should also create a seperate locations form to hold other information about the locations that do not need to be seen on the suppliers screen.

Hope this helps

 

[Pat Hartman]

Chris, you need to remove your spreadsheet thinking cap and put on the one for relational databases. In a relational database, you never add columns to expand a "list" of something, you add rows. You have a 1-to-many or many-to-many relationship between supplier and location. I can't tell from your description. If a location can be delivered to by ONLY a single supplier, then the relationship is 1-m. If many suppliers can deliver to the same location, then the relationship is m-m. I am going to assume it is m-m which means you'll need three tables. The supplier table, the locations, and the suppliers who deliver to a specific location.

tblSuppliers:
SupplierID (primary key)
SupplierName
....

tblLocations:
LocationID (primary key)
LocationName
....

tblSupplierLocation
SupplierID (primary key fld1, foreign key to tblSuppliers)
LocationID (primary key fld2, foreign key to tblLocations)

 

[chris01252]

Thanks Pat, yes it is m-m. Each supplier could possibly deliver to 100+ different locations. I have managed to put together the basic relationship.
My next query on this is, is it possible to have a form with the supplier at the top and ALL the locations with check boxes below, the locations can then be checked and unchecked as things change. Please note we are often acquiring new sites and closing old sites so when the locations list is changed this will need to be reflected in the form.

Can you offer me any help on this, or point me in the right direction, it would be much appreciated.

 

[Rabbie]

Thanks Pat, yes it is m-m. Each supplier could possibly deliver to 100+ different locations. I have managed to put together the basic relationship.
My next query on this is, is it possible to have a form with the supplier at the top and ALL the locations with check boxes below, the locations can then be checked and unchecked as things change. Please note we are often acquiring new sites and closing old sites so when the locations list is changed this will need to be reflected in the form.

Can you offer me any help on this, or point me in the right direction, it would be much appreciated.

I think you need to rethink your design. A form with 100+ check boxes is going to be very cluttered, difficult to use and probably error-prone. I don't think your users are going to love it.

Edit.

An alternative might be to use a listbox to select the locations. These can be allow you to select more than 1 entry which may help.

 

[Pat Hartman]

You would use a subform to manage the m-m relationship. Presence means that a supplier ships to that location. Absence meanse he doesn't. Unlike a spreadsheet where the list is rigid and requires application wide maintenance to change, no changes are required in a relational database when you add suppliers, locations, or supplier/location relationships. They are simply rows in a table.

 

[evanscamman]

I would use the dual listbox or dual subform approach.

One for available locations. The other for locations assigned to that supplier. The use 'Add' and 'Remove' buttons to move locations from one list to the other.