Include ROW Count in Query???

[taimysho0]

june, i took out the format but its still extremely slow to process. im assuming its taking long because it has to create the subquery in the column. Would it be faster if i just created the sub query on my own and then create another query with the subquery+original query? This is the query that is currently being ran in the "Count" function of my original query:



Count: (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)


If i create a query that will do:
(SELECT Count(AMR_AIS_Num) + 1 AS RowNum FROM
qry_select_only_missingdata_or_unabletoconnect_by_ group As Dupe

and then a separate query that does:
WHERE Dupe.AMR_AIS_Num<
qry_select_only_missingdata_or_unabletoconnect_by_ group.AMR_AIS_Num)


and then joining the result of THAT query with my original query, do you think that will speed things up? Let me know, thanks!!

[June7]

I doubt it will be faster but try it.

The only alternative I can see is VBA code as mentioned in earlier post.

[taimysho0]

heres a picture of how i currently have it if you are confused...

[June7]

That all looks correct. That this query can run slow is not new to me and I don't know what could be done, if anything, to make it faster. I can't take credit for originality, I found the query syntax on web. I have never actually needed it.

[taimysho0]

i see, thanks june for all the help anyways

[taimysho0]

i tried doing an alternate way that would be faster. create a table with an autonumber and append my IDs that i want to be sequenced into it and then add the autonumber count values from that table to the my query. The only problem is, the autonumber count seems to keep going after new IDs has been appended to it. How do i get it to restart at 1 when i run the append query? thanks!

[June7]

Something else I have never done.

Maybe this will help http://support.microsoft.com/kb/209696