AutoNumber based on Model, Serial & Date

[Dave]

I have a table of 400,000 records that I want to change the numbering based
Model, Serial then Date Order

Model Serial Date Number
A 1 1/1/2005 1
A 1 1/2/2005 2
B 1 1/1/2005 1
B 1 1/2/2005 2

Can you help get me started?

Thanks

 

[Tom van Stiphout]

On Wed, 17 Sep 2008 06:02:01 -0700, Dave

Tables are unordered. Create a query and apply the ordering you need.

-Tom.
Microsoft Access MVP

 

[Dave]

I can do that, but how do I change the numbering the way I described?

Dave

 

[MikeB]

I can do that, but how do I change the numbering the way I described?

This might be where you have to describe more completely what your
problem is.

If it is a matter of arranging the data in the order you show, you can
create a query and sort the fields in the query.

Otherwise I can't figure out what your problem is, since Autonumber is
a special kind of field in an access database that ensures that each
record will get a unique record number.

 

[BruceM]

You will need to describe the details of what you want to achieve. Are all
of the fields in the table, or are you trying to calculate the value in the
Number field based on the contents of the other fields? If the latter, do
you want to restart the numbering for each Model, or for each Serial Number
(presumably there are Serial Numbers above 1), or what exactly?

 

[John W. Vinson]

I can do that, but how do I change the numbering the way I described?

You didn't describe it. You said "change the numbering based
Model, Serial then Date Order". There was no number shown to be changed nor
did you say how you want to change it.

Please describe *what you want to accomplish* in real-world terms. We can't
see your database, and can't read your mind!

 

[John Spencer]

To display the information you could use the following. Which will probably be
fairly slow

SELECT A.Model, A.Serial, A.[Date]
, Count(B.Model) as Rank
FROM TheTable as A INNER JOIN TheTable As B
On A.Model = B.Model
and A.Serial = B Serial
AND A.[Date] <= B.[Date]
GROUP BY A.Model, A.Serial, A.Date

To update the Number in Access is going to be tediously slow. And subject to
becoming incorrect when new records are added.

UPDATE YourTable
SET [Number] = DCount("*","YourTable","Model=""" & [Model] & """ AND Serial
=""" & [Serial] & """ AND [Date]<=#" & Format([Date],"yyyy-mm-dd") & "#")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

There is also the problem of what to do with any two or more rows with the
same model, serial and date values. If this is a possibility it would be
necessary to add an autonumber or otherwise uniquely valued column to the
table and include this in the JOIN clause or in the criteria for the DCount
function so as to arbitrarily distinguish between those rows.

Ken Sheridan
Stafford, England

To display the information you could use the following. Which will probably be
fairly slow

SELECT A.Model, A.Serial, A.[Date]
, Count(B.Model) as Rank
FROM TheTable as A INNER JOIN TheTable As B
On A.Model = B.Model
and A.Serial = B Serial
AND A.[Date] <= B.[Date]
GROUP BY A.Model, A.Serial, A.Date

To update the Number in Access is going to be tediously slow. And subject to
becoming incorrect when new records are added.

UPDATE YourTable
SET [Number] = DCount("*","YourTable","Model=""" & [Model] & """ AND Serial
=""" & [Serial] & """ AND [Date]<=#" & Format([Date],"yyyy-mm-dd") & "#")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a table of 400,000 records that I want to change the numbering based
Model, Serial then Date Order

Model Serial Date Number
A 1 1/1/2005 1
A 1 1/2/2005 2
B 1 1/1/2005 1
B 1 1/2/2005 2

Can you help get me started?

Thanks