Combo box query

[kevlarsoul]

Hi again folks! (Newbie here)



I'm really struggling on this one.

I Have a table with:-

tbl.Interviewer

ID (Primary Key)
Employee Number
Forename
Surname
Gender (Dropdown) "Male","Female","Unspecified"

Ive tried to do a query with =Count(IIf([Gender]="Male",1)) in the criteria box options are Field I have Gender selected, then underneath that Interviewer in the table field. Then Show checked.

In order to count the number of occurrences, I actually need to calculate the gender bias.

(a) the query doesn't work, tho as far as I can see it has the right syntax. AND
(b) I can't figure out how I'm gonna come to calculate either a M/F/U ration or to express each of them as a list of percentages.

Thanks heaps in advance for your time and help. Its very much appreciated!
D.

[June7]

Post the query SQL statement. What you describe doesn't make sense to me.

How is this associated with a combobox?

Easiest approach to accomplish percentage calcs is to build a report using Sorting & Grouping with aggregate calcs.

[kevlarsoul]

Ok sorry about that
Here goes here is the sql

SELECT Interviewer.employeeNumber, Interviewer.Gender
FROM Interviewer
WHERE (((Interviewer.Gender)=Count(IIf([Gender]="Male",1))));

With regards to the combo box, The values, Male, Female and unspecified are selected form there by way of the input form. I just thought that this may be important to mention.

[June7]

Yep, WHERE clause makes no sense. Assuming Gender is a text field with alpha data, why would you compare to a number value?

And Count() is an aggregate function that only works in an aggregate (GROUP BY) query. I see no reason for Count().

Is this the SQL for combobox RowSource property? You want to filter the combobox to show only interviewers that match Gender selected in another control? This is called cascading (or dependent) combobox. Assuming that other control is named "Gender", set combobox properties:

RowSource: SELECT employeeNumber, employeeName FROM Interviewer WHERE Gender=[Gender];
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
ControlSource: the field you want to save employeeNumber into

[kevlarsoul]

No, this I did in query design, and the SQL is what has been generated by ACCESS. I thought I would be able to do a query, that would return the values of the number of occurences of each of the three variables "M" "F" and "U" this, I had hoped could be done without any user input other than to generate a report from the switchboard.

[June7]

Yes, a count of each value is possible but that has nothing to do the WHERE clause.

And how is a combobox involved with a query to calculate these counts?

SELECT Count(IIf([Gender]="Male",1)) AS CntMale, Count(IIf([Gender]="Female",1)) AS CntFem, Count(IIf([Gender]="Unspecified",1)) AS CntUnd, Count([Gender]) AS Total;

Now use that query as source for another query that calculates percentages or use it as RecordSource for a report. Or build a report as suggested earlier which allows display of detail data as well as aggregate calcs.

User input is only required if you design for it.

[kevlarsoul]

Ok, Thanks for that, I'll give that a go.
Really appreciate you time.

There is no actual combo box in the query, just in the tables/forms to produce the data. That's all. Think I probably didn't make that clear.

Now.... Stupid question probably

Would I just type all of that into the criteria field in the query?

[June7]

No, 'all of that' IS the query.