472,353 Members | 1,654 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Numbering rows based on group by


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 11443
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]
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
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
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;
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
32,511 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
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
32,511 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

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

Similar topics

by: BigD | last post by:
OK, Here is my challenge. If I have a query that produces the following Item Sold_On A 01-10-2004 8:03 A 01-11-2004 10:05...
by: laurenq uantrell | last post by:
I know I should know this but here goes... I have a table with the following rows: UniqueID int IDENTITY ParentUniqueID int RecordLabel...
by: rcamarda | last post by:
Hi, I wish to create new rows of data based on a source table. Example: I have a file that contains a SESSION (time roughly a calendar quarter for...
by: Wayne Aprato | last post by:
I posted this yesterday and it seems like a moderator has thrown it in another thread. This is a totally different question to the one asked in...
by: buran | last post by:
Dear ASP.NET Users, How can I get the total number of rows selected by the command text of the datagrid (items in datagrid)? If I use the...
by: jimstruckster | last post by:
I have a table with 10 rows, I want all rows except for the first to be hidden when the page first opens up. If the user puts a value in a text...
by: tasmontique | last post by:
I have an access table that outputs to excel using a query . However what I am trying to do is under the arrival date column specify a criteria based...
by: ravir81 | last post by:
Hi, I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the...
by: Vinda | last post by:
Hi Bytes, Using a previous question as a base Access 2000 Inserting multiple rows based on a date range. I also wanted to insert multiple rows...
by: patrick keady | last post by:
This feels simple. But not enough coffee I suppose. Cant get it to work. I have about ten worksheets in a workbook. The first worksheet is where...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.