Junction Table

[Air-ron]

I have two tables, one for Zones, and one For Vendors.

Each one has a primary key that is an AutoNumber.
Because different Zones can have machines from any number of Vendors, I
would like to define the relationship between the vendors and zones with a
junction table.

what would be the best way to do this? I started with a table with only two
fields, ZoneID and VendorId, and populated the table by hand, but this would
make it difficult to account for changes in the layout of the casino,
wouldn't it? I would also like to be able to add/remove vendors or even
zones as the casino grows and changes.
How could I make all of this possible?
aaron

 

[Steve Schapel]

Air-ron,

To be honest, I am wondering whether you are going down the right track
here. I don't know, without more information about your overall
project, but this is how it looks to me at the moment. You have
Machines. Presumably the machines are already listed in a table? And
presumably the machines are already identified by the Vendor? And each
machine is installed in a particular Zone? So if you put the ZoneID in
the Machines table, you will then already have all the information you
need in order to use a query whenever you need to find which vendors
have machines in which zones. Maybe I have missed the point, and
apologies if so.

 

[Martin J]

Here is what I think you want. I'd have 3 table as follows

Vendor-vendor id,all info for that vendor needed
Zones-zone id,all info about that zone
Machines-machine id not auto#,zone id,vendor id, other info regarding machine.

Then as vendors and or zones changes you add them in. If you want to keep a
history you would add a new machine with the same machine id and new zone or
vendor. This way you can keep track of how the machine is doing. PK's would
be id fields. I hope this is a start.

HTH
Martin J

 

[Air-ron]

Sorry guys - I didn't give you the whole story there. Actually, I'm getting
help for this on the post "Starting from Scratch - Again" from a couple of
days ago, and probably shouldn't have added another thread.

This DB is to track customer counts, so we're not really concerned with
individual machines here - just the vendor types to track popularity.

However, if you guys do get the chance to check that thread out, I'd
appreciate any and all tips/feedback I can get!!!
aaron

 

[John Vinson]

I have two tables, one for Zones, and one For Vendors.

Each one has a primary key that is an AutoNumber.
Because different Zones can have machines from any number of Vendors, I
would like to define the relationship between the vendors and zones with a
junction table.

what would be the best way to do this? I started with a table with only two
fields, ZoneID and VendorId, and populated the table by hand, but this would
make it difficult to account for changes in the layout of the casino,
wouldn't it? I would also like to be able to add/remove vendors or even
zones as the casino grows and changes.
How could I make all of this possible?
aaron

Use a Form based on Zones, and another Form based on Vendors, to add
and remove these.

On one (or both) of these forms, put a Subform based on your junction
table. For instance, you could have a Subform on the Zones table; use
the ZoneID as the master/child link field and the zone will be
automatically filled in as you add new records, and you'll see only
the vendors for that zone. The VendorID field on the subform should be
bound to a Combo Box showing the vendor name (with the vendor ID as
the bound column).

See the Northwind sample database Order form for an example of how
this should be set up.

If you're entering data in table datasheets... DON'T. Forms are your
friends; they provide the tools to let you do this chore with help
from the computer, rather than having to do it all the hard way!

John W. Vinson[MVP]

 

[Air-ron]

Thanks - the only data I'm entering that way is so I can test what I've done
so far. I want to make sure I get the tables and relationships right before
setting up forms

Aaron