469,271 Members | 1,685 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Numbering rows based on group by

25
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
Mar 21 '11 #1
6 10689
gershwyn
122 100+
You could do something like the following query (change "Table" to your actual table name, of course):
Expand|Select|Wrap|Line Numbers
  1. SELECT DCount(1,"Table","EmpID = " & [EmpID] & " AND Date <= #" & [Date] & "# AND Time <= #" & [Time] & "#") AS Row, [EmpID], [Date], [Time], [In/Out]
  2. FROM [Table] ORDER BY [EmpID], [Date], [Time]
  3.  
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.
Mar 21 '11 #2
Rabbit
12,516 Expert Mod 8TB
You can convert the DCount into a subquery.
Expand|Select|Wrap|Line Numbers
  1. SELECT GroupingField, IDField, OtherFields,
  2.    (SELECT Count(*)
  3.     FROM TableName
  4.     WHERE GroupingField = x.GroupingField
  5.        AND IDField >= x.IDField
  6.    ) AS Rank
  7. FROM TableName AS x
Mar 21 '11 #3
Bay0519
25
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:

Expand|Select|Wrap|Line Numbers
  1.  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
  2. FROM test_in
  3. ORDER BY test_in.EmployeeID, test_in.Date, test_in.Time;
  4.  
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....
Mar 21 '11 #4
NeoPa
32,171 Expert Mod 16PB
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.
Mar 22 '11 #5
Bay0519
25
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
Mar 22 '11 #6
NeoPa
32,171 Expert Mod 16PB
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.
Mar 22 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by BigD | last post: by
7 posts views Thread by laurenq uantrell | last post: by
3 posts views Thread by rcamarda | last post: by
1 post views Thread by buran | last post: by
11 posts views Thread by jimstruckster | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.