Grouped Autonumber or Count

[derekstewart]

I have a table that groups data by city. What I would like to do is grab only the first 25 records per city. Is there are way to have autonumber start over when the city changes or have a count function that starts at one and changes with the city. That way I can query the autonumber or count field between 1 and 25, group by city and have the first 25 records by city. Any thoughts? Thanks. Derek

 

[derekstewart]

Can I do this with TOP instead?

 

[r937]

no, you cannot make the autonumber "start over" for each city (you can in mysql, but not in access)

but yes, you can do this with TOP in a subquery

however, you need to have some other column to sort by, otherwise TOP doesn't make sense

SELECT city
     , foo
     , bar
  FROM citydata as ZZ
 WHERE foo in
       ( select top 25 foo
           from citydata
          where city = ZZ.city
         order 
             by bar desc )

r937.com | rudy.ca

 

[derekstewart]

I have a ton of additional columns, I tried to use the code but kept getting errors with the "as zz" part. When I do this it changes the table I'm selecting from to ZZ, and the variables after my select statement can't be found because they are .citydata not .zz.

I am using this to pull 25 lines by city by a random number. I took the table, appended a random autonumber, and am now trying to take the TOP 25 to pull a random sample of 25. Does this make sense? So really I want it to Group by city and order by "random" to pull the top 25 random records. Kind of a poor man's random number generator that actually works well, I just can't get it to pull the 25 by hospital?

 

[r937]

I have a ton of additional columns, I tried to use the code but kept getting errors with the "as zz" part.

i showed you my query, how about you show me yours

r937.com | rudy.ca

 

[derekstewart]

Ok. You asked for it. ProperName is city name and random is the 10 digit random autonumber that I generated in an earlier step. Here's the process: I get the raw data, append it to a table with an random autonumber and then run this qry. It actually works but I've never got it to finish. I have 23 cities so would expect 460 rows to be returned. It takes about 30 minutes to get 280 records. There are only 35k records total. I am running Access 2003 on a brand new huge system with 2 GB ram and 3.2 ghz processor. Can you think of a way to streamline the process so it stops getting bogged down. Thanks. Derek

SELECT A.ProperName, A.Random, A.FACILITY_ID, A.FACILITY_VENDOR_NAME, A.PURCHASE_DATE, A.PO_NUMBER, A.MMIS_ITEM_NUMBER, A.UOM, A.ANSI_UOM, A.QUANTITY, A.QUANTITY_TO_EA, A.TOTAL_SPEND, A.COST, A.CONVERSION_FACTOR, A.COST_TO_EA, A.VENDOR_CATALOG_NO_FIXED, A.VENDOR_CATALOG_NO, A.FACILITY_ITEM_DESCRIPTION, A.VENDOR_MASTER, A.VENDOR_DIVISION, A.MANUFACTURER_ITEM_ID, A.MANUFACTURER_MASTER, A.MANUFACTURER_DIVISION, A.MANUFACTURER_CATALOG_NO, A.CLASSIFICATION_DESCRIPTION, A.CONTRACT_NUMBER, A.CONTRACT_START, A.CONTRACT_END, A.PRICE_USED, A.CONTRACT_PRICE, A.CONTRACT_PRICE_TO_EA, A.CONTRACT_DESC, A.PRODUCT_DESCRIPTION, A.CURRENT_CONTRACT_PRICE, A.CURRENT_CONTRACT_PRICE_TO_EA, A.CURRENT_CONTRACT_NUMBER, A.CURRENT_CONTRACT_NUMBER1, A.DEPARTMENT_CODE, A.DEPARTMENT_NAME, A.COST_CENTER_CODE, A.COST_CENTER_NAME
FROM randomtable AS A
WHERE a.random in (select top 20 random
From randomtable b
Where b.propername = a.propername
Order by b.random desc);

 

[PHV]

Have you indexed the propername field in randomtable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

the 10 digit random number that you generated in an earlier step?

may i ask how? just curious

if it is indeed random, then you don't need the ORDER BY in the subquery



by the way, your query looks okay

try the index as PHV suggested

r937.com | rudy.ca

 

[derekstewart]

The 10 digit random I generated using the Autonumber field. I took the raw data, and added a autonumber field to the table. I changed it from standard incremental to random, and it gave me 10 digit positive and negative random numbers. Then I just sorted them ascending and grab the first 20. That's about as random as it gets i guess. I did index the fields and the query actually completed. Still took about 5 minutes to run though. It really must be more complicated than it looks. Thanks a lot for the help. Derek