updating a field with a count

[Greg]

I have a auction database where I have x number of steers, y number of lambs,
z number of rabbits, etc. After I get the database in order I would like to
automate the sale_order field with the sale number for each animal within
each species (so steers run from 1 to x, lambs run from 1 to y, etc). Is
there an way to do this using update queries.

Thanks for any help.

 

[John Spencer]

Possibly. More details about the structure of your table is required.

For instance, I assume you have a field that hold the animal type - steers,
lambs, etc.

Do you have a primary key field (one that puts the records in a unique order)-
an autonumber field for instance?

What you are looking for is a ranking query.
This sample query assumes that AnimalType is a text field and the Primary Key
Field is a number field.

UPDATE tblAnimals
SET Sale_Order = DCount("*","tblAnimals","AnimalType='" & [AnimalType] & "'
AND [PrimaryKeyField] <" & [PrimaryKeyField]) + 1

Your other option is to write a VBA function that will set the numbers
incrementally.

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