asked on

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.

ste5an

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 Fye

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]

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jenkins

ASKER

Gustav's works perfect. Thank you.