Starting from Scratch...Again.

Hi Aaron,

First, are you absolutely certain sure that a straight M:M relationship
between Zones and Vendors is what you need? I'd have thought that what
you have in each Zone is one or more types of machine (i.e. a M:M
relationship between Zones and MachineTypes), while each Vendor supplies
one or more types of machine (i.e. a 1:M relationship between Vendors
and MachineTypes).

In other words there's no direct relationship between vendors and zones,
but an indirect one via the type of machine installed in each zone. (In
that case it's still very easy to generate queries for questions like
"Which vendors are represented in each zone?"

Either way, though, your question about the user interface for the M:M
relationship is valid (whether it's Zones and MachineTypes or Zones and
Vendors). I guess you're envisaging a form with a grid that has zones
down the left side and vendors or machines across the top, or perhaps
the other way round. This is quite easy to generate using a query (often
but not always a crosstab), but queries like this are necessarily
read-only so it's not suitable for data entry.

The simplest way to enter or edit data in a M:M relationship in Access
is usually to use
1) a form bound to one table (Zones)
2) on this form, a continuous subform bound to (a query on) the junction
table
3) on the subform, a combobox bound to MachineTypeID or VendorID. The
combo's RowSource should be a query that returns
MachineTypeID, MachineType
or
VendorID, VendorName
in alphabetical order. (Because the M:M relationship is symmetrical, you
could also bind the main form to MachineTypes (or Vendors) and the
subform to Zones.

There are various techniques for creating an editable grid, but IMO
they're more trouble than they're worth except when specific data entry
requirements demand it.


On Thu, 30 Dec 2004 07:27:02 -0800, "Air-ron"
wrote:

I am relating my Zones to my Vendors in a M2M relationship, so I have a
junction table. However, if either the zones or vendors change, that table
will have to be changed, correct? I am now envisioning a form that shows the
vendors, zones, and then a crosstab-type data sheet to relate them. Do I
need to set that up as a table, or would a query work?

Aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Actually, we use the Vendors and machine types interchangably. While the
Vendors do in fact provide us with different machine types, we don't
discriminate between the different types when counting customers, as it's
done by hand. (counting the number of people on each of 4 vendors is
difficult, if it were broken down to 20+machine types as well, our customer
service people would never be done recording the data)

While the changes in the casino layout don't happen often, they do happen,
and it would be nice to be able to adapt - ie new vendors, change the layout
of the zones, expand the casino...

I tried something, and maybe you can tell me if it'll work. I used an
append query to fill a table that has all of the current vendors and all of
the current zones. there is also a yes/no field, that I used to insert the
current layout. Will this still allow my relationships, or will that destroy
them because the table contains a row with each vendor/zone combination.
(also I fear that this would be hard to update once I've moved out of this
dept - one of my main design goals here is that it's easy to use and flexible
enough that once I'm out of the dept, people can still use it)

I'll work on implementing your suggestions
aaron

If I understand you right, you've got a table like this (it would make
things easier if you provided this informatoin rather than expecting
people to try and work it out for themselves:

tblZonesVendors
ZoneID* - FK into tblZones
VendorID* - FK into tblVendors
VendorHasMachinesInThisZone - Yes/No

This is a junction table implementing a M:M relationship between Zones
and Vendors. The Yes/No field is redundant: the presence of a record in
this table means that that vendor has a machine in that zone, and the
absence of a record means that the vendor doesn't have a machine in that
zone.

On Thu, 30 Dec 2004 10:45:08 -0800, "Air-ron"
wrote:


Actually, we use the Vendors and machine types interchangably. While the
Vendors do in fact provide us with different machine types, we don't
discriminate between the different types when counting customers, as it's
done by hand. (counting the number of people on each of 4 vendors is
difficult, if it were broken down to 20+machine types as well, our customer
service people would never be done recording the data)

While the changes in the casino layout don't happen often, they do happen,
and it would be nice to be able to adapt - ie new vendors, change the layout
of the zones, expand the casino...

I tried something, and maybe you can tell me if it'll work. I used an
append query to fill a table that has all of the current vendors and all of
the current zones. there is also a yes/no field, that I used to insert the
current layout. Will this still allow my relationships, or will that destroy
them because the table contains a row with each vendor/zone combination.
(also I fear that this would be hard to update once I've moved out of this
dept - one of my main design goals here is that it's easy to use and flexible
enough that once I'm out of the dept, people can still use it)

I'll work on implementing your suggestions
aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thank you for your help

"John Nurick" wrote:

If I understand you right, you've got a table like this (it would make
things easier if you provided this informatoin rather than expecting
people to try and work it out for themselves:

tblZonesVendors
ZoneID* - FK into tblZones
VendorID* - FK into tblVendors
VendorHasMachinesInThisZone - Yes/No

This is a junction table implementing a M:M relationship between Zones
and Vendors. The Yes/No field is redundant: the presence of a record in
this table means that that vendor has a machine in that zone, and the
absence of a record means that the vendor doesn't have a machine in that
zone.

On Thu, 30 Dec 2004 10:45:08 -0800, "Air-ron"
wrote:


Actually, we use the Vendors and machine types interchangably. While the
Vendors do in fact provide us with different machine types, we don't
discriminate between the different types when counting customers, as it's
done by hand. (counting the number of people on each of 4 vendors is
difficult, if it were broken down to 20+machine types as well, our customer
service people would never be done recording the data)

While the changes in the casino layout don't happen often, they do happen,
and it would be nice to be able to adapt - ie new vendors, change the layout
of the zones, expand the casino...

I tried something, and maybe you can tell me if it'll work. I used an
append query to fill a table that has all of the current vendors and all of
the current zones. there is also a yes/no field, that I used to insert the
current layout. Will this still allow my relationships, or will that destroy
them because the table contains a row with each vendor/zone combination.
(also I fear that this would be hard to update once I've moved out of this
dept - one of my main design goals here is that it's easy to use and flexible
enough that once I'm out of the dept, people can still use it)

I'll work on implementing your suggestions
aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.