Is this possible, in a query? (Access 2010)

[dawnd3]

So, I have 3 fields, Zip Code, State, and Age. I want to show the top 1/3 records for EACH State based on age. In other words, if there are 99 CA records, I want the results to show the top 33 oldest according to the age field. I know how to set the properties of the query to show the top 1/3 records of all, but not how to do it for each state. Is this possible?

Dawn

 

[Golom]

I'm not entirely clear about your requirement but try something like

SELECT [Zip Code], [State], Age 

FROM myTable As M

WHERE Age IN (Select TOP 33 PERCENT Age 
              FROM myTable As X
              WHERE X.[State] = M.[State]
              ORDER BY X.Age DESC)

ORDER By [State], Age DESC

The point of confusion is the meaning of AGE. Suppose you had

   [b]AGE[/b]
    9
    9
    9
    9
    7
    8
    9
    9
    9

Sorted in DESC order seven '9's would appear first but in selecting only 3 (i.e. 33%) the other four will not appear and I'm not at all sure which ones you will get.

 

[Golom]

Oh ... and the other problem, if you have duplicates like that is that the query will return ALL of them ... not just the 33 percent.

 

[dawnd3]

I want 33% of the records, regardless of the values. So if there are 9 records in CA, it would show the 3 records with the 3 oldest ages.
45
42
42

So, it's not looking at the ages, except to sort. The 33% is saying I want to see 1/3rd of the records. So even if the ages were dupes, it would still cut off at the top 1/3rd. Does that make any sense?

Dawn

 

[dhookom]

dawnd3,
I'm not sure you understand the TOP 33% thing. If there are duplicates at the 33rd percent, you may get more values returned. If you don't want this, you have to provide another field value to break the ties.

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

So there isn't a way to count the records and just give me a 1/3rd of the records based on the sort I choose?

Dawn

 

[dhookom]

Did you try Golom's suggestion?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

If you're willing to try some VBA then we can do a bit better

Dim S As Recordset
Dim A As Recordset
Dim db As Database

Set db = CurrentDB()
Set S = db.OpenRecordset ( "Select DISTINCT State From myTable" )

Do Until S.Eof
    SQL = SQL & "SELECT TOP 33 PERCENT [Zip Code], [State], Age " & _
                "FROM myTable WHERE State = '" & S.State & "' " & _
                "ORDER BY Age DESC UNION "
    S.MoveNext
Loop
SQL = Left$(SQL, Len(SQL)-7) & "ORDER BY State, Age DESC"

Set A = db.OpenRecordset (SQL)

You now have the oldest 1/3 of the records for each state in the 'A' Recordset.

 

[Golom]

And of course I have at least one typo

SQL = Left$(SQL, Len(SQL)-[red][b]6[/b][/red]) & "ORDER BY State, Age DESC"

 

[dawnd3]

Not yet Duane, will try it today. I also need to post what worked on that last question you helped out with. I am a little behind on things. Seems like I always have the oddest requests, doesn't it? LOL All from the same client.

Golom, thanks for your help. I will follow up in a bit with how it worked.

Dawn

 

[dawnd3]

Ok, finally got this working but it takes FOREVER. Did I do something wrong or should it take a long time. (at least 10 minutes to run on normally fast machine, windows 7 and Access 2010.)

Here is my SQL:

SELECT X.MedianAgeYears, X.TotalPopulation, X.Zip, X.State
FROM ZipCodeData AS X
WHERE (((X.TotalPopulation)>=10000))
AND X.MedianAgeYears IN (Select Top 33 Percent Y.MedianAgeYears 
FROM ZipCodeData AS Y
WHERE X.State = Y.State
ORDER BY Y.MedianAgeYears DESC)
ORDER BY X.State, X.MedianAgeYears DESC;

Please note that I do need to eventually link to second table and pull data from that for this query, but for now, was just trying to get it to work. It works perfectly, but slow. I haven't tried the VBA code yet because honestly, I am not sure how I call it once I create the function. Do I call it from the query design?

 

[dhookom]

Which fields are indexed? MedianAgeYears and State need to be.
I would also guess the >=10000 might not be a restriction in the subquery. For instance a zip of less than 10000 might be returned by the subquery but excluded from the final results. Make sure you confirm the results.



Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

Thanks Duane, I will check both of those.

 

[dawnd3]

Oh, and I just found an issue with it. Some of the states it listed all records not just 1/3rd. Uggg!

 

[Golom]

Coordinated sub-queries do tend to take a long time because the sub query runs for every record in the main query. As Duane and I said, duplicate values for MedianAgeYears can cause it to return more records than you want. The VBA UNION approach should not have that problem.

 

[dhookom]

I believe you need to move some criteria to the subquery. Also, what is the primary key field in ZipCodeData? Is it the zip field?

SELECT X.MedianAgeYears, X.TotalPopulation, X.Zip, X.State
FROM ZipCodeData AS X
WHERE X.Zip IN 
(Select Top 33 Percent Y.Zip 
FROM ZipCodeData AS Y
WHERE X.State = Y.State AND Y.TotalPopulation>=10000
ORDER BY Y.MedianAgeYears DESC)
ORDER BY X.State, X.MedianAgeYears DESC;

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

I fixed the issue with the query by removing the part about >10,000 and putting that in it's own query. Then basing this SQL query on the results of that pre-query. Make sense? It is still slow, but it works. Yes zipcode is the primary key. I still haven't tried the VBA approach. Thanks you guys.I really appreciate the hand holding!

Dawn

 

[dhookom]

dawnd3,
You never answered my question "Which fields are indexed?"

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

Oh, sorry, I did as you suggested and indexed MedianAgeYears and State.

 

[dawnd3]

Just wanted to get back to you guys and say that all is working well. It does take a while, but I learned that it is a one-time query and so speeding things up isn't a big deal. THANK YOU GUYS so much.