Update field depending on date

[richary]

Hi

I wonder if anyone can help me out on a db i have inherited. Basically I need to add a field to a table and backfill the field with a number depending on a date field.

The new field (CallID) needs to record the order in which calls are received on a certain date (CallDate in the table). So for example the first call on 1/3/05 CallID=1, the second on 1/3/05 CallID=2. The following day CallID restarts at CallID=1.

So I'm looking for an update query which will set CallID for the several hundred existing records in the database. If anyone can help with a pointer or too, or example SQL, I would be very grateful

Cheers

 

[reclusivemonkey]

How are you determining which is the first/second/third call?

 

[richary]

How are you determining which is the first/second/third call?

Good question. There is a PK already defined and is an auto-number field, so it will be sufficient to use that to order calls.

Cheers

 

[reclusivemonkey]

What's your VBA like? The only way I can think to do this would be via code. For each date you will need to create a loop, which will then sequentially create a value for each Primary Key (I am assuming you aren't lucky enough to have this already in the format of 1,2,3 etc. already, or you wouldn't be asking!). The code will be quite complex though, so if you are new to this, you might want to take it a step at a time. I don't mind going through this as its the only way to do this I suspect, but it might take more than a couple of posts ;-)

 

[richary]

My VBA is pretty reasonable. My thought in posting this was that this would be something that would be possible to execute in an SQL query.

I reckon I could write this in VBA, thanks for the offer of help though

Thanks for the pointer as well!