Nested Loops and Adding Records

[Guest]

I was going to try and muddle through this on my own, but I'm under a time
crunch now, so I'd appreciate any help I can get.

I have a DB that I'm setting up to track customer count data in a casino.
The casino is broken down into Floors and each floor inot zones, Each zone
has machines by different vendors. The counts are done in 2 hour intervals
(on even numbered hours) by people on 3 different shifts. Thus, I have
tables for Floors, Zones, Shifts, Times, and vendors. Shifts is related to
times 1toM; Floors is related to zones 1toM, and vendors is related to zones
MtoM, using a junction table VendorZones. The table Counts has fields for
Date, CountTime, Vendor, and Zone (combination primary key) and HeadCount,
(the important data). Zone, Vendor, and CountTime are all related to their
respective tables as well.

I'm pretty sure I've got it set up properly (with lots of help from the
design forum)
Now I need to set up a data entry form.
I have a form set up right now where a shift manager can select the date
(using date picker), the Floor (a combo box), and the Shift (a combo box)

I'm hoping to put in a button that takes those 3 values, and checks the
counts table to see if records exist for those combinations.

In other words :
Given SHIFT, FLOOR, and DATE;
For all times within SHIFT:
For all zones within FLOOR:
For all Vendors within Zone:
If DCount("*", "tblCounts", "[GDay]=#" & _
GetDate() & "# And [CTime]=#" & ctime _
& "# And [ZoneID]=" & zone & " And [VendorID]" _
= " & vend") < 1
Then
Add a new record to tblCounts using DATE, Floor,
Time, Vendor,
and set HeadCount to 0.
EndIF
EndLoop
EndLoop
EndLoop

I have a query set up that shows (except for the date) what records would
need to be checked - including the junction table tie-ins.
I think I've got the program outlined properly, but that's a lot of code for
me to figure out in the next couple of days - Does this at least make sense
to you guys?
aaron

 

[Guest]

It occurred to me that there might be an easier way:

Because I have this query that references all of the appropriate tables,
would it be easier for me to use VBA to open it as a recordset (something I'm
not too clear on), and go down through the recordset one by one (using only 1
loop) using the 3 values, (and the date which returned by a function) to
check for corresponding data in the count table?

So in other words,

For (each record in qryVendorsTimesZones)
IF DCount("*", "Counts", "[Gday]=#" & GetDate() & "# And [CTime]=#" _
& Queries!QueryVendorsTimesZones.[CTime] &"# And [ZoneID]=" & _
Queries!QueryVendorsTimesZones.[ZoneID] & " And [VendorID]=" &_
Queries!QueryVendorsTimesZones.[VendorID]) <1
THEN Make New Record (how do I do this?)
End loop

I'm thinking this is the way to go, but I'm still not sure how to do certain
things ie use the query from VBA, make new records....
What do you guys think? Is this easier? Can you help?
Aaron

 

[Kevin K. Sullivan]

Why do you need to add records with zero HeadCounts? They won't affect your
totals. If you must:

You already have a PK across Date, CountTime, Vendor, and Zone. Therefore,
create a query that is a Cartesian join of the above (includes all the
tables but does not join them). Change it to an append query with
HeadCounts as the target. Add Date, CountTime, Vendor, and Zone from their
respective tables. Add a field Exp1: 0 and select "HeadCounts" in the
AppendTo row.

When you run this query, it will warn you it's going to change data, then
tell you it will add (Dates * CountTime * Vendor * Zone) records. When you
click yes, it will tell you that X records failed to append due to key
violations. These are the collisions with existing data. X is the number
of records you had in HeadCounts to begin with. Click yes to continue to
run the query. The result is a table that retains your data and has 0 in
all the previously missing rows.

I still don't understand why you need the zeroes, but this is a quick way to
get them into the table.

HTH,

Kevin