Basic Query/totaling fields in a query

[afisher]

Sorry, I'm sure this will be an easy answer, but I just couldn't find what I was looking for in google searches.

Anyway, What I want to do is create a query that will total how many occurrences of a particular record there are. To give you an example, I have a table called "software". In that table are:

DEVICENAME, OFFICE VERSION, OFFICE LICENSE

PCXP-101 | Office 2007 | Retail

PCXP-102 | Office 2010 | Volume

I want a query to tell me how many PC's are using a "Retail" license. I'm no master at access but I know a few SQL commands, So I came up with this:

SELECT COUNT([OFFICE LICENSE]) AS retail FROM Software WHERE [OFFICE LICENSE]='RETAIL';


That works, but if I add this to the same query:
SELECT COUNT([OFFICE LICENSE]) AS volume FROM Software WHERE [OFFICE LICENSE]='VOLUME';

It returns an error. Now I have a seperate query for every different value in that field and had to create a query to total all the other queries. I know access has to have some easy way to do this, I just can't figure this out. Thank you very much, any help is appreciated.

[TheShabz]

what is OFFICE LIC? thats not a field name that i see. that's probably why its not running.

Also, the easiest way would be to create a form and have a combobox on it that holds the different types of OFFICE LICENSEs.

then you would have:
SELECT COUNT([OFFICE LICENSE]) AS retail FROM Software WHERE [OFFICE LICENSE]=Forms!formName!comboBoxName

Then you create a button that would run the query. pretty simple.

[afisher]

Hmm, Thanks, the combo box idea might be interesting. The "OFFICE LIC" is what I actually use as the field name, I just tried to spell them out on in the post for easier reading, but I seemed to have missed one. Edit: corrected the field name.

[TheShabz]

yea just make sure everything is spelled correctly. the logic is correct.

[afisher]

Ok, I got the combo box working. Is there anyway to get the result to show on the same form instead of showing up in the query tab? I tried adding a text box with =[test]![retail] as the control source. (test being the name of the query) The query ran, but the text box showed "#name?"

Also would there be a way to query all of the possible values at once? some like:

Volume license: 25
Retail: 10
OEM Dell: 5

Or would each query have to be separate?

[TheShabz]

make a subform on the form that is bound to the query. click on the subform button in the for design toolbar and when you click inside the form a wizard will guide you through it.

afterwards, go to the properties box of the button you created to run the query and add Me.Requery to it. This will refresh the subform with the new data.

[afisher]

That works as well, except I had to requery the text box, not the button. In fact the whole thing works without the button. But I would really like one query to bring up all the results. As it stands, I have 5 queries to count totals of all the different values and a 6th query to bring those totals together into a query I base a report on.

The combo box method does work, but I can only see the results of one value at a time.