adding column for numbers in query design

  • 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
 
Giz said:
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
Click to expand...

Why not wait until the query is exported to Excel & use Excel's
functions to insert a "row-count" column? Easier than the query solution.
 
The reason I did not export to Excel first is because I need to my count to
be based on a particular record id, in this case "siteiid", and many rows may
have the same ID. Therefore this is not really a row count. For example, my
table may look like this

siteiid musym usiteid
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

There are 24 rows in this example, but only 5 different, unique "siteiid"
designations. So what I need is new column that displays the result of a
count of those ID's. So the new column will look something like this:

COUNT siteiid musym usiteid
1 450 38E 03MT637052032
1 450 38E 03MT637052032
1 450 38E 03MT637052032
1 450 38E 03MT637052032
1 450 38E 03MT637052032
2 451 38E 03MT637052033
2 451 38E 03MT637052033
2 451 38E 03MT637052033
2 451 38E 03MT637052033
2 451 38E 03MT637052033
3 604 38E 03MT637061023
3 604 38E 03MT637061023
3 604 38E 03MT637061023
3 604 38E 03MT637061023
3 604 38E 03MT637061023
4 610 38E 03MT637061040
4 610 38E 03MT637061040
4 610 38E 03MT637061040
4 610 38E 03MT637061040
4 610 38E 03MT637061040
5 611 38E 03MT637061042
5 611 38E 03MT637061042
5 611 38E 03MT637061042
5 611 38E 03MT637061042

So now I have a ranking for all unique, siteiid labels regardless of how
many times each one is duplicated in the table.
 
Back
Top