Question Getting totals in access when the field answers include ranges

[fllopez65]

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

 

[plog]

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?

 

[fllopez65]

Plog
Will try asap.

tks
fllopez65

 

[vbaInet]

You're probably after a crosstab query. See this thread -->

http://www.access-programmers.co.uk/forums/showthread.php?t=216116

 

[fllopez65]

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

 

[fllopez65]

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

 

[plog]

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:

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.

 

[fllopez65]

Tks vbaInet,plog will try your suggestions.

fllopez65

 

[vbaInet]

I think plog has got you sorted there. The crosstab would only have been useful when there wasn't a range table.

 

[fllopez65]

Tks to both vbaInet,plog, got it to work and will modify as necessary.

fllopez65

 

[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

 

[vbaInet]

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.

 

[fllopez65]

But the household income was a multi-value field also. why does it allow one and not the other?

 

[vbaInet]

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.

 

[fllopez65]

I'm not sure what you mean by .Value? I will try the expression instead. tks

 

[vbaInet]

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

 

[fllopez65]

vbaInet
found the .value. tks
fllopez65

 

[vbaInet]

Glad to hear!

 

[fllopez65]

Using the .value does seperate the rows but doesn't give the totals I need though but tks anyway.

 

[vbaInet]

Ok, you can still use the other field but Group By would need to be Expression.