Group By in an Access query

Using MS Access.

Ideally this whole thing would run under a single query but that's too complicated for me now, so currently I've broken it up and am dealing with 2 separate queries. What I'm looking for is a solution to query2.

A simplified version of the results of query1 is as follows. The first row are field names followed by data

State                     Location       Amount
Alabama              County             100
Alabama              City                   150
Alabama              Town                150
Alabama              City                   200
Arkansas             City                    500
Arkansas             City                    400
California            County              100
California            Town                 200
California            County              400
California            City                    300
California            Town                 600
New York            County              100
New York            County              500
New York            County              300
New York            City                    200
New York            Town                100
etc

Expected results:

State                 Location              Amount
Alabama         City or Town           500
Alabama         County                    100
Arkansas         City or Town          900
California        City or Town        1100
California        County                    500
New York        City or Town           300
New York        County                    900

So what I'm trying to do is a group by.  For a particular state, I want to group all of the county records into a single record and I want to group all of the City and Town records into a single record renamed as "City or Town".  My main problem is figuring out how to treat "city" records and "town" records as the same thing in the group by so that those types of records will be combined.   As you can see, in the expected results, the maximum number of records for a particular state would be 2.  1 record (if there are any) would be for the county record. The second record (if there are any), would be for the city and/or town record.
dbfromnewjerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You need to fold you values. As SQL, e .g.

SELECT  Iif(Q.State = "County", "County", "City or Town") AS State,
        Q.Location,
        SUM(Q.Amount) AS Amount
FROM    yourQuery Q
GROUP BY Iif(Q.State = "County", "County", "City or Town"),
        Q.Location;

Open in new window

Dale FyeOwner, Dev-Soln LLCCommented:
I think ste5an meant:
SELECT  Q.State,
       Iif(Q.Location = "County", "County", "City or Town") AS Location,
       SUM(Q.Amount) AS Amount
FROM    yourQuery Q
GROUP BY Q.State, Iif(Q.Location = "County", "County", "City or Town")

Open in new window


or another way to do this, which would give you more flexability and the ability to change the outcome without having to find every instance of:
iif(Q.Location="County", "County", "City or Town")

Open in new window

would be to use a translation table with something Like:

Location      Grouped_Location
County         County
City               City or Town
Town            City or Town

Then your query becomes:
SELECT  Q.State,
       T.Grouped_Location,
       SUM(Q.Amount) AS Amount
FROM    yourQuery Q
INNER JOIN tblTranslator as T ON Q.Location = T.Location
GROUP BY Q.State, T.Grouped_Location

Open in new window

[/code]
Gustav BrockCIOCommented:
Stefan is close, but the fields got mixed up:

SELECT
    Q.State,
    IIf(Q.Location = "County", "County", "City or Town") AS Location,
    SUM(Q.Amount) AS Amount
FROM
    YourQuery As Q
GROUP BY 
    Q.State,
    IIf(Q.Location = "County", "County", "City or Town")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
Gustav's works perfect. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.