Numbering Records from a Query

[Guest]

Can someone help me with a ranking (numbering) question. We are trying to
assign ranking to a series of products in our warehouse based on sales volume
by sales dollars, and time spent phyically picking the items. What we want
to accomplish is sorting a query and numbering the items from 1 to 100 in
order of dollar sales. We then want to assign another number rank by how
often the item is picked by warehouse staff. By adding these numbers
together we will have a list that shows high value sales as well as time
spent picking the item so we can organize the warehouse better.

My Question: How can I run a query on these items and assign a number to
the record that corresponds to the order that it appears in the Query? i.e.
what record number is the item?

Eg.
1 | Eggs | $400
2 | Butter | $350
3 | Bread | $300

We want to be able to run this periodically and make adjustment to the
warehouse as needed as the dollar sales change.

Thank you.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks Roger. I took the sample code from the first query:

Sequence: DCount("AuID","Authors","AuID <=" & [AuID])
I received this error however...

The expression you entered as a query parameter produced this error: 'the
object doesn't contain the Automation object 'R09354."

R09354 is the code of the first item in the query.

Any ideas?

----------------------------------------

 

[Guest]

I forgot to post the code I changed yours to....
Sequence: DCount("de_code","tbl_de_products","de_code <=" & [de_code])
I am using Access 2002

 

[Douglas J. Steele]

Since AuID is obviously text, and not a number, you need quotes around the
value:

Sequence: DCount("AuID","Authors","AuID <='" & [AuID] & "'")

Exagerated for clarity, that's

Sequence: DCount("AuID","Authors","AuID <= ' " & [AuID] & " ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Roger. I took the sample code from the first query:

Sequence: DCount("AuID","Authors","AuID <=" & [AuID])
I received this error however...

The expression you entered as a query parameter produced this error: 'the
object doesn't contain the Automation object 'R09354."

R09354 is the code of the first item in the query.

Any ideas?

----------------------------------------

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Did Doug's response answer your question?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I forgot to post the code I changed yours to....
Sequence: DCount("de_code","tbl_de_products","de_code <=" & [de_code])
I am using Access 2002

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

We are getting closer, I get no error message now however all the values in
the feild are 0

revised calculation:

Sequence2: DCount("de_code","tbl_de_products","de_code <= ' " & [de_code] &
" ' ")

Did Doug's response answer your question?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I forgot to post the code I changed yours to....
Sequence: DCount("de_code","tbl_de_products","de_code <=" & [de_code])
I am using Access 2002

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Can someone help me with a ranking (numbering) question. We are trying
to
assign ranking to a series of products in our warehouse based on sales
volume
by sales dollars, and time spent phyically picking the items. What we
want
to accomplish is sorting a query and numbering the items from 1 to 100
in
order of dollar sales. We then want to assign another number rank by
how
often the item is picked by warehouse staff. By adding these numbers
together we will have a list that shows high value sales as well as
time
spent picking the item so we can organize the warehouse better.

My Question: How can I run a query on these items and assign a number
to
the record that corresponds to the order that it appears in the Query?
i.e.
what record number is the item?

Eg.
1 | Eggs | $400
2 | Butter | $350
3 | Bread | $300

We want to be able to run this periodically and make adjustment to the
warehouse as needed as the dollar sales change.

Thank you.

 

[Douglas J. Steele]

Do you really have the spaces around the single quote? It was meant for
clarity only. You need to remove it from the actual calculation:

Sequence2: DCount("de_code","tbl_de_products","de_code <= '" & [de_code] &
"'")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

We are getting closer, I get no error message now however all the values
in
the feild are 0

revised calculation:

Sequence2: DCount("de_code","tbl_de_products","de_code <= ' " & [de_code]
&
" ' ")

Did Doug's response answer your question?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I forgot to post the code I changed yours to....
Sequence: DCount("de_code","tbl_de_products","de_code <=" & [de_code])
I am using Access 2002



:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Can someone help me with a ranking (numbering) question. We are
trying
to
assign ranking to a series of products in our warehouse based on
sales
volume
by sales dollars, and time spent phyically picking the items. What
we
want
to accomplish is sorting a query and numbering the items from 1 to
100
in
order of dollar sales. We then want to assign another number rank
by
how
often the item is picked by warehouse staff. By adding these
numbers
together we will have a list that shows high value sales as well as
time
spent picking the item so we can organize the warehouse better.

My Question: How can I run a query on these items and assign a
number
to
the record that corresponds to the order that it appears in the
Query?
i.e.
what record number is the item?

Eg.
1 | Eggs | $400
2 | Butter | $350
3 | Bread | $300

We want to be able to run this periodically and make adjustment to
the
warehouse as needed as the dollar sales change.

Thank you.

 

[Guest]

Got it!

Thanks

r

Do you really have the spaces around the single quote? It was meant for
clarity only. You need to remove it from the actual calculation:

Sequence2: DCount("de_code","tbl_de_products","de_code <= '" & [de_code] &
"'")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

We are getting closer, I get no error message now however all the values
in
the feild are 0

revised calculation:

Sequence2: DCount("de_code","tbl_de_products","de_code <= ' " & [de_code]
&
" ' ")

Did Doug's response answer your question?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I forgot to post the code I changed yours to....
Sequence: DCount("de_code","tbl_de_products","de_code <=" & [de_code])
I am using Access 2002



:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "NumberedQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Can someone help me with a ranking (numbering) question. We are
trying
to
assign ranking to a series of products in our warehouse based on
sales
volume
by sales dollars, and time spent phyically picking the items. What
we
want
to accomplish is sorting a query and numbering the items from 1 to
100
in
order of dollar sales. We then want to assign another number rank
by
how
often the item is picked by warehouse staff. By adding these
numbers
together we will have a list that shows high value sales as well as
time
spent picking the item so we can organize the warehouse better.

My Question: How can I run a query on these items and assign a
number
to
the record that corresponds to the order that it appears in the
Query?
i.e.
what record number is the item?

Eg.
1 | Eggs | $400
2 | Butter | $350
3 | Bread | $300

We want to be able to run this periodically and make adjustment to
the
warehouse as needed as the dollar sales change.

Thank you.