Complex (for me) IIf query

[Michel Walsh]

Using an inner join on a table with no record, indeed, produces something
with no record. You use:

.... FROM ... Employees INNER JOIN EmployeeProduction ...


and since if EmployeeProduction is empty, there will be no possible match.
The FROM clause then 'returns' an empty set of records. You can, better than
me, say why you need that inner join with something that can be empty.


Vanderghast, Access MVP

 

[tbrogdon]

Using an inner join on a table with no record, indeed, produces something
with no record. You use:

... FROM ... Employees INNER JOIN EmployeeProduction  ...

and since if EmployeeProduction is empty, there will be no possible match.
The FROM clause then 'returns' an empty set of records. You can, better than
me, say why you need that inner join with something that can be empty.

Hi Michael,

I am pretty new to Access and am probably trying to do something that
I have not designed my tables to do I think.

The Employees and Shift tables contain all static data re: Employees
(i.e., name, shift, dept, job function, etc.) and Shift includes the
Shift number and the length in hours of each shift (ShiftHours).

EmployeeProduction is junction table that needs to hold each employees
Name via EmployeeID and the ACTUAL number of hours they worked in
which of two job functions during that shift. It is only populated by
each shift/dept supervisors input.

I am trying to setup a form that allows a supervisor to input all of
the default data for each employee by Shift, Dept, and JobFunctionID
so the supervisor, at the end of the shift, can change the hours
worked by each employee given whether they worked more or less hours
during that shift and in which job function.

The whole purpose is to enter all of the default data i one fell swoop
to midify it later.

Am I going about this wrong from a design perspective?

Thanks,

Tim

 

[Michel Walsh]

It sounds it can be done in one step, indeed, but then, at least from what I
understand, you may consider using a Cartesian join rather than an inner
join. A Cartesian join is what you get when you bring two tables WITHOUT any
constraint. As example, just for illustration purpose, if you have a table
Days, with one field, theDay, with values from 1 to 31 and, also, have
another table, Hours, one field, theHour, with values from 0 to 23, then, to
get all possible mix, from {theDay=0, theHour=0} to {theDay=31,
theHour=23}, someone can use a Cartesian join:


SELECT theDay, theHour
FROM Days, Hours



You can then append that data to a third table, Schedules, three fields:
theDay, theHour, theComment, with something like:


INSERT INTO Schedules(theDay, theHour)
SELECT theDay, theHour
FROM Days, Hours


That would add 31 * 24 new rows to Schedules, with the default value in the
field theComment. The default value can be specified at the design table for
table Schedules, or, the value NULL would be use, if no default value has
been specified at the table design.


Once these new rows are appended, the table Schedules can be open, and the
field theComment can be changed, as usual.

And at the end of the month, if you want, you can remove the rows which have
nothing in the field theComment, still as example.


Does that help you a little?


Vanderghast, Access MVP

Using an inner join on a table with no record, indeed, produces something
with no record. You use:

... FROM ... Employees INNER JOIN EmployeeProduction ...

and since if EmployeeProduction is empty, there will be no possible match.
The FROM clause then 'returns' an empty set of records. You can, better
than
me, say why you need that inner join with something that can be empty.

Hi Michael,

I am pretty new to Access and am probably trying to do something that
I have not designed my tables to do I think.

The Employees and Shift tables contain all static data re: Employees
(i.e., name, shift, dept, job function, etc.) and Shift includes the
Shift number and the length in hours of each shift (ShiftHours).

EmployeeProduction is junction table that needs to hold each employees
Name via EmployeeID and the ACTUAL number of hours they worked in
which of two job functions during that shift. It is only populated by
each shift/dept supervisors input.

I am trying to setup a form that allows a supervisor to input all of
the default data for each employee by Shift, Dept, and JobFunctionID
so the supervisor, at the end of the shift, can change the hours
worked by each employee given whether they worked more or less hours
during that shift and in which job function.

The whole purpose is to enter all of the default data i one fell swoop
to midify it later.

Am I going about this wrong from a design perspective?

Thanks,

Tim

 

[tbrogdon]

Hi Michael,

I think I understand the implications of a Cartesian join. I am going
to have to work with it for a bit.

Am I understanding correctly that an unconstrained join will result in
a recordset that has every possible permutation of of the fields I
include?

Also, where should I place my IIf statements? I am assuming as field
criteria in the QBE (e.g., HoursMachine: IIf.......).

Is that correct?

Thank you very much.

Tim

 

[Michel Walsh]

The Cartesian join will produce every possible permutation of the ROWS of
each table. There is no difference if each table has only one field, but the
difference is important if at least one of the tables have more than one
row.

If table1 is like

f1 f2 f3 'fields
a1 b1 c1
a2 b2 c2 ' values


and table2:

g1 g2 ' fields
x1 y1
x2 y2
x3 y3 'values


then

SELECT *
FROM table1, table2


will lead to 6 rows:

f1 f2 f3 g1 g2 ' fields
a1 b1 c1 x1 y1
a1 b1 c1 x2 y2
a1 b1 c1 x3 y3
a2 b2 c2 x1 y1
a2 b2 c2 x2 y2
a2 b2 c2 x3 y3 ' join result


where any given row of table1 meets each row of table2.


An inner join would ELIMINATE rows, in the result, that evaluate the ON
clause to something else than TRUE.
(That describes the result we want, NOT the WAY, step by step, that the
inner join must use... optimization may find faster way to reach the
intended result, faster than, first, making a Cartesian join)


Sure, you can add computed columns, to your SELECT clause, like:

SELECT *, f1+g1
FROM table1, table2


which adds a new column in the result, made of the sum (or concatenation) of
values from f1 and from g1. The computed column can use +, or, sure, it can
use iif:

SELECT f1, g1, iif(f1>g1, f1, g1) AS maxBetweenF1andG1
FROM table1, table2


where here, the computed column returns the maximum value between the one
under f1 and the one under g1, for each row of the result.

So, in your case, computed columns implying iif can be used to add default
(or computed, or proposed) AssemblyHours and MachineHours, from what I
understand.




Vanderghast, Access MVP