displaying the total of multiple entries (1 Viewer)

HimAgain

Registered User.
Local time
Today, 13:04
Joined
Sep 15, 2015
Messages
19
Hey everyone. I need some help with displaying totals in the header on a continuous form.

I am working on a project to keep track of how many people enter and exit the building each hour every day at 4 different entrances.

Table has the following columns
ID, c_date, hours, entrance1_in, entrance1_out, entrance2_in, entrance2_out, entrance3_in, entrance3_out, entrance4_in, entrance4_out

What i am trying to do is display the total amount of people that entered and exited the building per shift (between certain hours between 7am and 2pm for example or between 3pm and 10pm) in unbound textboxes in the header of a continuous form.

any help would be greatly appreciated.
 

plog

Banishment Pending
Local time
Today, 13:04
Joined
May 11, 2011
Messages
11,611
Code:
ID, c_date, hours, entrance1_in, entrance1_out, entrance2_in, entrance2_out, entrance3_in, entrance3_out, entrance4_in, entrance4_out

Stop right there. You've got a bigger problem than this issue. Actually this issue is a symptom of your major problem--a poorly structured table.

You have violated a rule in 2 different manners: When you prefix or suffix field names with states or numbers, its a sign of a poor table structure.

1. In/Out should be stored as values in your table, not as values in a field name. You would have a new field called 'EntranceDirection' and the value in there would either be In or Out. That would tell you what you needed.

2. Numbers in field names to designate differences. Again, these numbers should be values in your table, not in field names. You would have a new field called EntranceNumber where it will go.

So, that means the structure of your table should be like this:

tblDoorUsage
DoorUsageID, autonumber primary key, same as now
EntranceNumber, number, will hold the entrance number now stored in field anme
EntranceDirection, text, In/Out value
EntranceCount*, number, holds how many people used the entrance in the direction

I can't tell if this data is aggregate (showing totals) or individual (showing just one entrance/exit). So EntranceCount may not be needed and it will also dictate where c_Date and hours go. Can you provide some sample data of whats in there now?
 

Cronk

Registered User.
Local time
Tomorrow, 05:04
Joined
Jul 4, 2013
Messages
2,770
Your data is not normalized and therefore it's more difficult to do calculation queries. The table structure should be


ID (autonumber)

C_Date (Date/Time)
Hours (Numeric)

Entrance (Numeric)
PeopleCount (Numeric)

In (True/False)


NB Entrance contains 1,2 3 or 4 and In is true for incoming, false for outgoing


It's then easy to use cross tab queries to generate totals and sub totals.
 

HimAgain

Registered User.
Local time
Today, 13:04
Joined
Sep 15, 2015
Messages
19
hmmm ok i'll work on it some more. I guess i am trying to make it look to much like an excell spreadsheet.
below is what my table currently looks like. don't laugh.. to hard..

column headers
ID cur_date hour entrance1_in entrance1_out entrance2_in entrance2_out entrance3_in entrance3_out entrance4_in entrance4_out total_hour_in total_hour_out
31 8/12/2018 5:00 AM 19 4 3 1 2 6 10 21 34 32
32 8/12/2018 6:00 AM 1 1 0 0 0 0 0 3 1 4
33 8/12/2018 7:00 AM 1 3 0 0 0 0 0 0 1 3
34 8/12/2018 8:00 AM 55 22 32 15 8 45 77 52 172 134
35 8/12/2018 9:00 AM 12 32 45 65 55 82 11 32 123 211
36 8/12/2018 10:00 AM 32 12 5 65 8 15 55 54 100 146
37 8/12/2018 11:00 AM 15 88 95 19 26 54 15 22 151 183
38 8/12/2018 12:00 PM 0 0 0 0 0 0 0 0 0 0
39 8/12/2018 1:00 PM 0 0 0 0 0 0 0 0 0 0
40 8/12/2018 2:00 PM 0 0 0 0 0 0 0 0 0 0
41 8/12/2018 3:00 PM 0 0 0 0 0 0 0 0 0 0
42 8/12/2018 4:00 PM 0 0 0 0 0 0 0 0 0 0
43 8/12/2018 5:00 PM 0 0 0 0 0 0 0 0 0 0
 

plog

Banishment Pending
Local time
Today, 13:04
Joined
May 11, 2011
Messages
11,611
This is what your table should look like:

tbl_EntranceActions
ea_ID, autonumber, primary key, same as you have now
ea_Time, Date/Time field, this replaces cur_date and hour, both of those pieces of data go into this 1 field now
ea_EntranceNumber, number, entrance number, in field name now
ea_Direction, text, will hold the values In or Out
ea_Total, number, will hold value of each entrance_* field you currently have

That's it, just 5 fields for your data. It also means that if all 4 of your entrances have someone going in and out in an hour then you will now have 8 records in your database (1 each per entrance and direction) instead of 1 record as you have now.
 

HimAgain

Registered User.
Local time
Today, 13:04
Joined
Sep 15, 2015
Messages
19
I did figure out a work something that works

Code:
DSum("[hour_in]", "pass_count_log", "[cur_date] = date() AND [hour] between #7:00:00 AM# And #3:00:00 PM#")

But i'll still correct my table info instead of using this.
 

Users who are viewing this thread

Top Bottom