Whats the best approach too?????? (1 Viewer)

Good Looking Bloke · Oct 15, 2019

So I have a time in for visitors.

The visitors stay for an average of 45 minutes.

So if they arrive at 8.00 they leave on average at 8.45.

I am trying to count something like:

Betty arrives at 8.00 (assumed to leave at 8.45)
Wilma arrives at 8.15 (assumed to leave at 9.00)
Linda arrives at 8.30 (assumed to leave at 9.15)

Therefore the answer I am wanting to calculate is:

Visitors on site at:
8.00 1
8.15 2
8.30 3
8.45 2
9.00 1
etc

I am broadly thinking I would start with the time in and calculate the time out using a dateadd for 45 minuets. But then how to count them from there? Sorry brain stopped working about morning tea time today.

Thanks

CJ_London · Oct 15, 2019

to determine a count (of visitors in this case) for a given time or period you need to find those where the persons start time <= the period end time AND the persons end time >= that the period start time.

In your requirement, the period start and end times are the same.

And because you want a running count for multiple times, you will need a separate table to store those times to use in a cartesian (no joins) query

e.g.
tblTimes
pTime
8am
8:15am
8:30am
etc

your query will be something like

Code:

SELECT pTime, count(vID) as present
FROM tblTimes T, tblVisitors V
WHERE V.arrived<=T.pTime AND dateadd("n",45,V.arrived)>=pTime
GROUP BY pTime

Good Looking Bloke · Oct 15, 2019

Thanks...but back up a bit.

So Using my examples...

I would have a table with
Betty 8.00am
Wilma 8.15am
Linda 8.30am

Call the above tblTimes

Then I duplicate these in a second table? tblVisitors or am I misunderstanding this?

CJ_London · Oct 15, 2019

no you need a separate table for the times - I called it tblTimes. Looks like you already have a table of that name holding visitors - which I called tblVisitors (Betty, Wilma, etc)

For the future - always better to provide your actual table and field names to avoid confusion - otherwise we have to make them up to illustrate the solution.

Be aware that date/time fields store both date and time as a number. The time here is 9:36am and the value stored for now is 43753.4002893519
The date is represented as 43753, the number of days since 31/12/1899 and the time is the number of seconds to 9:36am from midnight divided by 86400, the number of seconds in a day.

So be very sure that your time has a zero date value, otherwise you will get the wrong results

Good Looking Bloke · Oct 15, 2019

Oh. I am working from a CSV file I was reluctant to normalise it as it adds a potential for error.

CJ_London · Oct 15, 2019

totally confused now - what has normalisation got to do with it? what does 'working from a csv file' mean? You've linked to it? imported it? have a query on it? something else?

isladogs · Oct 15, 2019

PMFBI but I'm also totally confused.
Just recording the time in and assuming visitors stay for 45 minutes is going to lead to errors when someone leaves before or after that time duration.
A better way would be to have a time field and another Boolean(?) field for in/out. So each visitor has two records for arrival and departure. If the second record does NOT exist, the visitor is still on site.
Another approach uses a TimeIn field and another TimeOut field. This means one record per visitor. Whilst not fully normalised, it may suffice for your purposes.

Gasman · Oct 15, 2019

isladogs said:
P
A better way would be to have a time field and another Boolean(?) field for in/out. So each visitor has two records for arrival and departure. If the second record does exist, the visitor is still on site.

I think that should be 'If the second record does not exist' Colin ?

isladogs · Oct 15, 2019

Oops. Now corrected! Thanks Gasman

Good Looking Bloke · Oct 15, 2019

This is the data I am working with. Yes its linked

Visit Date Visit Time Member ID
2/01/2019 7:56:03 AM 3147476
2/01/2019 7:56:17 AM 3148052
2/01/2019 7:56:18 AM 3148052
2/01/2019 7:57:55 AM 3148235
2/01/2019 7:59:41 AM 3147545
2/01/2019 8:00:14 AM 3147008
2/01/2019 8:01:01 AM 3147416
2/01/2019 8:06:37 AM 3147117
2/01/2019 8:08:35 AM 3148218
2/01/2019 8:09:22 AM 3147533

Its all in separate columns. This is the only data I have. Hope that's clearer as mud.

CJ_London · Oct 15, 2019

and what do you call the linked table?

Good Looking Bloke · Oct 15, 2019

Data for now. Wasn't too worried

CJ_London · Oct 15, 2019

So I called my table tblVisitors, you have called it Data

You need to create a table which I called tblTimes with a field called pTime which you need to populate with the times you want to get a count on

then use the query I outlined using tblTimes and Data, change field names to suit.

Good Looking Bloke · Oct 28, 2019

Coming back to look at this again as I got totally lost in the explanation. Sorry if I am a bit slow here, I find this confusing.

So backing up...a lot.

I have the member ID and a time in.

I have a supposed visit time of 45 minutes.

Time out is not available.

So first off I add the 45 minutes to the Time in. That's the easy bit. I call the calculated outcome Out for lack of better words.

So now I can have
MemberID In Out

Great...Simple enough. Now I need to push this into a cross tab divided into 15 minute slices to get a count of members on site in each of 15 minute slices.

This is where I am getting confused I think. How can I count the visitor if I know the start and end times and then get into teh cross tab.

My data would look like:

Member ID In Out
1234 7:00 7:45
2345 7:10 7:55
3456 7:15 8:00
4567 8:05 8:50

Therefore in 15 minute slices the count of members in this small example is:

7:00-7:15 3
7:15-7:30 3
7:30-7:45 3
7:45 -8:00 2

Sorry if this is obvious to others I am simply not getting it...obviously a hole in my head where it falls out

CJ_London · Oct 28, 2019

compare what you wanted in your first post

Visitors on site at:
8.00 1
8.15 2
8.30 3
8.45 2
9.00 1

with your last post

7:00-7:15 3
7:15-7:30 3
7:30-7:45 3
7:45 -8:00 2

unless you can be clear about what it is you want, I don't see how we can help you.

Good Looking Bloke · Oct 28, 2019

CJ_London said:
compare what you wanted in your first post

with your last post

unless you can be clear about what it is you want, I don't see how we can help you.

I'm sorry to me this is the same? Perhaps I am miscommunicating. The only difference is I have added the first time its still 15 minute increments. I only elaborated further to make it clearer I thought.

isladogs · Oct 28, 2019

@GLB
Please have another look at my comments in post #7 which will I believe help significantly

jdraw · Oct 28, 2019

Just noticed this thread and admit to being a little confused with the requirement.
If you have Visitors entering and on average they leave(are processed) in 45 minutes, then it seems you may have the concept of a queue. You haven't stated clearly what happens in those nominal 45 minutes, and that may be the key issue causing confusion.

For example if you are admitting people to an office to be served by 1 technician, then it's possible for the visitor waiting time to vary depending on the time for the technician to resolve the problem.

Here' a link to some queue based application info by PhilS/codekabinett

As always clarity is critical to getting focused responses.

I agree that approach in post#7 should identify the visitors on site.
ONSite Visitors = Number on site at start + Number of new visitors - Number of Visitors who have left

CJ_London · Oct 28, 2019

I'm sorry to me this is the same?

your original requirement was for a specific point in time. The second is a specific period of time.

with periods, the usual way is to express for example

7:00-7:14
7:15-7:29

or
>=7:00 and <7:15
>=7:15 and <7:30

etc

you have 7:15 twice - so by your example
someone who arrives at 7:15 is counted when they arrive - for 7:00-7:15, and 7:15-7:30 and again when they leave at 8:00 they are counted in 7:45-8:00 and 8:00-8:15.

That may be what you want - to count someone who leaves at 8:00 as being there at 8:15, if so, be clear that is what you require

Good Looking Bloke · Oct 29, 2019

isladogs said:
PMFBI but I'm also totally confused.
Just recording the time in and assuming visitors stay for 45 minutes is going to lead to errors when someone leaves before or after that time duration.
A better way would be to have a time field and another Boolean(?) field for in/out. So each visitor has two records for arrival and departure. If the second record does NOT exist, the visitor is still on site.
Another approach uses a TimeIn field and another TimeOut field. This means one record per visitor. Whilst not fully normalised, it may suffice for your purposes.

This data is auto collected and it doesn't collect time out...

Whats the best approach too?????? (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

MVP / VIP

Enthusiastic Amateur

MVP / VIP

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

MVP / VIP

Super Moderator

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread