Question Getting totals in access when the field answers include ranges

fllopez65

Registered User.
Local time
Today, 03:08
Joined
Sep 22, 2011
Messages
43
Folks
I use access 2007 and I need to produce a total for fields where several fields answers include ranges and could be the same ie: answer could be 0-25,26-50 etc and within the column the 0-25 answer may appear 3or4 times. I thought in a query using the total row in the criteria grid but I think I need to assign each grid a value then add up the values. Example 0-25 = 1, 26-50 = 2 then add up the values. Not sure how to go about doing that in query. Sounds like boolerean again not sure. Think of as tabulating a the results of a survey with several ranges within the same category but you have to both total and seperate those different responses for that category.
Any tips would be greatly appreciated.

Tks
 
I'm lost. Could you post your table's structure, some sample data and then the output you want from your query for that sample data?
 
Plog
Will try asap.

tks
fllopez65
 
Tks will try the suggestions. Will it work for the attached db?
 

Attachments

vbaInet
I previously attached is my database which has a questionnaire and a data of sample responses. What I'm trying to do is to design a query which would provide(using household income as an example):
a. Totals for a particular range, for example: 0-25,00
b. An average for that same category.
To solve a., I think I need to a value for each range 0-25,000 = 1, 25001-50000 = 2 and so on because as the sample response table shows there are 2 respondents who fit into the range 0-25000.
To get a total for b., I think I need to come up with the average salary range for all the respondents.
I'm not sure whether I should try to create new combo fields(linking let say household income with purchaser's age) and or do a macro. I've got so many variables to deal with because of all the ranges within each category(ie: age, household income) not sure where to start.
Do you think the crosstab query will work for what I state above?
Any suggestions would be greatly appreciated.
tks
fllopez65
 
The database is ok, but you didn't give me an example of what you want your output to look like. From what I've read, I think this SQL will give you what you want:

Code:
SELECT [Survey Responses].[Household Income], Count([Survey Responses].[Respondent ID]) AS Total
FROM [Survey Responses]
GROUP BY [Survey Responses].[Household Income];

That will get you the total by range.
 
Tks vbaInet,plog will try your suggestions.

fllopez65
 
I think plog has got you sorted there. The crosstab would only have been useful when there wasn't a range table.
 
Tks to both vbaInet,plog, got it to work and will modify as necessary.

fllopez65
 
I modified the sql statement to read:SELECT [Survey Responses].[Buyers age], Count([Survey Responses].[Respondent ID]) AS Total
FROM [Survey Responses]
GROUP BY [Survey Responses].[Buyers age];
but I keep getting an error message: The multi valued field: '[Survey Responses].[Buyers age] cannot be used in a GROUP BY clause. I don't understand why this error comes up because the buyers age multi-value field is very similar to the household income field which has dropdown menu of options? Would a boolean operator work better for this particular query?

tks
fllopez65
 
Just like the error message says, you can't use a multivalued field in a Group By clause.

If you want to include that field in your query, create a query to pull all the fields that you need, then create another query based on this one and use it for the Count.
 
But the household income was a multi-value field also. why does it allow one and not the other?
 
Change Group By to Expression. You can't Group By a multivalued field. If you select the .Value of the multivalued field then you should be able to Group By.
 
I'm not sure what you mean by .Value? I will try the expression instead. tks
 
Look at the query design grid. If you click the + sign next to the multivalue field, it will expand. The field that becomes visible will have .Value affixed to it. For example, Buyers Age would look like Buyers Age.Value
 
Using the .value does seperate the rows but doesn't give the totals I need though but tks anyway.
 
Ok, you can still use the other field but Group By would need to be Expression.
 

Users who are viewing this thread

Back
Top Bottom