UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Alternative To Partition Row_number, Access 2016    
 
   
duffy132
post Mar 30 2020, 06:13 PM
Post#1



Posts: 10
Joined: 8-October 19



I am capturing date and time login into a table and need a solution to calculate elapsed time between login/logout times of same day without having separate fields for login and logout. If login in morning, then logout at lunch, then login again, and finally logout at end of days poses the issue.

Thought of the SQL partition rownumbering but will not work within Access.

Thank you in advance for any assistance.
Go to the top of the page
 
DanielPineault
post Mar 30 2020, 06:26 PM
Post#2


UtterAccess VIP
Posts: 7,337
Joined: 30-June 11



welcome2UA.gif

I'm confused by your statement "without having separate fields for login and logout"? You need to house both the In and out times, or have a time field and direction field. How else are you capturing these data points? What is your Table structure exactly?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
June7
post Mar 30 2020, 07:21 PM
Post#3



Posts: 1,400
Joined: 25-January 16
From: The Great Land


If login and logout times are in separate records then need to pull value from another record to calculate elapsed time. This gets tricky to determine which records need to be paired.

Following link has example of common situation requiring pulling value from another record. http://allenbrowne.com/subquery-01.html#AnotherRecord

Provide your table structure.

This post has been edited by June7: Mar 30 2020, 07:22 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Mar 30 2020, 10:11 PM
Post#4


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

To expand on June7's request for the table structure, how about attaching a db with just the table, with some data ... multiple people, multiple days. Be sure to compact and zip. There are ways to do this, but we really need what you are working with to help.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Mar 31 2020, 08:24 AM
Post#5



Posts: 10
Joined: 8-October 19



Thank you all. See attachmed db.
Attached File(s)
Attached File  Time_Clock.zip ( 126.7K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Mar 31 2020, 10:54 AM
Post#6


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


Hi again: Your example data were not adequate for testing. Employee 12330 had one segment on one day then another segment on another day. No employee had multiple segments on the same day. No employee had multiple segments on multiple days. So I corrected the records you provided and added more records to make this realistic. See if my corrections/additions represent reality.

I sequenced the time logged by employee and within day. At first, I assumed a maximum of two time segments per day (Rev1), but then generalized that to multiple segments per day using a MOD calculation against the time sequence value to get a segment number (Rev2). I assumed that the data you are analyzing will always have an even number of time logged values - that is, for every in there is and out, and that the paired times will always be on the same day, not overlapping days.

In each db, see qryElapsedHoursByDay. From that query, work backwards to see how this is built up, and if it calculates the hours you see as correct. No doubt you can simplify this with subqueries or combined methods, but I left the solution segmented so you could see the steps more easily.

See which version best meets your requirements, or if this is even what you are trying to accomplish.

This could be done with VBA as well, but I stayed with SQL processes for now.

HTH
Joe
Attached File(s)
Attached File  TimeClock_Rev1.zip ( 119.44K )Number of downloads: 1
Attached File  TimeClock_Rev2.zip ( 133.61K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Mar 31 2020, 01:49 PM
Post#7



Posts: 10
Joined: 8-October 19



RJD thank you so very much. I took quick look and it appears you hit it on the head. Tonight will look closer.

I have referenced this forum many times always learning something, but never truly posted. I am so glad I did
Go to the top of the page
 
RJD
post Mar 31 2020, 02:10 PM
Post#8


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Work with the solutions more, applying them to you actual data, and let us know if this is truly what you need, or if we need to look at this again.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Mar 31 2020, 07:53 PM
Post#9



Posts: 10
Joined: 8-October 19



Joe so far it's perfect!!!! You went beyond and extra mile.

I never would have thought to use DCount that way. Also never used Mod before which is good as now I have something new to learn and play with.


Thanks again so much.

Randy
Go to the top of the page
 
RJD
post Mar 31 2020, 08:06 PM
Post#10


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Randy. Glad that worked out for you and that you were introduced to some new approaches. thumbup.gif

Hope to see you back here again - and soon see you answering questions for our members as well!

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Apr 3 2020, 09:21 PM
Post#11



Posts: 10
Joined: 8-October 19



Hi Joe and All

Joe your solution still is working beautifully. I created a parameter report that shows the logins & outs with elapsed times grouped by day.

I ran into a wall when it seemed logical to also have those absent show on the same report. I cannot conjure up a path to that result. I got as far as creating a basic query to return those absent for a hard coded date but not able to crunch to process all dates in one.

I took your Rev2 solution and added the report and supporting query plus the query(s) to return those absent on 4/4/20. Yes 4/4. I scanned in more test data changing the system date. The list of employees that should be present is contained in qryProductionEmployees not tblEmployees

I have uploaded as Rev3 if your or others have a little time to take a look.

Thank you very much in advance

Attached File(s)
Attached File  TimeClock_Rev3.zip ( 187.19K )Number of downloads: 2
 
Go to the top of the page
 
June7
post Apr 4 2020, 09:27 AM
Post#12



Posts: 1,400
Joined: 25-January 16
From: The Great Land


Need a dataset of all possible combinations of employee and date.
SELECT DISTINCT tblEmployees.EmployeeID, DateValue(TimeLogged) AS DayLog FROM tblEmployees, tblTimeLogged;

Query of employees logged in.
SELECT DISTINCT tblTimeLogged.EmployeeID, DateValue([TimeLogged]) AS DayLog FROM tblTimeLogged;

Join the two to see all employees and who was and wasn't logged in on each date.
SELECT Query1.EmployeeID, Query1.DayLog, Query2.DayLog FROM Query2 RIGHT JOIN Query1 ON (Query2.DayLog = Query1.DayLog) AND (Query2.EmployeeID = Query1.EmployeeID);


Day is a reserved word. Advise not to use reserved words as names for anything.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
June7
post Apr 4 2020, 12:21 PM
Post#13



Posts: 1,400
Joined: 25-January 16
From: The Great Land


Or UNION your queries.

SELECT EmployeeID, DateLogged, TimeSeq FROM [TestEmployeesPresent4-4-20]
UNION SELECT EmployeeID, Null, Null FROM [TestEmployeesAbsent4-4-20];

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Apr 4 2020, 01:33 PM
Post#14


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


Hi again: Take a look at the revised db attached. Run the report from the form that displays on startup. Use the dates indicated. See if this is close to what you are trying to do.

The approach does use a UNION query as June7 suggests, joining work time with absences on all dates in the range without work hours for employees. (Note the new dates table - you can expand that, leave out non-workdays, etc., if desired.) But it uses other approaches as well. Look at the report, then trace back the record source query to where the data come from. Test with a single date or a different range.

See if this is close to what you need.

I had some time (isolated at home due to the virus situation) so just went ahead and wrote some SQL ...

HTH
Joe
Attached File(s)
Attached File  TimeClock_Rev4.zip ( 161.01K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Apr 4 2020, 09:14 PM
Post#15



Posts: 10
Joined: 8-October 19



Thank you both. Just finished working through what you provided and some lights upstairs came on.

Kept thinking about a union but was in the trees and missed the forest of establishing a work day dates calendar.

Thank you June7 for reminder of using the word day. I knew it but was lost in the trees at that moment.

One thing I haven't been exposed to before is a from statement with a comma separating two tables...........From tbl1, tbl2

Thank you again for the leg up and opportunity to learn.


Randy
Go to the top of the page
 
June7
post Apr 4 2020, 09:43 PM
Post#16



Posts: 1,400
Joined: 25-January 16
From: The Great Land


A query with multiple tables and no JOIN clause is referred to a Cartesian query - every record of each table associates with every record of other. So if two tables each have 3 records the resulting query dataset will be 9 records.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Apr 4 2020, 09:53 PM
Post#17


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Randy. June7 and I are happy to help.

Incidentally, the use of two sources (tables and/or queries) in the FROM clause, separated by a comma, is called a Cartesian product - all possible combinations of records in the two. This is necessary, as you see in this case, to make all dates in the range in combination with each person.

Continued success ...

(Ah, I see June7 also discussed Cartesian product queries - a name derived from the mathematician/philosopher cited just below, who is credited with this procedure.)

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duffy132
post Apr 5 2020, 12:34 PM
Post#18



Posts: 10
Joined: 8-October 19



Just an update on the output of your help.

In place of a hard data coded work schedule/production dates, I'm creating dynamically with a distinct statement on the time logged table. DISTINCT Datevalue(tblTimeLogged.TimeLogged) If no one scanned in then it was not a work day.

Coding advice as well as philosophy. Great combo. Believe "I think, therefore I am" is his as well.


Both of you stay safe.
Go to the top of the page
 
RJD
post Apr 5 2020, 06:31 PM
Post#19


UtterAccess VIP
Posts: 10,499
Joined: 25-October 10
From: Gulf South USA


Hi Randy:

QUOTE
In place of a hard data coded work schedule/production dates, I'm creating dynamically with a distinct statement on the time logged table. DISTINCT Datevalue(tblTimeLogged.TimeLogged) If no one scanned in then it was not a work day.

Good approach. As long as you get a table (or query) of all the relevant dates, that's all you need. I assume you are converting that to the day from the TimeLogged.

QUOTE
Believe "I think, therefore I am" is his as well.

Cogito, ergo sum. Well, he is usually credited with that, although it has appeared, in one form or another, as far back as Plato and Aristotle, and from philosophers from other regions of the world as well. But Descartes is a favorite of mine, having majored in Mathematics with an undeclared minor in Philosophy (we only got to declare one minor) as an undergraduate. Love his quotes.

Best of luck with your Access endeavors. Let us know if we can be of further assistance.

Be safe and healthy in this scary world ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 12:52 AM