Adding sequential numbers to generate a unique reference number

[Navin1991]

Hello, I am new to access and I don't know if this has been posted before but I haven't been able to find anything that can help me.

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

Thanks a lot for you help,
Sincerely Navin

 

[RuralGuy]

Perhaps this link will be helpful: http://www.baldyweb.com/CustomAutonumber.htm

 

[jdraw]

I would store the data in separate fields
DateReceived and SeqNumberForDate and would strongly consider using "meaningless" autonumbers for PK.

Just my $.02

Good luck

 

[Navin1991]

Thanks RuralGuy but the link that you provided is useful if I wanted to create a continuous sequential number which doesn't help in my situation. Because when the date changes it just continues the sequence instead of restarting it, since, it looks for the highest value.

 

[RuralGuy]

Unless the DMax() returns 0.

 

[MarkK]

Use DCount() with the date in question, and add one.

NextSeq = Nz(DCount("*", "Table", "RecDate = #" & Date() & "#"), 0) + 1

Notice that it counts the number of records for the date in question, so when the date changes, the count starts over. A hazard is that if you delete a record that isn't the last one you'll get a duplicate number, and a number will be missing.

 

[dj59]

This is close to solving my problem. I too want to have a sequencial numbering column in my query as follows:

ID seqNum
475 1
475 2
475 3
480 1
480 2
490 1
490 2
491 1

etc.

I tried this as suggested:

NextSeq =  Nz(DCount("*","Tbl_residence","CLIENT_ID <= " & [CLIENT_ID]),0)+1

But my result was:
ID seqNum
475 1
475 1
475 1
480 2
480 2
490 3
490 3
491 3

Thank you for any advice or direction.