Query in access 2000

[setyna]

Does any know How to create an Auto Number For Each Row of in Query..Currently the query has no unique values.

 

[DougP]

Try Count
turn on "Group by" by clicking the "E" epsilon button
then find count in there and choose a field.

DougP, MCP

 

[JonFer]

If you want to get an incremental number for each row in your query results you could create a field in your query that counts records from the same underlying table that have values in a field that are greater than the value for that field in the current row in your query. You said that there are no unique fields in the table so this would only work for a unique combination of fields (concatenate the values to get a unique value) which would take long to process if you have a lot of records.

I believe there is a FAQ on ranking which is one way get the number. There are problems, though, if you have Null values or non-unique values to rank.

 

[setyna]

The original table driving the query has unique key field...The challenge comes in to play in the group by..I am trying to give the end result query new unique values..For example the query returned 114 records, is there a way to use that count and populate the field in that same query? DougP replied with "clicking the "E" epsilon button" but I am not familiar with that experssion..

 

[JonFer]

If the new key doesn't have to be incremental, use Max(primary_key_field) in your group by query. That will still produce a unique value as long as your primary key only has a single field.

The group by query is activated by using a button on the toolbar. The icon is actually the capital sigma symbol (used in mathematics for sums).

 

[alr0]

Hi Setyna,

You can also activate the group by or totals row in a query by pulling down the view menu and clicking on totals. If you do not have that tool bar visible, you will never see the icon.

Hope this helps,

alr