you tried to exe query that did not include specified expr "" as part of aggregate Fn

[davefwelch]

you tried to exe query that did not include specified expr "" as part of aggregate Fn

Here is the SQL for the query I'm trying to run:

SELECT Sum(IIf(Survey![Q-3F]="-1",Survey![Q-1],0))/Sum(Survey![Q-1]) AS expr1, Sample.NAICS
FROM Survey INNER JOIN Sample ON Survey.SampleID = Sample.SampleID;

The error message is:
You tried to execute a query that does not include the specified expression 'NAICS' as part of an aggregate function.

I'm making a lot of work for myself here to save some later. Actually, I want to make this easy to minimize the chance for a mistake later. Sample is a table with nearly 2000 businesses surveyed, as well as characteristics such as industry, size, and location. Survey is a table with over 100 fields from a survey of those businesses about the fringe benefits they offer to employees. Many of the fields are Yes/No, hence the "-1" test for a true answer in Q-3F. I've figured out how to calculate percentages of employees (Q-1 is the number of full-time employees) that are offered each benefit for every business who responded. Now, I want to transfer that to queries that will break the percentages down based on characteristics of the companies where the employees work. For example, I want to figure out the percent of employees offered paid holidays at businesses in the health care industry (In the SQL above, NAICS represents the industry). Or, the percent of employees offered paid holidays at businesses with over 250 employees. Or, the third and final characteristic, I want the percent of employees offered paid holidays at businesses in a certain county.

If possible, I would rather not have to create separate domains by creating a select query for each industry, size, and area.

 

[davefwelch]

normalization

It sounds like normalization might help me a lot! The idea is rather than have each record be a business, each record would be a survey question? There are some fields in the table that are not survey questions, like contact name, phone number, etc. These fields would also be normalized? Also, I have a Sample table linked to the Survey table that has business characteristics. How does table linking work with normalization? Can you explain the whole normalization process to me some?

I've just read the following page, but still need some help?
http://www.datamodel.org/NormalizationRules.html

After reading further, I've realized that what you mean is to create a table for each survey question, right?

 

[davefwelch]

Thanks for the help Pat!
I've started the Append Queries. That seems to make sense, and I think I see how this will help me generate my results. Most of the questions are true/false. I don't understand why the true value is -1, but that's cool. Some are not true/false, but are still expected to be numbers, so I'll set the field type to a number (probably double not integer as some answers allow decimals). I guess as long as I take into account what the data type will be, it will be ok that some of them are true/false (-1/0) and others are numbers.

I guess if I have any more questions, I'll get back to you. Thanks so much!

btw, my grandfather played bridge here in Maine for many, many years from the 60's thru the 90's. being from CT, perhaps you know/heard of him. Dale Welch. He was pretty darn good. passed away a few years ago, BEFORE the Red Sox won the World Series

 

[davefwelch]

now trying to calc % with varying denominators

I've completed the normalization that Pat suggested, and boy did it make things simple!! I'm nearly done with all the queries I need to summarize the results of this survey. I am stuck on one, though. Below is the SQL for the queries I've been using to calculate percentage of Yes responses for different dimensions of stratification:

SELECT Universe.Size, Answers.qID, (Sum([Answers].[Answer])/Count([Answers].[Answer]))*-1 AS [Percent]
FROM ((Survey INNER JOIN Sample ON Survey.SampleID = Sample.SampleID) INNER JOIN Universe ON (Sample.RNUM = Universe.RNUM) AND (Sample.UNUM = Universe.UNUM)) INNER JOIN (tblQuestion INNER JOIN Answers ON tblQuestion.qID = Answers.qID) ON Survey.SampleID = Answers.SampleID
GROUP BY Universe.Size, Answers.qID, tblQuestion.Order, tblQuestion.Dependent, tblQuestion.Type
HAVING (((tblQuestion.Dependent)=No) AND ((tblQuestion.Type)="B"))
ORDER BY Universe.Size, tblQuestion.Order;

As well as:

SELECT Answers.qID, (Sum([Answers].[Answer])/Count([Answers].[Answer]))*-1 AS [Percent]
FROM (Survey INNER JOIN Sample ON Survey.SampleID = Sample.SampleID) INNER JOIN (tblQuestion INNER JOIN Answers ON tblQuestion.qID = Answers.qID) ON Survey.SampleID = Answers.SampleID
WHERE (((tblQuestion.Dependent)=No))
GROUP BY Answers.qID, tblQuestion.Order, tblQuestion.Type
HAVING (((tblQuestion.Type)="B"))
ORDER BY tblQuestion.Order;

which is the query that uses no dimensions of stratification in calculation the percentages.

The query I am trying to create now is for four Yes/No questions for which the denominator of the percent will be the number of Yes answers to a different question. As you can see previously, the denominator has simply been the number of companies that answered a given question, hence giving the percentage of Yes answers.

Here's the SQL for the query that seems like it should work for me, but instead I get the good old "You tried to execute a query that does not include the specified expression . . ." message.

SELECT Universe.Size, tblQuestion.qID, Sum([Answers].[Answer])/[SizeDenF]![expr1]*-1 AS [Percent]
FROM SizeDenF INNER JOIN (((Survey INNER JOIN Sample ON Survey.SampleID = Sample.SampleID) INNER JOIN Universe ON (Sample.UNUM = Universe.UNUM) AND (Sample.RNUM = Universe.RNUM)) INNER JOIN (tblQuestion INNER JOIN Answers ON tblQuestion.qID = Answers.qID) ON Survey.SampleID = Answers.SampleID) ON SizeDenF.Size = Universe.Size
GROUP BY Universe.Size, tblQuestion.qID, tblQuestion.Order, Answers.qID
HAVING (((tblQuestion.qID)="7F" Or (tblQuestion.qID)="8F" Or (tblQuestion.qID)="11F"))
ORDER BY tblQuestion.Order;

As you can see, I have created a query SizeDenF (actually six of them for three dimensions of stratification with FT and PT employees) that counts the number of Yes answers to that other question, for each dimension of stratification. But, as I said, when I run this query, I get the error "You tried to execute a query that does not include the specified expression . . ."

What am I doing wrong?

 

[davefwelch]

figured it out, but why?

Almost by trial and error, I figured out that the field from the intermediate query ([SizeDenF]![expr1]) needs to be included, shown or not, by itself in this query.