Include ROW Count in Query???

[taimysho0]

Hello, im using access 2010. How do i edit my query so that it will display the row number in the column? I only have two columns: AMR AIS# and Count. I want the column "Count" to display the number of rows. By the way, the IDs derive from another query, so just simply selecting the "summation" button and choosing "count" will not work as it wont be accurate. Heres how i want it:





ID Count

000123 1
000124 2
000152 3

[taimysho0]

INSERT INTO dbo_List_Detail ( Account_ID )
SELECT qry_select_only_missingdata_or_unabletoconnect_by_ group.[AMR AIS #]
FROM qry_select_only_missingdata_or_unabletoconnect_by_ group;

[June7]

Calculating a sequential number in query requires subquery. Review: http://allenbrowne.com/ranking.html

Easier to use the RunningSum property of textbox in report. The ControlSource would be =1.

Why wouldn't the aggregate Count be accurate?

[taimysho0]

June, thansk for the help. The SQL i have above IS a sub query. Its a query querying another query. The reason why i didnt use the summation and select by count is because there is duplicate data in the query that i was based off of (but i used group by to group up all the duplicates). Can you help me write what i should put into the "Field" column of the Design View of my query to create my "Count" column?

Heres what i have so far:

Count: (Select Count([AMR AIS #]))

[June7]

Either way, need another query. Use the GROUP BY query as basis for another query to do Count or as source for the row number calc. The general syntax to return row number over all records (not by group) would be:

SELECT Query1.ID, (SELECT Count(ID) AS RN FROM Query1 As Dupe WHERE Dupe.ID<Query1.ID) AS RowNum FROM Query1;

If all you care about is the total and not details, not a row number, do the Count.

What do you plan to do with this query?

A report using Grouping & Sorting with aggregate calcs in header/footer sections would probably be easiest. This will allow display of detail records and summary data. The GROUP BY query would be the form's RecordSource.

[taimysho0]

june, thanks for the help. to answer your question, the results of this query will be used to append into a table. Therefore, i cannot use report as that is not the purpose of the query. I tried following your format but am getting an error message about missing a ( { or something somwhere. Can you help me figure out the problem? thanks! The name of my qry is qry_select_only_missingdata_or_unabletoconnect_by_ group and the name of the ID column is AMR AIS #


SELECT qry_select_only_missingdata_or_unabletoconnect_by_ group.[AMR AIS #], (SELECT Count(AMR AIS #) AS RowNum FROM qry_select_only_missingdata_or_unabletoconnect_by_ group As Dupe WHERE Dupe.[AMR AIS #]<qry_select_only_missingdata_or_unabletoconnect_by _group.[AMR AIS #]) AS Q1 FROM qry_select_only_missingdata_or_unabletoconnect_by_ group

[June7]

Missing []
SELECT Count([AMR AIS #])

BTW, advise no spaces, special characters or punctuation (underscore is exception) in names nor reserved words as names. If do, must enclose in []. Sometimes Access will try to provide [] but can't depend on. Better would be AMR_AIS_Num.

[taimysho0]

I see, i just left it like that before because that column was taken from a link table to an excel spreadsheet and thats how the column is formatted. Will take note of it in the future. Now im getting the message "you may have entered an operand without an operator" and highlights the "AS" right before the "Q1" in the statement below

Q1: (SELECT Count([AMR AIS #]) AS RowNum FROM qry_select_only_missingdata_or_unabletoconnect_by_ group As Dupe WHERE Dupe.[AMR AIS #]<qry_select_only_missingdata_or_unabletoconnect_ by _group.[AMR AIS #]) AS Q1 FROM qry_select_only_missingdata_or_unabletoconnect_by_ group

[taimysho0]

june, it is working now, i just had to rename my id (i used a format() function). Only thing is, it is displaying a "0" as the first row. is there a way to make it begin with "1"? Thanks so much

[June7]

Try + 1 and I changed the aliases a little:

SELECT qry_select_only_missingdata_or_unabletoconnect_by_ group.[AMR AIS #], (SELECT Count([AMR AIS #]) + 1 AS RN FROM qry_select_only_missingdata_or_unabletoconnect_by_ group As Dupe WHERE Dupe.[AMR AIS #]<qry_select_only_missingdata_or_unabletoconnect_ by _group.[AMR AIS #]) AS RowNum FROM qry_select_only_missingdata_or_unabletoconnect_by_ group;

[taimysho0]

Wow, you are a genius!, it worked. thanks so much!

[taimysho0]

june, after trying this query again, for some reason it is taking forever to load. it takes almost 5 minutes, and sometimes even freezes the database. When i remove the whole "count" function, it works fine. Is there a way to re work this to make it simpler for the database to process? if you have any ideas let me know. thanks!

[June7]

I don't know why it would have slowed down.

The RunningSum property in report is the simplest approach.

Anything else requires VBA. You could use VBA to populate a field in table with sequential value. Requires looping through an editable recordset and setting value of field which will update the table.

[taimysho0]

here is how my query is written right now, do you see any issues with it?

SELECT qry_select_only_missingdata_or_unabletoconnect_by_ group.AMR_AIS_Num, (SELECT Count(AMR_AIS_Num) + 1 AS RowNum FROM
qry_select_only_missingdata_or_unabletoconnect_by_ group As Dupe WHERE Dupe.AMR_AIS_Num<
qry_select_only_missingdata_or_unabletoconnect_by_ group.AMR_AIS_Num) AS [Count], Format([AMR_AIS_Num],"000000") AS AMR_AIS_Num2
FROM qry_select_only_missingdata_or_unabletoconnect_by_ group;

[June7]

Might try without the Format function. Instead apply format in textbox ControlSource expression on form or report.