SQL Query problem

I have the following script that collects people clock in/out hours from a table in access.

Now when I run the query it collects the clock in/out times no problem but if a user is clock in twice in 1 day say working a split shift it only collects the first clock in/out time and ignores the other clock in.out for that day.

Example:

User A

First Shift:
Clock In 10:00  Clock Out 14:00
Second Shift
Clock In 17:00 Clock Out 22:00

It will only return to me the first shift and not both shifts. I know it is to do with the SELECT TOP 1 eventTime FROM qCashierLog but don't know how to return all clock in/out times

How can I get it to return all the clock in/out times instead of the first clock in/out time?
SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], (SELECT TOP 1EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], 1440+DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code WHERE EventType = 1 AND EventDate >= #04/01/2009# AND EventDate <= #04/30/2009# ORDER BY EventDate

Open in new window

richard_garAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Have you tried removing the TOP 1?
SELECT qCashierLog.CashierName,
(SELECT EventTime FROM qCashierLog qC
    WHERE qC.EventType = 1 AND
    qC.CashierCode = qCashierLog.CashierCode AND
    qC.EventDate = qCashierLog.EventDate ORDER BY qC.EventDate) AS [In],
(SELECT EventTime FROM qCashierLog qC
    WHERE qC.EventType = 2 AND
    qC.CashierCode = qCashierLog.CashierCode AND
    (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1 ORDER BY qC.EventDate)) AS Out,
PayRate,
EventDate,
iif([In]>[Out],
1440+DateDiff('n',[In],[Out]),
DateDiff('n',[In],[Out])) AS Hours
 
FROM qCashierLog INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code
    WHERE EventType = 1 AND
    EventDate >= #04/01/2009# AND
    EventDate <= #04/30/2009#
 
ORDER BY EventDate

Open in new window

0
käµfm³d 👽Commented:
I put wrong column in Order By. Try this instead.
SELECT qCashierLog.CashierName,
(SELECT EventTime FROM qCashierLog qC
    WHERE qC.EventType = 1 AND
    qC.CashierCode = qCashierLog.CashierCode AND
    qC.EventDate = qCashierLog.EventDate ORDER BY qC.EventTime) AS [In],
(SELECT EventTime FROM qCashierLog qC
    WHERE qC.EventType = 2 AND
    qC.CashierCode = qCashierLog.CashierCode AND
    (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1 ORDER BY qC.EventTime)) AS Out,
PayRate,
EventDate,
iif([In]>[Out],
1440+DateDiff('n',[In],[Out]),
DateDiff('n',[In],[Out])) AS Hours
 
FROM qCashierLog INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code
    WHERE EventType = 1 AND
    EventDate >= #04/01/2009# AND
    EventDate <= #04/30/2009#
 
ORDER BY EventDate

Open in new window

0
richard_garAuthor Commented:
I get the error:

At most one record can be returned by this subquery.

When I remove the 'TOP 1'
0
richard_garAuthor Commented:
there is an error in your santax and I cannot work out where it is pointing at:

SELECT qCashierLog.CashierName,
(SELECT EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND
    (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1 ORDER BY qC.EventTime))

I cannot see where though
0
käµfm³d 👽Commented:
Ok. I see now why my previous post was a mistake. Perhaps the following. In lines 11 and 12, change "CashierName" to whatever the primary key of your qCashierLog table is.
SELECT qCashierLog.CashierName,
q2.EventTime AS [IN],
q3.EventTime AS [OUT],
PayRate,
EventDate,
iif([In]>[Out],
1440+DateDiff('n',[In],[Out]),
DateDiff('n',[In],[Out])) AS Hours
 
FROM qCashierLog q1 INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code
INNER JOIN qCashierLog q2 ON q1.CashierName = q2.CashierName
INNER JOIN qCashierLog q3 ON q1.CashierName = q3.CashierName
    WHERE EventType = 1 AND
    EventDate >= #04/01/2009# AND
    EventDate <= #04/30/2009# AND
    q2.EventType = 1 AND q2.CashierCode = qCashierLog.CashierCode AND
    q2.EventDate = qCashierLog.EventDate AND
    q3.CashierCode = qCashierLog.CashierCode AND
    (q3.EventDate = qCashierLog.EventDate OR q3.EventDate = qCashierLog.EventDate + 1)
 
ORDER BY EventDate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.