Line Numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that pulling orders by container. However, I need to assign a
line number to each order in the container.

Example
ToteNumber OrderNumber LineNum
25639 CN48963 1
25639 CN48536 2
25693 CN45262 1
25794 CN45360 1
25794 CN45868 2

I tried the following code:
Expr1: (SELECT tblTotes.ToteNumber, (SELECT Count(*) FROM tblTotes AS T
WHERE T.ToteNumber =tblTotes.ToteNumber And T.OrderNumber
<=tblTotes.OrderNumber
ORDER BY T.ToteNumber) as LineNum)

When I try to run the query, I get the error message that I am missing
something in the select statement.

Any help would be appreciated.
 
An expression cannot get TWO elements:


EXPR1: SELECT expression1, expression2


returns 2 expression into ONE field (slot, cell), EXPR1.


Furthermore, it cannot neither return more than ONE row. You cannot pump
1000 lines, and a simple

SELECT field FROM myTable

is likely to return more than just one ROW.

SELECT MAX(field) FROM myTable WHERE someCondition

always return one field, one row, even is "someCondition" is found true on
more than one record.



And finally, a SELECT statement has generally a FROM clause too, and here,
your outer most SELECT does not.




Vanderghast, Access MVP
 
Thanks for your response. I guess I will need to come up with another way of
creating in field in the query that will return line numbers.
 
The following is the response:

"The SELECT statement includes a reserve word or an argument name missed
spelled or missing, or the punctuation is incorrect."

I have tried rewrite it, the new field returns "1" for every record returned
in the query.

I keep thinking that there has to be a easy solution that I am missing.

I would appreciate any assistance you can give me.
 
You can use a sub-query to get it, indeed.


something like:


Expr1: (SELECT COUNT(*)
FROM tblTotes AS T
WHERE T.ToteNumber = tblTotes.ToteNumber
And T.OrderNumber <= tblTotes.OrderNumber
)



assuming you didn't aliased tblTotes in the main query.

You can observe that the aggregate COUNT( ), and no explicit GROUP BY, give
you the assurance you will get just one single row, from that sub-query.




Vanderghast, Access MVP
 
It works now. Thanks for your help.

Michel Walsh said:
You can use a sub-query to get it, indeed.


something like:


Expr1: (SELECT COUNT(*)
FROM tblTotes AS T
WHERE T.ToteNumber = tblTotes.ToteNumber
And T.OrderNumber <= tblTotes.OrderNumber
)



assuming you didn't aliased tblTotes in the main query.

You can observe that the aggregate COUNT( ), and no explicit GROUP BY, give
you the assurance you will get just one single row, from that sub-query.




Vanderghast, Access MVP
Click to expand...
 
Back
Top