Multiple Record Form

[Guest]

I'm trying to put together a front-end form for data entry. I have a
database that tracks customer count data in a casino, broken down by Time (2
hour intervals), Zone (several zones across 3 casino floors), and game type
(or vendor). The table containing the counts has fields for all of the above
items, as well as the customer count.

I would like to be able to select the date, casino floor, and shift at the
top, and then (possibly in a subform) an area with 4 columns (representing
each count time in a shift) and several rows, with each vendor grouped by
zone.

The problem is, the records in the table are set up so each individual count
is saved as a record, with fields for the time, date, zone, and vendor.

This example would be for the first shift, Expansion Casino

Date ______ Floor ______ Shift ______

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - -
- - - - -
ZONE 1
Vendor 8:00 AM 10:00 AM 12:00 PM 2:00 PM
VGT ________ ________ _______ _______
SDG ________ _________ _______ _______
ZONE 2
Vendor 8:00 AM 10:00 AM 12:00 PM 2:00 PM
SDG ________ ________ ________ _______
SODAK _________ ________ ________ _______
ZONE 3
Vendor 8:00 AM 10:00 AM 12:00 PM 2:00 PM
VGT ________ ________ _______ _______
SDG ________ _________ _______ _______
ZONE 4
Vendor 8:00 AM 10:00 AM 12:00 PM 2:00 PM
SDG ________ ________ _______ _______
Rocket ________ _________ _______ ________
CJack _________ ________ _______ ________

How can I set this up?
Aaron

 

[Jeff Boyce]

It sounds a bit like you've worked out the data entry form, but your
underlying data structure is not clear. In a relational database like
Access, you really need to start from the data elements and relationships.

What's your data (table) structure?

 

[Guest]

The Table containing the count data will have 5 fields, four of which
combine to be a primary key.

GDay, CTime, ZoneID, Vendor - (primary key combination)

And HeadCount will be the actual data.

How would I set up the form I described before, using this data structure?
The form would have to be able to create multiple records, (if they don't
exist), and store or edit them for each zone, vendor, and time within a
shift/Day/Floor combination.

Aaron

 

[Jeff Boyce]

It sounds like the combination is a "junction" or "resolver" table, to
handle M:M relationships.

One approach for a form would be to use combo boxes for each of the fields
representing an underlying (many) table. For example, if you wanted to
select a Zone (from a combo box), and a Vendor (from a combo box), and ...,
your form would be based on the underlying junction table, and the fields on
that table's fields. But the controls on the form would be combo boxes,
with row sources based on the related tables.