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
> Select Top 1 Subquery Issues, Access 2016    
 
   
rramsey
post Jan 14 2020, 11:49 AM
Post#1



Posts: 12
Joined: 3-September 19



Hello All,
I am using a time punch record to calculate the amount of time my employees spent in the department. The way our company has the record available for download puts each punch (in or out) on a new line for the employee. I've created a query to help filter that data and condense it to the information I need, and in doing so I wanted to use a subquery to put the "Clock in" and corresponding "Clock out" in separate columns, so I could then Calculate the total time for each span of punched in time. This is my SQL statement for the query:

INSERT INTO tblIndvInfo ( ID, FullName, DeptStart, Dept, DeptEnd, DeptTotal, ClockInOrOut, cDeptTime, HomeDept, HomeNum, TransNum, DeptNum )
SELECT qryIndvProcessor.ID, qryIndvProcessor.FullName, qryIndvProcessor.DateTime AS DeptStart, IIf([ClockInOrOut]="Change" And IsNull([DeptShort])=True,[HomeDept],[DeptShort]) AS Dept, (SELECT TOP 1 tbl.[DateTime] FROM [qryIndvProcessor] AS tbl WHERE tbl.[FullName] = [qryIndvProcessor].[FullName] AND tbl.[DateTime] > [qryIndvProcessor].[DateTime] ORDER BY tbl.[DateTime] ASC) AS DeptEnd, DateDiff("n",[DeptStart],[DeptEnd]) AS DeptTotal, qryIndvProcessor.ClockInOrOut, Format([DateTime],"Long Time") AS cDeptTime, qryIndvProcessor.HomeDept, qryIndvProcessor.HomeNum, qryIndvProcessor.TransNum, qryIndvProcessor.DeptNum
FROM qryIndvProcessor
ORDER BY qryIndvProcessor.ID;

The bold section is the subquery i am trying to run. Oddly enough it was working and now it is not. If I "View" the data, it will show for a few seconds but then I get a pop up window saying "At most one record can be returned by this subquery." and all the data then clears out. It will ONLY give the error message (and no data) if I try and "Run" it. I've attached a picture of this to also show what my end result is supposed to be.
Attached File(s)
Attached File  Query_Error.png ( 140.93K )Number of downloads: 10
 
Go to the top of the page
 
BruceM
post Jan 14 2020, 02:11 PM
Post#2


UtterAccess VIP
Posts: 8,014
Joined: 24-May 10
From: Downeast Maine


If you are trying to get the results per department (that is, TOP 1 per department) I think your subquery needs to be a WHERE rather than a value. I only have a little time now, but this link may be helpful.
Go to the top of the page
 
rramsey
post Jan 14 2020, 02:32 PM
Post#3



Posts: 12
Joined: 3-September 19



The result I'm looking for is exactly how it is showing up under that error box in my first picture. The data is being taken form a table that looks like this:
Attached File  query_before.png ( 123.31K )Number of downloads: 4


I want it to take the Clock out row and move it to a new column called "DeptEnd."

It acts as if it is doing this properly, and then throws this error and clears everything out like this:
Attached File  query_after_error.png ( 87.49K )Number of downloads: 2


This is my Statement in my query.

DeptEnd: (SELECT TOP 1 tbl.[DateTime] FROM [qryIndvProcessor] AS tbl WHERE tbl.[FullName] = [qryIndvProcessor].[FullName] AND tbl.[DateTime] > [qryIndvProcessor].[DateTime] ORDER BY tbl.[DateTime] ASC)
Go to the top of the page
 
theDBguy
post Jan 14 2020, 02:40 PM
Post#4


UA Moderator
Posts: 77,336
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just a guess, but does this work?

CODE
DeptEnd: (SELECT Max(tbl.[DateTime]) FROM [qryIndvProcessor] AS tbl WHERE tbl.[FullName] = [qryIndvProcessor].[FullName] AND tbl.[DateTime] < [qryIndvProcessor].[DateTime]

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 14 2020, 02:42 PM
Post#5


UtterAccess VIP
Posts: 2,099
Joined: 4-June 18
From: Somerset, UK


Ignoring the main query for now, does your SQL in the last post give the same result as this much simpler version?
CODE
DeptEnd: SELECT TOP 1 [DateTime] FROM qryIndvProcessor ORDER BY [DateTime]


BTW DateTime is a very poor choice for a field name as its a reserved word in Access

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
rramsey
post Jan 14 2020, 03:02 PM
Post#6



Posts: 12
Joined: 3-September 19



Hi theDBguy,

This worked to an extent... it did give no errors and information, but it is backwards.
Attached File  qry_new_result.png ( 151.58K )Number of downloads: 1


This statement, but the blank DeptEnd at the beginning of a person, and gave me negative results (in the DeptTotal column), instead of putting a blank on their last entry giving me positive results. Any idea how to reverse it? I can use it this way, it will just involve some rewriting in the queries that follow it, so it would be helpful if I could just fix it here.

Thanks!

Hi isladogs,

Unfortunately that was too simple, and gave me only one DeptEnd value for every entry.
Go to the top of the page
 
theDBguy
post Jan 14 2020, 03:14 PM
Post#7


UA Moderator
Posts: 77,336
Joined: 19-June 07
From: SunnySandyEggo


Okay, still guessing here, but try this one then.

CODE
DeptEnd: (SELECT Min(tbl.[DateTime]) FROM [qryIndvProcessor] AS tbl WHERE tbl.[FullName] = [qryIndvProcessor].[FullName] AND tbl.[DateTime] > [qryIndvProcessor].[DateTime]



--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Jan 14 2020, 03:30 PM
Post#8



Posts: 12
Joined: 3-September 19



That worked perfectly! Thank you so much! Good guessing thumbup.gif

Cheers!
Go to the top of the page
 
theDBguy
post Jan 14 2020, 03:42 PM
Post#9


UA Moderator
Posts: 77,336
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 14 2020, 04:23 PM
Post#10


UtterAccess VIP
Posts: 2,099
Joined: 4-June 18
From: Somerset, UK


Hi
Oops
I had missed the '<' symbol in your query SQL in post #4 (time for new glasses!) so my follow up suggestion was clearly incorrect
Anyway, pleased to hear its now working thanks to the DBGuy's suggestion

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th January 2020 - 03:10 PM