Numbering rows based on group by

[Bay0519]

Hi,

I'm using access 2003 as front-end and sql 2000 as back-end. I have a query with the result like

EmpID Date Time In/Out
1111 3/1/11 7:00:00 AM In
1211 3/1/11 7:11:00 AM In
1111 3/1/11 12:00:00 PM Out
1211 3/1/11 1:00:00 PM Out

What I want is the result like

RowNumber EmpID Date Time In/Out
1 1111 3/1/11 7:00:00 AM In
2 1111 3/1/11 12:00:00 PM Out
1 1211 3/1/11 7:11:00 AM In
2 1211 3/1/11 1:00:00 PM Out

Can I do that? if yes, can someone please help me?

Thank you so much

[gershwyn]

You could do something like the following query (change "Table" to your actual table name, of course):

SELECT DCount(1,"Table","EmpID = " & [EmpID] & " AND Date <= #" & [Date] & "# AND Time <= #" & [Time] & "#") AS Row, [EmpID], [Date], [Time], [In/Out]
FROM [Table] ORDER BY [EmpID], [Date], [Time]
 

I'm guessing a little bit at your data types, in that the dlookup is expecting EmpID to be numeric and your date and time fields to be date/time.

Note that it doesn't actually group anything - from your sample data it looks like you are only really sorting, but I may be missing some of your intent.

I generally dislike using the dLookup and similar functions in queries, but it does produce the results you listed. With 4 records, it works very well, though you may find a performance hit when the number of records increases significantly.

[Rabbit]

You can convert the DCount into a subquery.

SELECT GroupingField, IDField, OtherFields,
   (SELECT Count(*)
    FROM TableName
    WHERE GroupingField = x.GroupingField
       AND IDField >= x.IDField
   ) AS Rank
FROM TableName AS x

[Bay0519]

thank you Gershwyn for your quick response

But when I tried your code, I get the correct row numbers only for the first group. here is my query:

 SELECT DCount(1,"test_in","EmployeeID = '" & [EmployeeID] & "' and Date <= #" & [Date] & "# and  time <= #" & [time] & "#") AS Row, test_in.EmployeeID, test_in.Date, test_in.Time, test_in.Status
FROM test_in
ORDER BY test_in.EmployeeID, test_in.Date, test_in.Time;
 

but here is the result that I got:

Row EmployeeID Date Time Status
1 3000 3/14/2011 7:08:00 AM In
2 3000 3/14/2011 10:13:00 AM In
3 3000 3/14/2011 12:39:00 PM In
1 3000 3/15/2011 7:03:00 AM In
4 3000 3/15/2011 11:33:00 AM In
3 3000 3/16/2011 7:08:00 AM In
6 3000 3/16/2011 12:15:00 PM In
4 3000 3/17/2011 7:34:00 AM In
9 3000 3/17/2011 1:32:00 PM In
2 3000 3/18/2011 7:05:00 AM In
10 3000 3/18/2011 12:39:00 PM In

when it should be:

Row EmployeeID Date Time Status
1 3000 3/14/2011 7:08:00 AM In
2 3000 3/14/2011 10:13:00 AM In
3 3000 3/14/2011 12:39:00 PM In
1 3000 3/15/2011 7:03:00 AM In
2 3000 3/15/2011 11:33:00 AM In
1 3000 3/16/2011 7:08:00 AM In
2 3000 3/16/2011 12:15:00 PM In
1 3000 3/17/2011 7:34:00 AM In
2 3000 3/17/2011 1:32:00 PM In
1 3000 3/18/2011 7:05:00 AM In
2 3000 3/18/2011 12:39:00 PM In

Please help....

[NeoPa]

I looked at this and found there wasn't enough information in the question to suggest an answer. The example data (a very good idea BTW) also had too few different records to be fully clear. Let me be clear. This wasn't a bad attempt at putting the question clearly. It simply left some unresolved questions.

1. Is it true that each EmpID can have many Ins and Outs?
2. Is it true that an EmpID can have two Ins without an Out between them (as implied by your post #4)?
3. Unless your data has no Outs at all, please provide some example data showing what you expect with data for multiple EmpIDs, with multiple Ins and multiple Outs.

PS. It is almost never a good idea to store dates and times separately when they both refer to the same item. You should be looking at storing a time which includes the date.

[Bay0519]

Hi Neopa,

sorry for the confusion,

basically, I'm working on attendance report that will show all clock ins and outs for each employee daily. so employee can clock in but forgot to clock out or they can clock out but forgot to clock in. and they can clock in and out as many as they want like break, lunch, etc
the way the data stored in sql are employeeID as text, Status (in/out), timestamp (date and time).

I've created a crosstab query that will give me result like this
employeeID Date In Out
3000 3/14/2011 7:08:00 AM -- this is in
3000 3/14/2011 10:13:00 AM -- this is in
3000 3/14/2011 12:39:00 PM -- this is in
3000 3/14/2011 4:30:00 PM --this is out

what I need to show on the report is with in and out side by side.
EmpID Date In Out
3000 3/14/2011 7:08:00 AM
10:13:00 AM
12:39:00 PM 4:30:00 PM
I can show the report on the first and last data, but can't show it if it's the second, third, etc. That's why I was thinking about numbering the row. Can you help me on how can I achieve this?
Hope it helps.
Thank you

[NeoPa]

Reasonably well explained. As a general rule though, when questions are asked, and especially when they are numbered, it is sensible to respond to them all individually, and if possible number the replies so that all who are reading the thread can follow easily. I tried to make it as easy as I could for you. Let's see if I can post answers to the questions you have covered (to make it easier for all to follow) :

1. Yes. Employees may clock in and out a number of times in a day to go away for lunch or other breaks.
2. Yes. Employees may forget to clock out sometimes.
3. No data supplied.

Unfortunately, your explanation does mean we are dealing with a completely different scenario from that outlined in the question (The requested output is non-trivially different from what was originally requested). I'm busy ATM so I can't spend the pretty considerable time required just now to formulate a response, but your explanation has at least made it clear now what you're after, so you may get some more appropriate responses from elsewhere too.