473,714 Members | 2,021 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Numbering rows based on group by

25 New Member

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 11826
122 New Member
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 Recognized Expert Moderator MVP
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
25 New Member
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,569 Recognized Expert Moderator MVP
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
25 New Member
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,569 Recognized Expert Moderator MVP
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 A 01-12-2004 1:37
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 nvarchar(50) RecordText ntext RecordDate DateTime What I'm trying to do is to group all records by ParentUniqueID
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 a University), START_DT, END_DT. I want to create rows that would be for each session and each day, so for session 200102 that starts 09/10/2000 and ends 12/15/2000 I want 96 rows, with 200102 for Session, and the 96 days in DAY. -- Source table: CREATE TABLE (
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 that thread, so I'm posting it again. It is not a simple "numbering records on a report" question. It is more complex than that. I have a report that shows the results of a query. One of the fields is an autonumber field from the query which shows for instance: 120, 121 , 122 for 3 records. ...
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 statement grdSP.Items.Count with the datagrid grdSP with paging, it returns the page size (5,10..) not the total results. How can I get the total no? Thanks in advance, Buran
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 box in the first row then I want the second row to display. If they put a value in the text box in the second row then display the third row etc. etc. etc. to 10 rows. I'm pretty new to javascript, so I'm not to sure where to start. Any help would be great, thanks a lot.
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 on the Datepart function that only displays output based on specific day of week. I want query to check the stored date in the arrival column and based on that date that is examined by the date part function only return rows based on day of week .Here is my sql. SELECT...
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 validation. Could anyone please tell me how to get the number of duplicate rows based on a particular cell value of each these duplicate rows. I mean all the cell values of a row will not be duplicated but a individual columns cell value will be duplicated and I need to create a separate excel with...
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 into a table according to a date range supplied by a user (eg txtRDateStart & txtRDateEnd). The script was envisaged to be able to also gather a time and text description that would be repeated within each row. For example: The user would enter... Start Date: 13/03/2010 End Date: 17/03/2010...
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 I want ROWS returned to from the other 9 worksheets based on values in the "C" column of each worksheet. The "C" column is a priority column. So in other words, if PRIORITY is 1 then I want those rows returned first from all worksheets, then iterate through and return all PRIORITY 2's all the way...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.