how to create item numbers column in a Query Options

[mezzanine1974]

Is there anyway to place a column (Item#) in a query, which will show
the item numbers of the results. Say that, if query result has 13
items, Item# values will be sequentially 1,2,3...11,12,13.
I have a feeling that it might be solved by usind DCOUNT function.
Thanks

 

[Guest]

You can create a column using either DCount() or a subquery. You might want
to search google on ranking query.

If you don't find anything, come back with some table and field names as
well as what field values will be used to create the order.

 

[mezzanine1974]

Duane,
I will be greatly appreceated for your any help. I have been looking
for an answer to this question.
The query what i need is very simple SELECT query. Say that:
Table: Tbl1 (Product_ID, Price)
Query: Qry1 (will list the product prices for the products where
criteria is - Carpet Tiles - )

My target is to create a XYchart which will show me variation of the
prices in the query results according to query item number. I demand
such a query output:

Item# Price
1 145
2 156
3 139
.. .
.. .
.. .
n 142

Since I can not create an Item# column in the Qry1, there is no way to
put Price into chart.
Dont we have any simple function for that?

Many thanks for your helps!

 

[Guest]

"If you don't find anything, come back with some table and field names as
well as what field values will be used to create the order."

You didn't provide enough information about what field stores "Carpet Tiles"
or what field describes the order.

In addition I googled "query rank" and found the answer.

 

[mezzanine1974]

I am trying my best to be more clear.

I checked the answer that you suggested (googled "query rank"). My
problem is not to understand SQL statements well. So that, I can
create my Queries on Grid.
Let me give you all parameters what we need to create this SQL.

I have table "TblProducts" which has two fields "ProductName" and
"ProductPrice".
Query will search the "ProductName" field with a criteria "Carpet
Tile".
SQL underlying this grid is exactly like that up to now:

SELECT TblProducts.ProductName, TblProducts.ProductPrice
FROM TblProducts
WHERE (((TblProducts.ProductName)="Carpet Tile"));

How should I modify this SQL statements to get a new Item# column (or
simply say "RANK") to the next "TblProducts.ProductName" and
"TblProducts.ProductPrice" ?

I promise that I will be patient nex time

Regards.

 

[Guest]

You still haven't stated "what field values will be used to create the
order". Assuming you want these ordered by ProductPrice, try:

SELECT ProductName, ProductPrice, (SELECT Count(*) FROM tblProducts B WHERE
B.ProductName = "Carpet Tile" And B.ProductPrice <= tblProducts.ProductPrice)
As Rank
FROM TblProducts
WHERE ProductName="Carpet Tile";

 

[mezzanine1974]

Duane,

Many thanks for your help. I have used the SQL above and I saw how it
looks like in Grid mod. So, I understand better what Sub-query is!

You are still asking that I have not stated yet "what field values
will be used to create the
order". I apologize for missing information. But beleive me, i
supposed that everything is clear.

what field values will be used to create the
order? Answer is "None of The Fields". I mean, Ranked numbers will be
free from any other fields in the Query. Because, rank numbers will be
sequantial numbers starting from 1 upto N which is total item number
of the Query.

Regardless you will make a final comment on this e-mail, i appreceated
for your helps anyway.

 

[John W. Vinson]

what field values will be used to create the
order? Answer is "None of The Fields". I mean, Ranked numbers will be
free from any other fields in the Query. Because, rank numbers will be
sequantial numbers starting from 1 upto N which is total item number
of the Query.

I'm sorry, but this makes no sense. You want Access to automatically assign a
rank... but there is nothing in the data which would allow Access to do so!

If you had a stack of 3x5 cards with the information in your report, and
shuffled them, how would YOU assign a rank order to correctly sort the cards?

John W. Vinson [MVP]

 

[mezzanine1974]

Hello John,
I understood what point is! All what I need is to place a new
autonumber field into "TblProducts" and I have to use this field to
creat the order. I already tested and it is working!
Duane Hookom was also giving warning repeatedly about this missing
information as well.

I think SQL statements (please see below) are called as SUB-QUERY! Do
you mind if you tell me a web site to study this issue more? I have an
excellent access book: written by John L. Viescas. But i can not find
a specific chapter which is dedicated to this concept.

(SELECT Count(*) FROM tblProducts B WHERE
B.ProductName = "Carpet Tile" And B.ProductPrice <=
tblProducts.ProductPrice)

Thank you both!!