Query row number

G

Guest

I have a query that sorts infomation in a particular sequence. I want to put
an expression in the query that numbers the records sequentially. Is there a
simple expression I can enter to tell me the row number as a field in the
query?
 
K

Ken Snell \(MVP\)

You'll need a field to show the ranking. In order to give you an example,
post the SQL statement of the query that you're using now and identify what
the primary key field(s) are that are in the query.
 
G

Guest

Hi Ken
Thanks for the response. The query is:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome, tblMeetingItem.Resolved
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;

The PK is MeetingItemNo but the items are in SeqNo order and I wanted to
number them in that order.
 
K

Ken Snell \(MVP\)

OK try this:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(T.SeqNo) FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo
ORDER BY T.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;


--

Ken Snell
<MS ACCESS MVP>
 
G

Guest

Thanks Ken but got an error. You tried to execute a query that does not
include the specified expression 'T.SeqNo' as part of an aggregate function.
I am getting a bit out of my depth here (but learning fast). Can you make
any suggestions?
Thanks
 
J

John Spencer

Drop the order by in the sub-query. It is not needed AND it is what is
causing the error.

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome,
tblMeetingItem.Resolved,
(SELECT Count(T.SeqNo)
FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
K

Ken Snell \(MVP\)

Try this variation:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(*) FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;
 
G

Guest

Well done Ken! There was a missing bracket but it worked. I have had to add
on a small section to make it work. Basically each meeting has many items
each with a sequence number. Hence meeting one might have sequence 1, 2, 3,
4 and meeting two 1, 2, 4, 7. Screws up the sequence numbers if I look at
them all (If there are 2 sequence number 1's the number from the query is 2
for both of the 1's. Had to apply a where clause to only look at items
relating to the meeting displayed on the screen.

Just for the record, the final working SQL is

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(*) FROM tblMeetingItem AS T WHERE T.MeetingNo =
[Forms]![frmMeetings]![MeetingNo] AND T.SeqNo <= tblMeetingItem.SeqNo) AS
ItemNo
FROM tblMeetingItem
WHERE (((tblMeetingItem.MeetingNo)=[Forms]![frmMeetings]![MeetingNo]))
ORDER BY tblMeetingItem.SeqNo;

Thanks again Ken for your valuable assistance.
 
J

JBB

Some years later, this conversations is still usefull, thanks to both.

I have a similar problem and I'm trying to apply the same "trick" using a sub-query.
But as soon as I introduce a WHERE clause in the sub-query Access crashes... any hints?
My Table has 30.121 registers could it be too much for this type of recursive sub-queries?

My query tries to give a serial number the changes of price in a table of articles. This is in order to be able to filter later for example the last 3 prices that a certain article has had and the dates in which they were changed:

SELECT tblPriceHistory.Article, tblPriceHistory.Date, tblPriceHistory.Price, (SELECT Count(*) FROM tblPriceHistory AS Temp WHERE (Temp.Article=tblPriceHistory.Article) AND (Temp.Date <= tblPriceHistory.Date)) AS ItemNo
FROM tblPriceHistory;


Thanks for any help,

JBB

I have a query that sorts infomation in a particular sequence. I want to put
an expression in the query that numbers the records sequentially. Is there a
simple expression I can enter to tell me the row number as a field in the
query?
On Thursday, June 21, 2007 11:47 PM Ken Snell \(MVP\) wrote:
You'll need a field to show the ranking. In order to give you an example,
post the SQL statement of the query that you're using now and identify what
the primary key field(s) are that are in the query.
--

Ken Snell
<MS ACCESS MVP>


news:[email protected]...
Click to expand...
On Saturday, June 23, 2007 2:56 AM Neville wrote:
Well done Ken! There was a missing bracket but it worked. I have had to add
on a small section to make it work. Basically each meeting has many items
each with a sequence number. Hence meeting one might have sequence 1, 2, 3,
4 and meeting two 1, 2, 4, 7. Screws up the sequence numbers if I look at
them all (If there are 2 sequence number 1's the number from the query is 2
for both of the 1's. Had to apply a where clause to only look at items
relating to the meeting displayed on the screen.

Just for the record, the final working SQL is

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(*) FROM tblMeetingItem AS T WHERE T.MeetingNo =
[Forms]![frmMeetings]![MeetingNo] AND T.SeqNo <= tblMeetingItem.SeqNo) AS
ItemNo
FROM tblMeetingItem
WHERE (((tblMeetingItem.MeetingNo)=[Forms]![frmMeetings]![MeetingNo]))
ORDER BY tblMeetingItem.SeqNo;

Thanks again Ken for your valuable assistance.


"Ken Snell (MVP)" wrote:
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows

"Crash"? As in "shuts down", or "freezes and needs to be killed via task
manager"? Please be more descriptive about your symptoms... I've seen some
people describe error messages as crashes.

The only issue I can see is you've used a reserved keyword (Date) for a
field name. You really need to change the name of that field. Using reserved
keywords for object names can lead to many hard-to-debug problems. One way
to avoid having to refer to a list of reserved keywords when designing your
tables is to use more descriptive names: perhaps "PriceChangeDate" instead
of simply "Date". If the design is set in stone, you are going to need to
remember to surround that name with brackets whenever you use it in your
queries so as to avoid some of the more common problems that name can cause.
Some years later, this conversations is still usefull, thanks to both.

I have a similar problem and I'm trying to apply the same "trick"
using a sub-query.
But as soon as I introduce a WHERE clause in the sub-query Access
crashes... any hints?
My Table has 30.121 registers could it be too much for this type of
recursive sub-queries?

My query tries to give a serial number the changes of price in a
table of articles. This is in order to be able to filter later for
example the last 3 prices that a certain article has had and the
dates in which they were changed:

SELECT tblPriceHistory.Article, tblPriceHistory.Date,
tblPriceHistory.Price, (SELECT Count(*) FROM tblPriceHistory AS Temp
WHERE (Temp.Article=tblPriceHistory.Article) AND (Temp.Date <=
tblPriceHistory.Date)) AS ItemNo
FROM tblPriceHistory;


Thanks for any help,

JBB

I have a query that sorts infomation in a particular sequence. I
want to put an expression in the query that numbers the records
sequentially. Is there a simple expression I can enter to tell me
the row number as a field in the query?
On Thursday, June 21, 2007 11:47 PM Ken Snell \(MVP\) wrote:
You'll need a field to show the ranking. In order to give you an
example, post the SQL statement of the query that you're using now
and identify what the primary key field(s) are that are in the
query. --

Ken Snell
<MS ACCESS MVP>


news:[email protected]...
Click to expand...
On Friday, June 22, 2007 12:37 AM Neville wrote:
Hi Ken
Thanks for the response. The query is:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;

The PK is MeetingItemNo but the items are in SeqNo order and I
wanted to number them in that order.


"Ken Snell (MVP)" wrote:
Click to expand...
SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved, (SELECT
Count(T.SeqNo) FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo
ORDER BY T.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;


--

Ken Snell
<MS ACCESS MVP>




news:[email protected]...
Click to expand...
On Friday, June 22, 2007 8:37 PM Neville wrote:
Thanks Ken but got an error. You tried to execute a query that
does not include the specified expression 'T.SeqNo' as part of
an aggregate function. I am getting a bit out of my depth here
(but learning fast). Can you make any suggestions?
Thanks

"Ken Snell (MVP)" wrote:
Click to expand...
On Friday, June 22, 2007 10:28 PM John Spencer wrote:
Drop the order by in the sub-query. It is not needed AND it is
what is causing the error.

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(T.SeqNo)
FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


NevilleT wrote:
Click to expand...
On Friday, June 22, 2007 10:54 PM Neville wrote:
No. That was not it. Deleted the Order By and got the same
error.

"John Spencer" wrote:
Click to expand...
SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved, (SELECT
Count(*) FROM tblMeetingItem AS T
WHERE T.SeqNo <= tblMeetingItem.SeqNo
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;

--

Ken Snell
<MS ACCESS MVP>


message
news:[email protected]...
Click to expand...
On Saturday, June 23, 2007 2:56 AM Neville wrote:
Well done Ken! There was a missing bracket but it worked.
I have had to add on a small section to make it work.
Basically each meeting has many items each with a sequence
number. Hence meeting one might have sequence 1, 2, 3, 4
and meeting two 1, 2, 4, 7. Screws up the sequence numbers
if I look at them all (If there are 2 sequence number 1's
the number from the query is 2 for both of the 1's. Had to
apply a where clause to only look at items relating to the
meeting displayed on the screen.

Just for the record, the final working SQL is

SELECT tblMeetingItem.MeetingItemNo,
tblMeetingItem.MeetingNo, tblMeetingItem.Description,
tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome,
tblMeetingItem.Resolved, (SELECT Count(*) FROM
tblMeetingItem AS T WHERE T.MeetingNo =
[Forms]![frmMeetings]![MeetingNo] AND T.SeqNo <=
tblMeetingItem.SeqNo) AS ItemNo
FROM tblMeetingItem
WHERE
(((tblMeetingItem.MeetingNo)=[Forms]![frmMeetings]![MeetingNo]))
ORDER BY tblMeetingItem.SeqNo;

Thanks again Ken for your valuable assistance.


"Ken Snell (MVP)" wrote:
Click to expand...
On Saturday, June 23, 2007 12:24 PM Ken Snell \(MVP\) wrote:
Sorry about that -- it was pretty late last night when I
typed that.... < g

Glad it is working now.
Click to expand...
Submitted via EggHeadCafe
New Features Of Microsoft Outlook 2010
Click to expand...
Click to expand...
Click to expand...
http://www.eggheadcafe.com/tutorial...8/new-features-of-microsoft-outlook-2010.aspx
 
J

John Spencer

An alternative query that might work for you is

SELECT A.Article
, A.[Date]
, A.Price
, 1 + Count(B.[Date]) AS ItemNo
FROM tblPriceHistory as A LEFT JOIN tblPriceHistory as B
ON A.Article = B.Article
AND A.[Date] < B.[Date]
GROUP BY A.Article, A.Date, A.Price


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

My query tries to give a serial number the changes of price in a
table of articles. This is in order to be able to filter later for
example the last 3 prices that a certain article has had and the
dates in which they were changed:

SELECT tblPriceHistory.Article, tblPriceHistory.Date,
tblPriceHistory.Price, (SELECT Count(*) FROM tblPriceHistory AS Temp
WHERE (Temp.Article=tblPriceHistory.Article) AND (Temp.Date<=
tblPriceHistory.Date)) AS ItemNo
FROM tblPriceHistory;


Thanks for any help,

JBB

I have a query that sorts infomation in a particular sequence. I
want to put an expression in the query that numbers the records
sequentially. Is there a simple expression I can enter to tell me
the row number as a field in the query?
Click to expand...
You'll need a field to show the ranking. In order to give you an
example, post the SQL statement of the query that you're using now
and identify what the primary key field(s) are that are in the
query. --

Ken Snell
<MS ACCESS MVP>
Click to expand...
Hi Ken
Thanks for the response. The query is:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;

The PK is MeetingItemNo but the items are in SeqNo order and I
wanted to number them in that order.


:
Click to expand...
OK try this:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved, (SELECT
Count(T.SeqNo) FROM tblMeetingItem AS T
WHERE T.SeqNo<= tblMeetingItem.SeqNo
ORDER BY T.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;
Click to expand...
Thanks Ken but got an error. You tried to execute a query that
does not include the specified expression 'T.SeqNo' as part of
an aggregate function. I am getting a bit out of my depth here
(but learning fast). Can you make any suggestions?
Thanks

:
Click to expand...
Drop the order by in the sub-query. It is not needed AND it is
what is causing the error.

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved,
(SELECT Count(T.SeqNo)
FROM tblMeetingItem AS T
WHERE T.SeqNo<= tblMeetingItem.SeqNo) AS RecordNumber
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


NevilleT wrote:
Click to expand...
No. That was not it. Deleted the Order By and got the same
error.

:
Click to expand...
Try this variation:

SELECT tblMeetingItem.MeetingItemNo, tblMeetingItem.MeetingNo,
tblMeetingItem.Description, tblMeetingItem.SeqNo,
tblMeetingItem.Time, tblMeetingItem.Responsible,
tblMeetingItem.Outcome, tblMeetingItem.Resolved, (SELECT
Count(*) FROM tblMeetingItem AS T
WHERE T.SeqNo<= tblMeetingItem.SeqNo
FROM tblMeetingItem
ORDER BY tblMeetingItem.SeqNo;

--

Ken Snell
<MS ACCESS MVP>


message
Click to expand...
Well done Ken! There was a missing bracket but it worked.
I have had to add on a small section to make it work.
Basically each meeting has many items each with a sequence
number. Hence meeting one might have sequence 1, 2, 3, 4
and meeting two 1, 2, 4, 7. Screws up the sequence numbers
if I look at them all (If there are 2 sequence number 1's
the number from the query is 2 for both of the 1's. Had to
apply a where clause to only look at items relating to the
meeting displayed on the screen.

Just for the record, the final working SQL is

SELECT tblMeetingItem.MeetingItemNo,
tblMeetingItem.MeetingNo, tblMeetingItem.Description,
tblMeetingItem.SeqNo, tblMeetingItem.Time,
tblMeetingItem.Responsible, tblMeetingItem.Outcome,
tblMeetingItem.Resolved, (SELECT Count(*) FROM
tblMeetingItem AS T WHERE T.MeetingNo =
[Forms]![frmMeetings]![MeetingNo] AND T.SeqNo<=
tblMeetingItem.SeqNo) AS ItemNo
FROM tblMeetingItem
WHERE
(((tblMeetingItem.MeetingNo)=[Forms]![frmMeetings]![MeetingNo]))
ORDER BY tblMeetingItem.SeqNo;

Thanks again Ken for your valuable assistance.


:
Click to expand...
Sorry about that -- it was pretty late last night when I
typed that....< g

Glad it is working now.

--

Ken Snell
<MS ACCESS MVP>

Submitted via EggHeadCafe
New Features Of Microsoft Outlook 2010
Click to expand...
Click to expand...
http://www.eggheadcafe.com/tutorial...8/new-features-of-microsoft-outlook-2010.aspx
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use In() Expression as Query criteria with guts from Function 1
Access Query problem 1
Bug? Using full identity expression in any arbitrary column works 4
Parameter Value in Crosstab Query 4
Access Macro Repeat Expression using value of a field in table 0
Creating a total query 4
Access #Error & Excel Pivot 0
Make table query: [Enter 1] AS Test 1

Top