Complex (for me) IIf query

T

tbrogdon

I have tblEmployeeProduction which is populated via a form using an
Update query with the following sql:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, ShiftHours )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift, JobFunctionID,
Shift.ShiftHours
FROM Shift INNER JOIN Employees ON Shift.Shift=Employees.Shift
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

This all works great. The reason for returning Shift.ShiftHours is
that 3rd shift works 7.5 hours instead of 8 hours like 1st and 2nd
shift. JobFunctionID denotes one of the following 3 values:
1 (Operator), 2 (Assembler), or 3 (Other).

So currently, my query returns the appropriate default number of total
hours for each employee by shift and it returns the appropriate
JobFunctionID.

However, any given employee can work more or less hours AND they can
work as both an operator or an assembler and I need to know how many
hours they worked in either capacity.

What I have considered doing is deleting the field
tblEmployeeProduction.ShiftHours and replace it with 2 fields:
HoursOperator and HoursAssembler.

In tblEmployee.JobFunctionID, each employee is categorized as either
and Operator or an Assembler so what I could do is have my query do
something like this both new fields:

If (Shift = 3 and JobFunctionID = 1 then HoursOperator = 7.5 or if
Shift = 3 and JobFunction <> 1 then HoursOperator = 0 )
Else If (Shift <> 3 AND JobFunctionID = 1 then HoursOperator = 8 or if
Shift <> 3 and JobFunctionID <> 1 then HoursOperator = 0)

HoursAssembler would be exactly the sme thing with the substitution of
the field name.

But I am a novice at SQL and can't figure out the correct way to write
this.

Thanks in advance,

Tim
 
D

Douglas J. Steele

IIf(Shift = 3, IIf(JobFunctionID = 1, 7.5, 0), IIf(JobFunctionID = 1, 8, 0))
 
T

tbrogdon

Thanks Doug,

So now I have:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID,
IIf("Shift"=3,IIf("JobFunctionID"=1,7.5,0),IIf("JobFunctionID"=1,8,0))
AS HoursMachine,
IIf("Shift"=3,IIf("JobFunctionID"=1,7.5,0),IIf("JobFunctionID"=1,8,0))
AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

I have also tried the IIfs in the Criteria field in QBE.

But when I run it I get the following error:

"Datatype mismatch in criteria expression." Fields HoursOperator and
HoursAssembler are both calculated fields so I am having difficulty
understanding where the mismatch is coming from. Both fields are set
to datatype Decimal w/ Precision, 4; Scale, 3, and Positions, Auto.

Where else can I look for the mismatch?

Thanks,

Tim
 
D

Douglas J. Steele

You don't refer to the fields by putting quotes around their names.

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID,
IIf([Shift].[Shift]=3,IIf([JobFunctionID]=1,7.5,0),IIf([JobFunctionID]=1,8,0))
AS HoursMachine,
IIf([Shift].[Shift]=3,IIf([JobFunctionID]=1,7.5,0),IIf([JobFunctionID]=1,8,0))
AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

If the JobFunctionId field occurs in more than one of the underlying tables
in the SELECT statement, you'll need to qualify it as I did with the Shift
field.
 
T

tbrogdon

You don't refer to the fields by putting quotes around their names.
Click to expand...

Actually, I cut the your code directly from your previous email and
pasted it directly into the SQL view window. Access must have
automatically inserted the quotes. Is this possible? And if so how can
I stop that from happening?

Thanks,

Tim
 
D

Douglas J. Steele

I suspect that had there been square brackets in the original post, Access
wouldn't have made the change.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


You don't refer to the fields by putting quotes around their names.
Click to expand...

Actually, I cut the your code directly from your previous email and
pasted it directly into the SQL view window. Access must have
automatically inserted the quotes. Is this possible? And if so how can
I stop that from happening?

Thanks,

Tim
 
T

tbrogdon

Hi Doug,

I did have to qualify JobFunctionID. The query runs now with no error
message but it returns nothing. Here is the message:

"You are about to append 0 rows." which it does.

Here is the new sql statement:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID,
IIf([Shift].[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobFunctionID=1,8,0))
AS HoursMachine,
IIf([Shift].[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=1,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=1,8,0))
AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept])
AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

My table structure is as follows:

tblEmployeeProduction w/
EmployeeID
ProductionDate
Department
Shift
JobFunctionID
HoursMachine
HoursAssembly

tblEmployees w/
EmployeeID
Department
Shift
JobFunctionID

tblDepartment w/
Department

tblShift w/
Shift
ShiftHours

tblJobFunction w/
JobFunctionID (linked to Employees.JobFunctionID which is linked to
EmployeeProduction.JobFunctionID)
JobFunctionDescription
 
D

Douglas J. Steele

That imples that no records meet the conditions defined by the combo boxes.
Are you sure that the bound column in the combo boxes in question return
what you think they do?
 
T

tbrogdon

That imples that no records meet the conditions defined by the combo boxes..
Are you sure that the bound column in the combo boxes in question return
what you think they do?
Click to expand...

Yes. The sql in its original form did exactly what I wanted up to that
point. Remember, I used to have tblEmployeeProduction.ShiftHours which
would be either 7.5 hours for 3rd shift or 8 hours for 1st or 2nd
shift.

Starting with tblEmployeeProduction with no records, I would run
frmSetEmpHours which asked for 3 pieces of information: 1)
ProductionDate as an unbound text field as datatype Date/Time (Short
Date), combo box cboDept (bound to Employees.Department), and cboShift
(bound to Employees.Shift). It also has a command button which runs
the query.

So I would enter a date in the date field on the form, select a
department from the first combo box, select a shift from the next
combo box and click the command button which populated the appropriate
fields in tblEmployeeProduction including the old field ShiftHours
which HoursOperator and HoursAssembly have taken the place of.

In the QBE, were exactly were you intending that I place the IIf
statements?
 
T

tbrogdon

Is it possible that I could use the IIf statement as a row source in
tblEmployeeProduction instead of as a field in my query and have those
fields populate after I run my Append query in the original manner?
I'm guessing not because the whole reason I need to do this is because
I need to be able to further modify the number and divisionof an
employee's hours and if the IIf is hard-coded to a field's row source
it will keep changing back to the default.. Am I correct in thinking
that?

Thank you,,
Tim
 
T

tbrogdon

After reading more of the help files, I'm wondering if I need to nest
my IIf inside of a SELECT as a subquery in the design grid?
 
D

Douglas J. Steele

The SQL you showed earlier should work.

Try running just the SELECT portion to see what's returned:
 
T

tbrogdon

Hi Doug,

Thanks for staying with me. I think we're getting closer.

I used the following SELECT query with some success:

SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobFunctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

Notice I did qualify JobFunctionID and Shift and changed the numeric
value in the second IIf. It does just what I need.

So how can I place this into my Append query? I tried it and it
returns 0 records. No error message. Just 0 records. Here is the
Append query sql:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobFunctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));
 
D

Douglas J. Steele

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?
 
T

tbrogdon

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?
Click to expand...

Hi Doug,

I am actually using the parameter and I am doing it from a form. What
I am trying to accomplish is to allow each shift supervisor to "batch"
populate tblEmployeeProduction from frmSetEmpHours. Each shift/dept
combination always has the same people theoretically working the same
amount of hours in the same job function - theoretically.

frmSetEmpHours lets the supervisor enter the date in an unbound text
field [txtDate] and then select the department and shift from combo
boxes. Then the supervisor clicks a command button which runs the sql
in my very first post - which happens to be an append query. This adds
a new record for every employee for that shift and department in
tblEmployeeProduction with the input date including the employee's
name and JobFunctionID. All of this works so far. Each employee has a
"default" job function but can act in either capacity during a shift
which leads us to my current question.

I want to run an append query (above) that also has 2 calculated
fields in it. Like I said, each employee can act in 1 or 2 or both job
functions during a shift and I need to track the number of hours spent
by an employee in each. Since each employee has "default" job function
I would like my append query to also place the corresponding "default"
number of shift hours in either [HoursMachine] or [HoursAssembly]. If
necessary those hours can be changed later by the supervisor if an
employee moves from one job function to another.

The original append query works well by itself and the query you have
helped me design works also as a secondary query to the first (i.e.,
if I run the append query first and then run the query containing the
IIf it does what I need it to do - just in an extra step).

Is it possible to merge them into one query or not? Will Access allow
the IIf calculation on JobFunctionID and Shift at the same time it is
doing an append? Or is it possible to run the append query in VBA and
include the IIf?

The VBA attached to the command button is simply:
Private Sub cmdFillRecords_Click()
On Error GoTo Err_cmdFillRecords_Click

Dim stDocName As String

stDocName = "qryEmpShiftHours"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdFillRecords_Click:
Exit Sub

Err_cmdFillRecords_Click:
MsgBox Err.Description
Resume Exit_cmdFillRecords_Click

End Sub
 
D

Douglas J. Steele

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see
whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?
Click to expand...

Hi Doug,

Is it possible to merge them into one query or not? Will Access allow
the IIf calculation on JobFunctionID and Shift at the same time it is
doing an append? Or is it possible to run the append query in VBA and
include the IIf?
Click to expand...

It should work.


See whether this works any better:

Private Sub cmdFillRecords_Click()
On Error GoTo Err_cmdFillRecords_Click

CurrentDb.QueryDefs("qryEmpShiftHours").Execute dbFailOnError

Exit_cmdFillRecords_Click:
Exit Sub

Err_cmdFillRecords_Click:
MsgBox Err.Description
Resume Exit_cmdFillRecords_Click

End Sub

If you get an error about dbFailOnError not being declared, make sure you
have a reference set to Microsoft DAO 3.6 Object Library (You do this
through Tools | References while in the VB Editor)
 
T

tbrogdon

Hi Doug,

I tried your code verbatim but I received the following dialog box:
"Too few parameters. Expected 3." Depending on how you look at it,
frmSetEmpHours does request and qryEmpShiftHours does utilize 3
parameters. The first is txtDate (unbound) stored as ProductionDate;
cboDept (como box w/ row source SELECT Department.Department FROM
Department) and cboShift (combo box w/ row source SELECT Shift.Shift
FROM Shift).

I tried the following query:
PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction!JobFunctionID=1,7.5,0), IIf(EmployeeProduction!
JobFunctionID=1,8,0)) AS HoursMachine, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction.JobFunctionID=2,7.5,0),
IIf(EmployeeProduction.JobFunctionID=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID=EmployeeProduction.EmployeeID) ON
(Shift.Shift=Employees.Shift) AND
(Shift.Shift=EmployeeProduction.Shift)
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

Notice that I do use [txtDate] AS ProductionDate in the SELECT
statement.

I also tried my original query (1st post) and received the same dialog
box "Too few parameters..."

Is there a way for me to check how many parameters it is receiving if
any and which ones?> Private Sub cmdFillRecords_Click()
 
M

Michel Walsh

If you use

DoCmd.OpenQuery "qryEmpShiftHours"

instead of

CurrentDb.Execute "qryEmpShiftHours"

that should solve the parameter problem with FORMS!formName!ControlName.
Indeed, DoCmd solves the FORMS!formName!ControlName parameters for you, but
CurrentDb does NOT do it automatically, for you.


(you can also use DoCmd.RunSQL instead of DoCmd.OpenQuery, if more
appropriate, given the situation).



Vanderghast, Access MVP
 
T

tbrogdon

Hi DOug and Mike,

I tried a modifcation of your code:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "qryEmpShiftHours"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

with this query:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID,
IIf(EmployeeProduction.Shift=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobFunctionID=1,8,0)) AS
HoursMachine,
IIf(EmployeeProduction.Shift=3,IIf(EmployeeProduction.JobFunctionID=2,7.5,0),IIf(EmployeeProduction.JobFunctionID=2,8,0))
AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID=EmployeeProduction.EmployeeID) ON
(Shift.Shift=Employees.Shift) AND
(Shift.Shift=EmployeeProduction.Shift)
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));


and it works great! I've only run it a couple fo times but it looks
like it is going to work!

Thank you both very much for your help.

Now more testing! :)

Tim
 
T

tbrogdon

Well,

I guess I spoke too soon. Using the above code and query with an EMPTY
tblEmployeeProduction I receive no error messages but it runs the
append query and returns 0 records.

When I ran it before with success tblEmployeeProduction actually had
data in it and I suspect that the query was repeating records from
tblEmployeeProduction rather than querying the Employees table.

The reason I think this is that, selecting a date, specific department
and shift appended far too many records. For instance, Dept 1, 3rd
Shift has 18 employees and the query appended 54 on repeated attempts.

Any thoughts?

Thanks,

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Key Violations My Wits End 7
Select/Append Query Malfunction 1
Select, Modify, and Append Question 0
Query requesting parameter value twice 7
SELECT query as subquery to APPEND 2
Capture composite PK for input in second and third table 0
Use single entry in one table to generate multiple in another 1
Add a (sequential not autonumber) number in append query 5

Top