updating a field with a count

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.

Hi Greg,

Could you please try explaining that in a different way. Having trouble
understanding the problem.

Bonnie
http://www.dataplus-svc.com

Greg wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

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

bhicks11 via AccessMonster.com wrote:
Hi Greg,

Could you please try explaining that in a different way. Having trouble
understanding the problem.

Bonnie
http://www.dataplus-svc.com

Greg wrote:
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.