select statement within SQL execute (1 Viewer)

[matt beamish]

Hi folks,
the following sql works OK for me as part of a larger SQL statement, but I would like to more efficiently reference the Job_Nos from a table in which these particular job_nos are listed.

What would my statement be, to select the Job_nos in the IIf, from a table "tbl_jobnosforactualhrs" rather than separately specified as below.

If I haven't included enough information then apologies.

Round(((rst.Fields("WeeklyHoursTotal") / 60)) /IIf((rst.Fields("Job_no") = "00001") Or (rst.Fields("Job_no") = "00002") Or (rst.Fields("Job_no") = "00006") Or (rst.Fields("Job_no") = "00007") Or (rst.Fields("Job_no") = "00008") Or (rst.Fields("Job_no") = "00009") Or (rst.Fields("Job_no") = "00011") Or (rst.Fields("Job_no") = "00130") Or (rst.Fields("Job_no") = "0043"), 1, ((Me.[ActWeekHrsTot]) - (Me.[HrsasExactcntrl])) / ((Me.[ContractHourscntrl]) - (Me.[HrsasExactcntrl]))), 2) & ")"

 

[CJ_London]

basically, you have asked for help fixing the car and provided the steering wheel. I can infer this is a bit of vba code which references a recordset, but that's it.

what is the background to what you are trying to achieve? what is the sql? why not just include the table in your sql?

 

[Cronk]

Matt, if you want to simplify the test for existence of Job Nos, you could either put the test into a vba function that your sql calls, or use a sub query. Allen Browne's site has some excellent examples (search 'allen browne access')

 

[static]

You could use IN / NOT IN and a UNION. e.g.

Select *, (WeeklyHoursTotal / 60) as TheValue from YourTable
where Job_no IN (select Job_no from tblJob_no)
union
Select *, (ActWeekHrsTot - Me.[HrsasExactcntrl]) / (ContractHourscntrl - HrsasExactcntrl) as TheValue from YourTable
where Job_no NOT IN (select Job_no from tblJob_no)

 

[matt beamish]

re

basically, you have asked for help fixing the car and provided the steering wheel. I can infer this is a bit of vba code which references a recordset, but that's it.

what is the background to what you are trying to achieve? what is the sql? why not just include the table in your sql?

Apologies.
The vba is manipulating a number of records which comprises a recordset that are listed on a subform, along with some controls on that subform.

I am sending those records to another table and in the process doing some simple maths: the hours in the host database are daily worked hours including some which aren't paid for: the hours in the target database are those that are paid for and are summed by job by week. So where people work more or less hours than they are paid for I am transforming their hours by a factor (which is the ratio of Actual Hours/ Contracted Hours). Some jobs (the ones I separately list) are exempt from this and are to be transferred with no transformation.
My IIf statement successfully excludes these Job_nos from the transformation and they are transferred as is, but I want to use the presence of these job nos in a separate table that can be easily edited rather than hard coded in the vb.

 

[matt beamish]

Thanks Static, that's just the pointer I needed. I know my knowledge is perilously light on some fundamentals.