View Single Post
  #9 (permalink)  
Old October 17th, 2003, 09:20 PM
scott scott is offline
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal: Thanks GREATLY for the reply!! It looks great and will work on implementing it tomorrow when I go back into the office. However, where should I put the query? And could you give a newbie a quick explain of T1, T2 etc....? I hate to be such a pain in the ***, but I've struggled with this one so long, my brain is fried! Also, will this "store" the information in a table for retrieval at a later time? My boss wants to be able to, say, pull up a listing (much like the one you have inserted!) on a daily, weekly, monthly, quarterly, etc... basis. So, for example, on 10/31/03, she may choose to run a report showing all entries (with the job log "sequencing" intact) for 10/01/03....

Thanks in advance from a non-IT person. You know what they say, a little knowledge is a dangerous thing! Unfortunately in this situation, I have no choice but to come up with a solution! ARRRGH!

Thanks again,
Scott
New York City


Quote:
quote:Originally posted by sal
 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
Reply With Quote