Help manipulating data

[DanHolley]

Hi all,



I am trying to to set up an access database/program to query a table in a SQL database which will provide a list of users and events of a relay being triggered (one event when triggered on and another event when triggered off), users may trigger the relay several times per day. What I am trying to achieve is to run a monthly report of how long per user the relay has been on for. Example dataset below:-

User ID - First Name - Surname - Event time - Event Type
1 Dan Holley 10/11/2020 09:50 Relay On
1 Dan Holley 10/11/2020 10:15 Relay Off
1 Dan Holley 10/11/2020 10:20 Relay On
1 Dan Holley 10/11/2020 10:45 Relay Off
1 Dan Holley 19/11/2020 15:30 Relay On
1 Dan Holley 19/11/2020 16:01 Relay Off
2 Joe Bloggs 19/11/2020 10:30 Relay On
2 Joe Bloggs 19/11/2020 10:55 Relay Off
2 Joe Bloggs 19/11/2020 13:30 Relay On
2 Joe Bloggs 19/11/2020 14:15 Relay Off

The data comes from an access control system on a social club where the access control system is controlling the table light for 3 snooker tables which has 24/7 access so of course the data above would be when it's sorted and grouped by user id and date/time but the raw data will have on and off's from the three tables all over the place.

So what I need to do is take an on/off pair and subtract one from the other to give minutes of light used add it to the next on/off pair etc etc (or a better way that I've not thought of) per user to give an invoice to table light.

It's been a few years since I have done access programming so I'm a bit rusty!



Any help greatly appreciated.
Dan

[ssanfu]

Welcome to the forum

.... control system is controlling the table light for 3 snooker tables

Q1) Is there 1 light for 3 snooker tables or does each snooker table have its own light?
Q2) If 1 light/1 table, do you record which user is assigned to which table?

Q3) Can User 1 be assigned to 2 tables at the same time? User 2 is assigned to table 1 and User 1 assigned to tables 2 & 3?

[DanHolley]

Hi Steve,

1. There are three tables & each has its own light and access card reader
2. Yes, so when a user inserts their card into the reader adjacent to the table the access system records the event and switches the relay on which turns the light on.
3. The access reader can only accept one user card at any given time, all users can use any of the three tables (whichever is free at the time).

The club runs pretty much autonomously so members are not assigned a table at point of entry, members can visit when they like and play on which ever table is free/preference, of course on busy evenings one member could play on all three tables (at different times obviously).

The way the access reader works is 1. user inserts card into table reader 2. control system reads card number toggles the light relay on and records an event in the database 3. user removes card when finished 4. control system reads card number when removed and toggles light relay off and records an event in the database.


Hope this helps.

Dan

[Micron]

Seems to me that you're needing a subquery that gets the value in another record. They are not my specialty by any means so I will refer you to the guru

http://www.allenbrowne.com/subquery-01.html

I'm thinking that the first date column gets the date time as "Relay On" and the next field gets the date time as "Relay Off" for that user. When they are side by side in a query, you can then subtract On from Off.
EDIT - forgot to mention that your table will require a PK field. You don't seem to have one, so I think you'll have to add an autonumber EventID field.

[orange]

In your sample data how do you -or do you care- which table/reader is involved?
The logic suggests the ON/OFF occurs in pairs and that you could not have 2 consecutive ONs for the same reader. ON must be followed by OFF for a given user.
Can you mock up some data showing different table/reader and ON/OFF for testing?

Aside:
Do not use embedded spaces in your field or object names.

[Micron]

Orange, I hope that's not really necessary because I spent probably a half hour building a table and trying to do it with a self join, then another 30 minutes doing a subquery!
Here's the table

EventID	UserID	FName	SName	EventTime	EventType
1	1	Dan	Holley	10/11/20 9:50:00 AM	Relay On
2	1	Dan	Holley	10/11/20 10:15:00 AM	Relay Off
3	1	Dan	Holley	10/11/20 10:20:00 AM	Relay On
4	1	Dan	Holley	10/11/20 10:45:00 AM	Relay Off
5	1	Dan	Holley	11/19/20 3:30:00 PM	Relay On
6	1	Dan	Holley	11/19/20 4:01:00 PM	Relay Off
7	2	Joe	Bloggs	11/19/20 10:30:00 AM	Relay On
8	2	Joe	Bloggs	11/19/20 10:55:00 AM	Relay Off
9	2	Joe	Bloggs	11/19/20 1:30:00 PM	Relay On
10	2	Joe	Bloggs	11/19/20 2:15:00 PM	Relay Off

Here's the result

EventID	UserID	Relay On	Relay Off
1	1	10/11/20 9:50:00 AM	10/11/20 10:15:00 AM
3	1	10/11/20 10:20:00 AM	10/11/20 10:45:00 AM
5	1	11/19/20 3:30:00 PM	11/19/20 4:01:00 PM
7	2	11/19/20 10:30:00 AM	11/19/20 10:55:00 AM
9	2	11/19/20 1:30:00 PM	11/19/20 2:15:00 PM

Here's the sql

SELECT tblLights.EventID, tblLights.UserID, tblLights.[EventTime] AS [Relay On], 
(SELECT TOP 1 Dupe.EventTime FROM tblLights AS Dupe WHERE Dupe.UserID = tblLights.UserID AND 
Dupe.EventTime > tblLights.EventTime  ORDER BY Dupe.EventTime, Dupe.EventID DESC;) AS [Relay Off]
FROM tblLights
WHERE (((tblLights.EventType)="relay on"));

I just had to try! If that is correct, then I leave it to the OP to do the time span calculation. I see no point in me doing that at present as it may not be what's required. Regardless, I am going to celebrate my success now, even if it's not what is being asked for.
Note - as I mentioned, this sort of thing is impossible without a pk in your table AFAIK.
P.S. This forum site is woefully lacking in emoticon choices!!

EDIT - forgot to say that I took the liberty of manipulating date values as it appears they are not in US format. That might frustrate replicating the example.

[ssanfu]

I too changed the dates to American format. But I "brute forced" it using VBA.

There needs to be more error checking and error handling - and I had to use a temp table (the table stays/ the records as temp) to get the totals.

You shouldn't have the first name/surname in the main table. The User table should be linked to the main table (I didn't do that - its late-ish and I have to run.
But maybe the code will help someone (hopefilly?)

Also, apologies on table names.... not very imaginative..

[orange]

@Micron and Steve,

I had a tough time getting these dates into a table. Imported EventTime as text, then did a Cdate or something??(forgotten the details) to get the dates as dates. My default is to show dates in Canadian format MDY, but I also have MMM for month. I may just have changed EventTime from Text to Date after importing. (Getting older and trying to do 5 things at once)

This is my version of the table with data.

EventID	UserID	FName	SName	EventTime	EventType
1	1	Dan	Holley	10-Nov-20 9:50:00 AM	Relay On
2	1	Dan	Holley	10-Nov-20 10:15:00 AM	Relay Off
3	1	Dan	Holley	10-Nov-20 10:20:00 AM	Relay On
4	1	Dan	Holley	10-Nov-20 10:45:00 AM	Relay Off
5	1	Dan	Holley	19-Nov-20 3:30:00 PM	Relay On
6	1	Dan	Holley	19-Nov-20 4:01:00 PM	Relay Off
7	2	Joe	Bloggs	19-Nov-20 10:30:00 AM	Relay On
8	2	Joe	Bloggs	19-Nov-20 10:55:00 AM	Relay Off
9	2	Joe	Bloggs	19-Nov-20 1:30:00 PM	Relay On
10	2	Joe	Bloggs	19-Nov-20 2:15:00 PM	Relay Off

Micron,

I think your materials are on target.
I used your query, which I call QMicrontblLights.

SELECT tblLights.EventID, tblLights.UserID, tblLights.[EventTime] AS [Relay On], (SELECT TOP 1 Dupe.EventTime FROM tblLights AS Dupe WHERE Dupe.UserID = tblLights.UserID AND Dupe.EventTime > tblLights.EventTime  
ORDER BY Dupe.EventTime, Dupe.EventID DESC;) AS [Relay Off]
FROM tblLights
WHERE (((tblLights.EventType)="relay on"));

Which gives result

EventID	UserID	Relay On	Relay Off
1	1	10-Nov-20 9:50:00 AM	10-Nov-20 10:15:00 AM
3	1	10-Nov-20 10:20:00 AM	10-Nov-20 10:45:00 AM
5	1	19-Nov-20 3:30:00 PM	19-Nov-20 4:01:00 PM
7	2	19-Nov-20 10:30:00 AM	19-Nov-20 10:55:00 AM
9	2	19-Nov-20 1:30:00 PM	19-Nov-20 2:15:00 PM

Then made a final query for the summation using

SELECT QMicrontblLights.UserID
    ,tblLights.FName
    ,tblLights.SName
    ,Sum(DateDiff("n", [Relay On], [Relay Off])) AS TotalTimePlayed_minutes
    ,Choose(Month([Relay On]), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", 
        "Nov", "Dec") AS ReportMonth
FROM QMicrontblLights
INNER JOIN tblLights
    ON (QMicrontblLights.UserID = tblLights.UserID)
        AND (QMicrontblLights.EventID = tblLights.EventID)
GROUP BY QMicrontblLights.UserID
    ,tblLights.FName
    ,tblLights.SName
    ,month([Relay on]);

To give result:

UserID	FName	SName	TotalTimePlayed_minutes	ReportMonth
1	Dan	Holley	81	Nov
2	Joe	Bloggs	70	Nov

But OP may have to deal with table also.

[Micron]

I had a tough time getting these dates into a table

IIRC I used Excel and preceded all values with ' to make them text, then paste appended into a table where the field was already set to date/time. Seemed to work and was easy. At least we've shown that it can be done. The DateDiff function can return the minutes (or some other time portion):

EventID	UserID	Relay On	Relay Off	Duration
1	1	10/11/20 9:50:00 AM	10/11/20 10:15:00 AM	25
3	1	10/11/20 10:20:00 AM	10/11/20 10:45:00 AM	25
5	1	11/19/20 3:30:00 PM	11/19/20 4:01:00 PM	31
7	2	11/19/20 10:30:00 AM	11/19/20 10:55:00 AM	25
9	2	11/19/20 1:30:00 PM	11/19/20 2:15:00 PM	45

EDIT - I suppose it is slightly more difficult if the time span goes over midnight.

[DanHolley]

Wow!

Thanks guys for all your help, this certainly gives me something to move forward on. Certainly wasn't expecting you all to go through all that effort.

Thanks again your help is appreciated, I'll have a play tomorrow.



Cheers
Dan