Clarified Question

[Air-ron]

Okay, I had posted before under the title "Multiple Record Form", but there
were some troubles with the underlying data structure.

Now I have cleared up my confusion about how to attack different parts of
the problem, and am left with only one unresolved issue so far as data entry
goes -
I need to create a form to enter the data!

Here's what I have as for structure:

tblCounts
PK GDay - the gaming day (date)
PK CTime - the time the count data was taken
PK ZoneID - the zone where the data was taken
PK VendorID - the vendor(machine type) we're counting the usage of
Headcount - the customer count for the zone, time, and vendor in question.

tblVendors
PK VendorID - 1toM related both to tblCounts and tblVendorZones
VendorName - text string

tblZones
PK ZoneID - autonumber, 1toM related both to tblCounts and tblVendorZones
ZoneName - Text string
FloorID - FKfrom casinofloors table

tblVendorZones - Junction table for vendors and zones
ZoneID - FK from tblZones
VendorID - FK from tblVendors

tblTimes
PK Ctime - data collection times
Shift - FK from tblShifts

tblCasinoFloors
PK FloorID - autonumber
FloorName - Text String.

thus, each shift counts 4 times over 8 hours, each floor is broken down into
zones, and each zone can have machines from any number of vendors.

The most used and updated data will be in tblCounts. (I have a form already
to adjust the casino layout as far as zones, zone names, and the vendors in
the zones)

What I would like is a form where the shift manager can, at the end of
his/her shift, input the counts taken by the underlings(like me) all at once.


I have a form set up that takes the date, shift, and floor; and runs a
couple of queries to make sure there are records in the table that correspond
to the all possible time, zone and vendor combinations (as defined by the
relationships and junction tables).

What would be ideal at this point would be a form based on a crosstab query,
so the shift managers could then input their data in a spreadsheet-styled
form.
Alas, I found that this was impossible without several more weeks of
seat-of-the-pants coding instruction.

What's my best alternative?
The easier I can make this(these) form(s) to use, the easier my life will
be, as I'm leaving the customer service department very soon, and that
departure may depend on this DB...

I appreciate all the help I can get!!!
Aaron

 

[Air-ron]

Okay, I have a Non-Ideal form set up, but there's a problem:

The query I set up for my dataentry form is based on several tables, and
it's coming up as a nonupdateable recordset. In order to filter the data
down to the specified shift, date, and floor, I have to reference nearly
every table in the database... What can I do?

aaron

 

[Rick Brandt]

Okay, I have a Non-Ideal form set up, but there's a problem:

The query I set up for my dataentry form is based on several tables,
and it's coming up as a nonupdateable recordset. In order to filter
the data down to the specified shift, date, and floor, I have to
reference nearly every table in the database... What can I do?

aaron

Use one form to filter and find the desired record, then provide a button to
open a second form with subforms for doing the edits.

 

[Air-ron]

Use one form to filter and find the desired record, then provide a button to
open a second form with subforms for doing the edits.

I apologize for sounding like an idiot, but I thought that that was exactly
what I was doing. - the original form, frmDataEntryPicker, is unbound. It
has an activeX control to pick the date, and two combo boxes to pick the
floor and shift. The subsequent queries reference those values to filter the
records. The data entry form, frmDataEntry, is bound to the query
qryZonesbyFloor. It has the Floor name, the ZoneName, the date (unbound,
referencing the other form), and the subform, sfrmDataEntry.
The subform, sfrmDataEntry, is bound to the query qryCountsbyVendorTime,
which gets its data from tblCounts. It does reference tblCasinoFloors and
tblShifts for filtering purposes.

I'm still not sure how to fix the problem
Thank you for any help you can give!
Aaron

 

[Rick Brandt]

I apologize for sounding like an idiot, but I thought that that was exactly
what I was doing. - the original form, frmDataEntryPicker, is unbound. It
has an activeX control to pick the date, and two combo boxes to pick the
floor and shift. The subsequent queries reference those values to filter the
records. The data entry form, frmDataEntry, is bound to the query
qryZonesbyFloor. It has the Floor name, the ZoneName, the date (unbound,
referencing the other form), and the subform, sfrmDataEntry.
The subform, sfrmDataEntry, is bound to the query qryCountsbyVendorTime,
which gets its data from tblCounts. It does reference tblCasinoFloors and
tblShifts for filtering purposes.

I'm still not sure how to fix the problem
Thank you for any help you can give!
Aaron

If the second form is not editable, then it is likely because the queries it
uses are not editable. Have you opened them separately to see if you can make
changes there?

Forms for data entry can be based on queries, but typically only queries that
limit records, apply sorting, or add a few calculated fields. If the query
contains more than one base table though then there is a good chance for these
kinds of problems.

 

[Air-ron]

If the second form is not editable, then it is likely because the queries it
uses are not editable. Have you opened them separately to see if you can make
changes there?

Forms for data entry can be based on queries, but typically only queries that
limit records, apply sorting, or add a few calculated fields. If the query
contains more than one base table though then there is a good chance for these
kinds of problems.

Unfortunately, because of the MtoM relationship between Vendors and Zones, I
have to reference the junction table to sort out the records I want to be
able to edit. Do you see a way to work around this problem?

Aaron

 

[Rick Brandt]

Unfortunately, because of the MtoM relationship between Vendors and Zones, I
have to reference the junction table to sort out the records I want to be
able to edit. Do you see a way to work around this problem?

I am not as well-versed in this topic as others, but (usually) a non-editable
query with just a couple of tables can be modified so that it is editable.
Perhaps if you post the SQL someone can point out some changes that would do
that.

 

[Air-ron]

I am not as well-versed in this topic as others, but (usually) a
non-editable

query with just a couple of tables can be modified so that it is editable.
Perhaps if you post the SQL someone can point out some changes that would do
that.

Okay, here is a copy of the sql statement for this query:

SELECT tblCounts.GDay, tblCounts.ZoneID, tblCounts.VendorID,
tblCounts.CTime, tblCounts.HeadCount
FROM (tblCasinoFloors INNER JOIN tblZones ON tblCasinoFloors.FloorID =
tblZones.FloorID) INNER JOIN ((tblVendors INNER JOIN ((tblShifts INNER JOIN
tblTimes ON tblShifts.Shift = tblTimes.Shift) INNER JOIN tblCounts ON
tblTimes.CTime = tblCounts.CTime) ON tblVendors.VendorID =
tblCounts.VendorID) INNER JOIN tblVendorZones ON tblVendors.VendorID =
tblVendorZones.VendorID) ON (tblZones.ZoneID = tblVendorZones.ZoneID) AND
(tblZones.ZoneID = tblCounts.ZoneID)
WHERE (((tblCounts.GDay)=GetDate()) AND ((tblTimes.Shift)=GetShift()) AND
((tblZones.FloorID)=GetFloorID()))
ORDER BY tblCounts.VendorID, tblCounts.CTime;

I'm pretty sure the problem lies with the fact I have to refer to the
junction table to see which vendors are available within the different zones,
as well as check to see which zones are within the specified floor, and which
Hours corresponded to the specified shift.

Is it possible that I've over-normalized to the point that I can't create
updateable queries?

Aaron

 

[Air-ron]

I have changed the underlying query's recordset type property to
dynaset(inconsistent updates), and now I can edit the data from within the
query's datasheet view, but not from the form... Do you think I'm on the
right track?

Aaron

 

[Rick Brandt]

I have changed the underlying query's recordset type property to
dynaset(inconsistent updates), and now I can edit the data from
within the query's datasheet view, but not from the form... Do you
think I'm on the right track?

Aaron

There is a similar sertting on the form as well. I'm not sure if there are any
issues associated with going that way though.