adding column with numbers in query

Hi, I am relatively new to Access and was hoping someone could help me out. I
have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a "symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before export I
would like to add a column to the query (in query design view, I think) that
will contain a number for every row in the query, listed sequentially. In
other words if the query results in 20 rows of information, I would like a
column that lists 1 to 20. Is there any way to do this in the query design
view, either with an expression or otherwise?? Any help would be appreciated

Hi,

You can rank your data. Assuming you have a field (or a list of fields) that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp =
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help me
out. I
have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before export I
would like to add a column to the query (in query design view, I think)
that
will contain a number for every row in the query, listed sequentially. In
other words if the query results in 20 rows of information, I would like a
column that lists 1 to 20. Is there any way to do this in the query design
view, either with an expression or otherwise?? Any help would be
appreciated

Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in the
"Field" cell in the new column in the query design view, reads like this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of fields) that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp =
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help me
out. I
have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before export I
would like to add a column to the query (in query design view, I think)
that
will contain a number for every row in the query, listed sequentially. In
other words if the query results in 20 rows of information, I would like a
column that lists 1 to 20. Is there any way to do this in the query design
view, either with an expression or otherwise?? Any help would be
appreciated

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news
Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp =
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I think)
that
will contain a number for every row in the query, listed sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and expressions,
the original code (the first suggestion) appears to require a table "a" in
the initial part of the statement "SELECT a.*(SELECT....". Is this the case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news
Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp =
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I think)
that
will contain a number for every row in the query, listed sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

HI,


You are right, missing the table name:

Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid)


the " AS a " and the " AS b " are meant to be "alias" for a "reference"
to the table. Indeed, we need to reference the table twice, once to get the
final result, once to count how many records are in the table with the
supplied condition. In the last case, one reference is " As b", the other
reference, by default, is the table name itself: in the query designer, if
you have the "properties sheet" visible, click on the table (on the top
half portion, the "graphical" portion of the designer) and the properties
sheet should said "Alias" in the first line (there is only two properties,
for a table). As you see, the table name is automatically used as "alias".
If you change it for " a ", then, type:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid)




because "a" is now the name of one of the "reference" .


Since the ( ) around the SELECT acts like a "scope", and since " AS b" is
defined within this scope, you cannot refer to b outside that scope. On the
other hand, " as a " is define more externally (as you can see in the main
FROM clause, in SQL view) and thus you can referred to that reference
anywhere within that scope, including in the innermost scope ( SELECT.... )


For references on SQL? here, sure, and some books, like
http://www.amazon.ca/exec/obidos/ASI...583917-4704004



Hoping it may help,
Vanderghast, Access MVP



"Giz" wrote in message
...
Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and
expressions,
the original code (the first suggestion) appears to require a table "a"
in
the initial part of the statement "SELECT a.*(SELECT....". Is this the
case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT,
COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather
than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news
Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's FROM clause. Revise
the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like
this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of
fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp
=
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help
me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the
accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I
think)
that
will contain a number for every row in the query, listed
sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

Ah it is getting close. This time unique siteiid values in the query output
corresponded to a unique value in the created column. However, it looks like
it ranked all 900+ rows that contained unique siteiid values in the original
table BEFORE, and used those numbers in the new query column. I only want to
rank the subset created by my query based on the 20 unique siteiid values in
the query. So instead of succesive numbers from row 1 to row 2 to row 3, etc.
in my query output that are 240, 322, 435, etc. , I need the 20 rows to
number 1 to 20 (i.e. row 1 to row 2 to row 3 etc. are numbered 1,2,3 etc.) I
hope this makes sense.

"Michel Walsh" wrote:

HI,


You are right, missing the table name:

Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid)


the " AS a " and the " AS b " are meant to be "alias" for a "reference"
to the table. Indeed, we need to reference the table twice, once to get the
final result, once to count how many records are in the table with the
supplied condition. In the last case, one reference is " As b", the other
reference, by default, is the table name itself: in the query designer, if
you have the "properties sheet" visible, click on the table (on the top
half portion, the "graphical" portion of the designer) and the properties
sheet should said "Alias" in the first line (there is only two properties,
for a table). As you see, the table name is automatically used as "alias".
If you change it for " a ", then, type:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid)




because "a" is now the name of one of the "reference" .


Since the ( ) around the SELECT acts like a "scope", and since " AS b" is
defined within this scope, you cannot refer to b outside that scope. On the
other hand, " as a " is define more externally (as you can see in the main
FROM clause, in SQL view) and thus you can referred to that reference
anywhere within that scope, including in the innermost scope ( SELECT.... )


For references on SQL? here, sure, and some books, like
http://www.amazon.ca/exec/obidos/ASI...583917-4704004



Hoping it may help,
Vanderghast, Access MVP



"Giz" wrote in message
...
Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and
expressions,
the original code (the first suggestion) appears to require a table "a"
in
the initial part of the statement "SELECT a.*(SELECT....". Is this the
case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT,
COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather
than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's FROM clause. Revise
the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like
this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of
fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp
=
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help
me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the
accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I
think)
that
will contain a number for every row in the query, listed
sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

Ah it is getting closer. That time it populated unique values in the created
column for each unique siteiid value in the query. However, it looked like it
created the values BEFORE

"Michel Walsh" wrote:

HI,


You are right, missing the table name:

Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid)


the " AS a " and the " AS b " are meant to be "alias" for a "reference"
to the table. Indeed, we need to reference the table twice, once to get the
final result, once to count how many records are in the table with the
supplied condition. In the last case, one reference is " As b", the other
reference, by default, is the table name itself: in the query designer, if
you have the "properties sheet" visible, click on the table (on the top
half portion, the "graphical" portion of the designer) and the properties
sheet should said "Alias" in the first line (there is only two properties,
for a table). As you see, the table name is automatically used as "alias".
If you change it for " a ", then, type:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid)




because "a" is now the name of one of the "reference" .


Since the ( ) around the SELECT acts like a "scope", and since " AS b" is
defined within this scope, you cannot refer to b outside that scope. On the
other hand, " as a " is define more externally (as you can see in the main
FROM clause, in SQL view) and thus you can referred to that reference
anywhere within that scope, including in the innermost scope ( SELECT.... )


For references on SQL? here, sure, and some books, like
http://www.amazon.ca/exec/obidos/ASI...583917-4704004



Hoping it may help,
Vanderghast, Access MVP



"Giz" wrote in message
...
Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and
expressions,
the original code (the first suggestion) appears to require a table "a"
in
the initial part of the statement "SELECT a.*(SELECT....". Is this the
case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT,
COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather
than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's FROM clause. Revise
the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like
this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of
fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp
=
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help
me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the
accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I
think)
that
will contain a number for every row in the query, listed
sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

It is getting closer. That time the query assigned unique numerical values
into the new column for each unique siteiid value. However, it looks like it
assigned the values to every row, or unique siteiid value, in the original
table (over 900!!). Therefore, the new column (expr1) has values that read
124, 158, 405, 514 for the first 4 unique siteiid values in the queried
subset, rather than 1, 2, 3, 4. I imagine there is a way to do this within
the function used so far, but am at a loss. Access help menu is weak, by the
way.

Thanks again for the help

Michel Walsh" wrote:

HI,


You are right, missing the table name:

Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid)


the " AS a " and the " AS b " are meant to be "alias" for a "reference"
to the table. Indeed, we need to reference the table twice, once to get the
final result, once to count how many records are in the table with the
supplied condition. In the last case, one reference is " As b", the other
reference, by default, is the table name itself: in the query designer, if
you have the "properties sheet" visible, click on the table (on the top
half portion, the "graphical" portion of the designer) and the properties
sheet should said "Alias" in the first line (there is only two properties,
for a table). As you see, the table name is automatically used as "alias".
If you change it for " a ", then, type:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid)




because "a" is now the name of one of the "reference" .


Since the ( ) around the SELECT acts like a "scope", and since " AS b" is
defined within this scope, you cannot refer to b outside that scope. On the
other hand, " as a " is define more externally (as you can see in the main
FROM clause, in SQL view) and thus you can referred to that reference
anywhere within that scope, including in the innermost scope ( SELECT.... )


For references on SQL? here, sure, and some books, like
http://www.amazon.ca/exec/obidos/ASI...583917-4704004



Hoping it may help,
Vanderghast, Access MVP



"Giz" wrote in message
...
Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and
expressions,
the original code (the first suggestion) appears to require a table "a"
in
the initial part of the statement "SELECT a.*(SELECT....". Is this the
case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT,
COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather
than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's FROM clause. Revise
the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like
this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of
fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp
=
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help
me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the
accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I
think)
that
will contain a number for every row in the query, listed
sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated

Pardon me for jumping in, but I think Michel might be taking a break.

You need to limit the subquery to the same set of records that are in the main
query.

So, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Basically, you need to add the same criteria to
SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid
as are in your main query.
(SELECT COUNT(*) FROM site AS b
WHERE b.siteiid = site.siteiid AND b.SomeOtherField = SomeValue)


Giz wrote:

It is getting closer. That time the query assigned unique numerical values
into the new column for each unique siteiid value. However, it looks like it
assigned the values to every row, or unique siteiid value, in the original
table (over 900!!). Therefore, the new column (expr1) has values that read
124, 158, 405, 514 for the first 4 unique siteiid values in the queried
subset, rather than 1, 2, 3, 4. I imagine there is a way to do this within
the function used so far, but am at a loss. Access help menu is weak, by the
way.

Thanks again for the help

Michel Walsh" wrote:

HI,


You are right, missing the table name:

Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid)


the " AS a " and the " AS b " are meant to be "alias" for a "reference"
to the table. Indeed, we need to reference the table twice, once to get the
final result, once to count how many records are in the table with the
supplied condition. In the last case, one reference is " As b", the other
reference, by default, is the table name itself: in the query designer, if
you have the "properties sheet" visible, click on the table (on the top
half portion, the "graphical" portion of the designer) and the properties
sheet should said "Alias" in the first line (there is only two properties,
for a table). As you see, the table name is automatically used as "alias".
If you change it for " a ", then, type:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid)




because "a" is now the name of one of the "reference" .


Since the ( ) around the SELECT acts like a "scope", and since " AS b" is
defined within this scope, you cannot refer to b outside that scope. On the
other hand, " as a " is define more externally (as you can see in the main
FROM clause, in SQL view) and thus you can referred to that reference
anywhere within that scope, including in the innermost scope ( SELECT.... )


For references on SQL? here, sure, and some books, like
http://www.amazon.ca/exec/obidos/ASI...583917-4704004



Hoping it may help,
Vanderghast, Access MVP



"Giz" wrote in message
...
Hi,

That doesn't really work either. A new column is created in the query, but
with just one, identical value populated in each row in the column, that
value being the count of rows in the original "site" table that is being
queried.

Conversely, while I am admittedly a novice at query design and
expressions,
the original code (the first suggestion) appears to require a table "a"
in
the initial part of the statement "SELECT a.*(SELECT....". Is this the
case,
or am I missing something?

Is there any method for getting help on functions (i.e. RANK, SELECT,
COUNT,
etc.) and their required arguements, syntax, etc.??? I have tried Access
help but can't seem to get anywhere. This would help my situation a lot.

thanx again for any help.




"Michel Walsh" wrote:

Hi,

Should preferably be typed in SQL view.

If you wish to type it from the query designer, that could be something
like:


Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid)



and, in the line Sort, ask that expression to be ordered. You can change
Expr1: by Rank: to get, in the result, the field name Rank, rather
than
the field name Expr1.



Hoping it may help,
Vanderghast, Access MVP


"Giz" wrote in message
news Hi Michel,

I attempted to do this, and the expression begins to run, but then
interupted with the following error message in a dialog box;

"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's FROM clause. Revise
the
SELECT statement of the subquery to request only one field."

I tried to locate in the help menu any assistance on the syntax and
arguement for the FROM function, with little success. My expression, in
the
"Field" cell in the new column in the query design view, reads like
this;

Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid =
a.siteiid) as Rank FROM site As a ORDER BY a.siteiid)

Help!!
Giz


"Michel Walsh" wrote:

Hi,

You can rank your data. Assuming you have a field (or a list of
fields)
that
uniquely define who is first, who is second, etc, we can compute that
"rank". If DateTimeStamp is that field, as example, then

SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp
=
a.DateTimeStamp) as Rank
FROM myTable As a
ORDER BY a.DateTimeStamp



That assumes you do not have duplicated values.


Hoping it may help,
Vanderghast, Access MVP




"Giz" wrote in message
...
Hi, I am relatively new to Access and was hoping someone could help
me
out. I
have created a query that accumulates various columns of information
from
different tables. In this query the user selects, as criteria for a
"symbol"
field, a symbol of interest, for subsequent export of the
accumulated
information for that specific symbol into excel. However, before
export I
would like to add a column to the query (in query design view, I
think)
that
will contain a number for every row in the query, listed
sequentially.
In
other words if the query results in 20 rows of information, I would
like a
column that lists 1 to 20. Is there any way to do this in the query
design
view, either with an expression or otherwise?? Any help would be
appreciated