adding column with numbers in query

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

Guest

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
 
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
 
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
 
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.
 
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/ASIN/0201433362/qid=1107549061/702-3583917-4704004



Hoping it may help,
Vanderghast, Access MVP
 
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.
 
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
 
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
 
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)
 
Hi, thanx for responding. Hopefully you are still around, I was out for
weekend.
Here is the SQL for my query:

SELECT site.siteiid, pedon.musym, site.usiteid, phorizon.hzname
FROM (site INNER JOIN pedon ON site.siteiid = pedon.siteiidref) LEFT JOIN
phorizon ON site.siteiid = phorizon.peiidref
WHERE (((pedon.musym)="38E"));

Now, as I have stated there are something like 900 different "siteiid"
records in the original "site" table. This query will only pull those
"siteiid" records that are found in "musym" record "38E". It is those I need
to count, and subsequently add the results of in a new column. Thanks
 
So, You probably need something like

(SELECT COUNT(*) FROM SITE AS S INNER JOIN Pedon ON S.SiteIID =
Pedon.SiteIidRef WHERE S.SiteIID <= Site.SiteIID AND Pedon.MuSym = "38E") as CountThem
 
That doesn't quite do it either. What happens when I run this is that it
ranks ALL 900 plus records containing unique "siteiid" values in the site
table, and not just the subset of records queried based on setting a criteria
for "musym" of 38E. So therefore the result of my query looks like this
(also notice replication of unique siteiid values, that is OK):

rank musym siteiid
450 38E 03MT637052032
450 38E 03MT637052032
450 38E 03MT637052032
450 38E 03MT637052032
450 38E 03MT637052032
451 38E 03MT637052033
451 38E 03MT637052033
451 38E 03MT637052033
451 38E 03MT637052033
451 38E 03MT637052033
604 38E 03MT637061023
604 38E 03MT637061023
604 38E 03MT637061023
604 38E 03MT637061023
604 38E 03MT637061023
610 38E 03MT637061040
610 38E 03MT637061040
610 38E 03MT637061040
610 38E 03MT637061040
610 38E 03MT637061040
611 38E 03MT637061042
611 38E 03MT637061042
611 38E 03MT637061042
611 38E 03MT637061042

instead of like this, which is desired:

rank musym siteiid
1 38E 03MT637052032
1 38E 03MT637052032
1 38E 03MT637052032
1 38E 03MT637052032
1 38E 03MT637052032
2 38E 03MT637052033
2 38E 03MT637052033
2 38E 03MT637052033
2 38E 03MT637052033
2 38E 03MT637052033
3 38E 03MT637061023
3 38E 03MT637061023
3 38E 03MT637061023
3 38E 03MT637061023
3 38E 03MT637061023
4 38E 03MT637061040
4 38E 03MT637061040
4 38E 03MT637061040
4 38E 03MT637061040
4 38E 03MT637061040
5 38E 03MT637061042
5 38E 03MT637061042
5 38E 03MT637061042
5 38E 03MT637061042

What I need to do is pull the subset first, then rank based on siteiid. I
have tried multiple combinations to do such a thing, including trying to use
the query as an object itself. Don't know if that is legitimate or not to do.
It didn't work. Thanks again.
 
Hi,

You may rank the siteiid in another query:

SELECT siteiid
FROM site
WHERE musym='38E'


say it is query Q1.


SELECT a.siteiid, COUNT(*) As rank
FROM Q1 as a INNER JOIN Q1 As b
ON a.siteiid <= b.siteiid
GROUP BY a.siteiid

save it as Q2.


In a final query, make an inner join from your working table and Q2 to get
the rank.



Hoping it may help,
Vanderghast, Access MVP
 
That worked. Although I am not quite sure how Q2's syntax operates. Looks
like Q1 is being joined to itself and I can't quite make logical sense of it.
Thank you!
 
Hi,


If siteiid values are 1, 2, 3 and 5 then the join, before the aggregate,
will supply:


a.siteiid, b.siteiid
1 1
1 2
1 3
1 5
2 2
2 3
2 5
3 3
3 5
5 5



since b.sideiid >= a.siteiid


then, if we count on each a.siteiid:


a.siteiid, COUNT(*)
1 4
2 3
3 2
5 1



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top