View Single Post
 
Old October 17th, 2003, 08:10 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Scott,
Please double check my propossed solution. Since you already have an auto number. I can guarantee that this will always work assigning the correct number to your record, as long as you never delete any records. Like This

ID DailyID DateIn TimeIn
105 1 11/16/2003 9:01:47 AM
106 2 11/16/2003 9:01:48 AM
107 3 11/16/2003 9:01:48 AM
108 4 11/16/2003 4:45:00 PM
109 1 11/17/2003 8:59:00 AM First one
110 2 11/17/2003 10:00:00 AM

You get the idea

By using this in a query, you will not have to worry about where to put what code. the query takes care of it all for you. Just do not delete any records from it, and the numbers will never change. If you delete a record, It should not matter much.



SELECT T1.ID, (SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID) AS DailyID, T1.TimeIn, T1.DateIn
FROM T1
WHERE ((((SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID))<>0));

By the way, substitute T1 with the name of your table ans voila, you get

ID DailyID TimeIn DateIn All Other fields here
__________________________________________________ __________
1 1 12/30/1899 19:52:03 10/17/2003
2 2 12/30/1899 19:52:05 10/17/2003
3 3 12/30/1899 19:52:08 10/17/2003
4 4 12/30/1899 19:52:09 10/17/2003
5 5 12/30/1899 19:52:09 10/17/2003
6 6 12/30/1899 19:52:09 10/17/2003
7 7 12/30/1899 19:52:09 10/17/2003
8 8 12/30/1899 19:52:10 10/17/2003
9 9 12/30/1899 19:52:10 10/17/2003
10 1 12/30/1899 19:52:32 10/18/2003
11 2 12/30/1899 19:52:37 10/18/2003
12 3 12/30/1899 19:52:40 10/18/2003
13 4 12/30/1899 19:52:41 10/18/2003
14 5 12/30/1899 19:52:41 10/18/2003
15 6 12/30/1899 19:52:42 10/18/2003
16 7 12/30/1899 19:52:43 10/18/2003

It works. Notice multiple entries within the same second, and no problem.

Let me know if you do not know where to put this in the query.



Sal