Comparison Query???

[darth sidious]

Hi

I have a table containing:

Job Type------ NumberNeeded---------- NumberAssigned
Office-----------10 ------------------------------10
Manager------- 1-------------------------------- 0
CEO-------------- 1-------------------------------- 1
Diner------------ 5-------------------------------- 2

What I'm trying to achieve is write a query that returns a list of all the jobs that still needs somebody to fill that slot. For instance, the result should show manager and diner job types still needing filling.

Any help writing this would be appreciated

Thanks

Darth

 

[pbaldy]

In design view create a new calculated field:

StillNeeded: NumberNeeded - NumberAssigned

and put in a criteria of

>0

 

[darth sidious]

Hi

Thanks for the reply.

I have a form on which I show the results of the jobs still needed in a combo box.

I have a option field above this combo. The options are 'Jobs needed' and 'Other'. Currently I have the results of the query displayed in the combo box regardless of which option is selcected from the options field.

What I want is for the combo box to display the results of the query only if the 'Jobs Needed' option is checked. How can I achieve this?

Many thanks

Darth

 

[pbaldy]

One way would be to change the row source of the combo in the after update event of the option.

 

[darth sidious]

Hi

How would i do this?

Darth

 

[pbaldy]

Like one of the methods here:

http://www.baldyweb.com/CascadingCombo.htm

You can also use the name of saved queries instead of SQL.

 

[darth sidious]

Like one of the methods here:

http://www.baldyweb.com/CascadingCombo.htm

You can also use the name of saved queries instead of SQL.

Hi

I have attached my database with the forms and associated queries. At the moment my queryAvailableJobs should pull cboCompany and OptHigh from the frmJobs and display a list of the jobs matching this criteria in a new combo box below the selctImportance section of the form..

My query doesnt seem to work. Also, I only want the new combo box to display only the results that match the query.

Please can anyone look and help with this. Many Thanks.

Darth.

 

[pbaldy]

Sorry, lost track of this thread. You store the words "low" or "high" in the table, but an option group always has a numeric value. In your case the options are 1 and 2. That means you're comparing apples to oranges. You can switch the option group to a combo so the values match what's in the table or switch what gets stored in the table to a numeric value. I suppose you could also create a calculated field in the query like:

IIf(JobStatus = "Yes", 1, 2)

and put the criteria on that.