Multiple SUM()-WHERE clauses

Arbec · June 26th, 2011, 11:01 AM

Hi All,

As a long-time SAS user (quite spoiled) I am having a problem getting Access SQL to do what I want. I am attempting to get county totals by age and ultimately age-************-race totals. The form I have gotten data in is one record for each unique combination of county (FIPS is the ID), age (18), ************ (2), and race (4) -- giving 144 records for each county. I can get the proper totals for one age category with the code:

Code:

SELECT PopDetail06.FIPS, Sum(PopDetail06.Pop) AS Pop75 INTO AllPop70
FROM PopDetail06
WHERE (((PopDetail06.AgeCat)=16))
GROUP BY PopDetail06.FIPS
ORDER BY PopDetail06.FIPS;

but all of the locations I can think of for the WHERE clause I can think of give a syntax error, since I need a separate WHERE value for each of the SUM() functions but the WHERE seems to have to follow the INTO and FROM statements.

How does one do this?

Thanks,

Arbec

HiTechCoach · June 26th, 2011, 02:15 PM

TIP: Use the query designer. It will will validate the SQL

It looks like you aer creating an append query. Is that coorect?

If yes, Try:

Code:

INTO AllPop70 (FIPS, Pop75 )
SELECT PopDetail06.FIPS, Sum(PopDetail06.Pop) AS Pop75 
FROM PopDetail06
WHERE (((PopDetail06.AgeCat)=16))
GROUP BY PopDetail06.FIPS
ORDER BY PopDetail06.FIPS;

Arbec · June 27th, 2011, 11:46 AM

Thanks for the response. I wasn't clear enough. I'm trying to create a new table with one record per county with 18 age-specific sums (looking like FIPS, age1, age2, age3 ... age18, with each ageN having a pop total for the M/F and race categories). To do this I need to logically do:

Code:

Sum(PopDetail06.Pop) AS Pop70
WHERE (((PopDetail06.AgeCat)=15))
Sum(PopDetail06.Pop) AS Pop75
WHERE (((PopDetail06.AgeCat)=16))
Sum(PopDetail06.Pop) AS Pop80
WHERE (((PopDetail06.AgeCat)=17))
/*etc. but it will not let me do this because it wants
      INTO AllPop70
      FROM PopDetail06
before the WHERE.

I've spent about six hours going through SQL Designer and SQL direct code window using three different books. All the books give is the single population code like what I gave and got to work. What I can't do is get one query to produce the single records for each county with all 18 population totals.

Since ultimately I'm going to be doing age, M/F, race specific populations for each of the 3100-odd counties, summing to BEA economic areas and then applying U.S. mortality rates to estimate the area's mortality if it experienced
national rates I can't even see how to finesse using Excel or somesuch.

Roger

gbianchi · June 27th, 2011, 12:32 PM

IsnÂ´t this solved with a group by clause?

Arbec · June 27th, 2011, 02:32 PM

Hi Gonzalo,

I'm afraid that the GROUP BY and SUM() do not do the job alone, you need to tell SQL what to put in each group, which is the purpose of the WHEN. Since I'm creating 18 variables in the target county record I need 18 WHEN statements, one for each variable. My problem is where to put it in the SQL -- from the unspecific error messages I'm getting SQL is expecting an INTO and a FROM after the first WHEN and I can't figure out where the other 17 WHENs go.

Roger

HiTechCoach · June 28th, 2011, 10:27 AM

Quote:

Originally Posted by Arbec

Hi Gonzalo,

I'm afraid that the GROUP BY and SUM() do not do the job alone, you need to tell SQL what to put in each group, which is the purpose of the WHEN. Since I'm creating 18 variables in the target county record I need 18 WHEN statements, one for each variable. My problem is where to put it in the SQL -- from the unspecific error messages I'm getting SQL is expecting an INTO and a FROM after the first WHEN and I can't figure out where the other 17 WHENs go.

Roger

Roger,

Assuming you want all the fields in the same row (record)

Here are some options:

1) use the Group By and use calculated fields for each group. This will return a single row.

2) Create a table with all the fields. Run a series of update queries where each query updates a single field file to build the data into a table.

3) Create a table with all the fields. Use VBA code to build the data use a recordset. As each record is read, update he count in the table.