Cross-joins and non-updateable record sets

[Stephen Poley]

I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus optional
extra textual information (e.g. "left early due to illness").

The solution I had in mind was:
1) create a presence table, keyed on employee-id and session-id,
containing a 'present' yes/no field and a comment text field;
2) cross-join employees and sessions; (this gives a record-set with all
the occasions someone could have attended);
3) left outer-join the result of (2) on the presence table, joining on
both employee-id and session-id; in principle entering presence data
should cause a new record to be created with the two foreign keys;
4) couple the result of (3) as a subform of a session form, with the
subform coupled on session-id.

The result would thus be a form with information about the session; it
has a subform with a list of employees and a 'present' field against
each, and the user can run through the list, ticking off everyone who
was present at that session. (The total number of employees is only
around 30, so this is perfectly feasible for the user.)

All fine and dandy except for one thing - the record-set resulting from
step (3) is non-updateable.

How do I get an updateable record-set? Or am I barking up the wrong tree
completely, and need to tackle it differently?

--
Stephen Poley

[Ed Robichaud]

You need a third table to handle this common many-many situation.

tblEmployee
EmpID -primary key
employee name.......

tblTraining
TngID -key

tblClassAttendance
ClassID -primary key
ClassDate
EmpID - foreign key
TngID - foreign key

-Ed
"Stephen Poley" <sb******************@xs4all.nl> wrote in message
news:m6********************************@4ax.com...

I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus optional
extra textual information (e.g. "left early due to illness").

The solution I had in mind was:
1) create a presence table, keyed on employee-id and session-id,
containing a 'present' yes/no field and a comment text field;
2) cross-join employees and sessions; (this gives a record-set with all
the occasions someone could have attended);
3) left outer-join the result of (2) on the presence table, joining on
both employee-id and session-id; in principle entering presence data
should cause a new record to be created with the two foreign keys;
4) couple the result of (3) as a subform of a session form, with the
subform coupled on session-id.

The result would thus be a form with information about the session; it
has a subform with a list of employees and a 'present' field against
each, and the user can run through the list, ticking off everyone who
was present at that session. (The total number of employees is only
around 30, so this is perfectly feasible for the user.)

All fine and dandy except for one thing - the record-set resulting from
step (3) is non-updateable.

How do I get an updateable record-set? Or am I barking up the wrong tree
completely, and need to tackle it differently?

--
Stephen Poley

[Stephen Poley]

Er, yes - that was my step 1. But having created it, how do I get
records into it?
On Fri, 24 Feb 2006 12:26:09 -0500, "Ed Robichaud" <ed*********@wdn.com>
wrote:

You need a third table to handle this common many-many situation.

tblEmployee
EmpID -primary key
employee name.......

tblTraining
TngID -key

tblClassAttendance
ClassID -primary key
ClassDate
EmpID - foreign key
TngID - foreign key

-Ed
"Stephen Poley" <sb******************@xs4all.nl> wrote in message
news:m6********************************@4ax.com.. .

I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus optional
extra textual information (e.g. "left early due to illness").

The solution I had in mind was:
1) create a presence table, keyed on employee-id and session-id,
containing a 'present' yes/no field and a comment text field;
2) cross-join employees and sessions; (this gives a record-set with all
the occasions someone could have attended);
3) left outer-join the result of (2) on the presence table, joining on
both employee-id and session-id; in principle entering presence data
should cause a new record to be created with the two foreign keys;
4) couple the result of (3) as a subform of a session form, with the
subform coupled on session-id.

The result would thus be a form with information about the session; it
has a subform with a list of employees and a 'present' field against
each, and the user can run through the list, ticking off everyone who
was present at that session. (The total number of employees is only
around 30, so this is perfectly feasible for the user.)

All fine and dandy except for one thing - the record-set resulting from
step (3) is non-updateable.

How do I get an updateable record-set? Or am I barking up the wrong tree
completely, and need to tackle it differently?

--
Stephen Poley

--
Stephen Poley

[Stephen Poley]

Any suggestions?

On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb******************@xs4all.nl> wrote:

I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus optional
extra textual information (e.g. "left early due to illness").

The solution I had in mind was:
1) create a presence table, keyed on employee-id and session-id,
containing a 'present' yes/no field and a comment text field;
2) cross-join employees and sessions; (this gives a record-set with all
the occasions someone could have attended);
3) left outer-join the result of (2) on the presence table, joining on
both employee-id and session-id; in principle entering presence data
should cause a new record to be created with the two foreign keys;
4) couple the result of (3) as a subform of a session form, with the
subform coupled on session-id.

The result would thus be a form with information about the session; it
has a subform with a list of employees and a 'present' field against
each, and the user can run through the list, ticking off everyone who
was present at that session. (The total number of employees is only
around 30, so this is perfectly feasible for the user.)

All fine and dandy except for one thing - the record-set resulting from
step (3) is non-updateable.

How do I get an updateable record-set? Or am I barking up the wrong tree
completely, and need to tackle it differently?

--
Stephen Poley

[CDMAPoster]

Stephen Poley wrote:

Any suggestions?

On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb******************@xs4all.nl> wrote:

I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus optional
extra textual information (e.g. "left early due to illness").

The solution I had in mind was:
1) create a presence table, keyed on employee-id and session-id,
containing a 'present' yes/no field and a comment text field;
2) cross-join employees and sessions; (this gives a record-set with all
the occasions someone could have attended);
3) left outer-join the result of (2) on the presence table, joining on
both employee-id and session-id; in principle entering presence data
should cause a new record to be created with the two foreign keys;
4) couple the result of (3) as a subform of a session form, with the
subform coupled on session-id.

The result would thus be a form with information about the session; it
has a subform with a list of employees and a 'present' field against
each, and the user can run through the list, ticking off everyone who
was present at that session. (The total number of employees is only
around 30, so this is perfectly feasible for the user.)

All fine and dandy except for one thing - the record-set resulting from
step (3) is non-updateable.

How do I get an updateable record-set? Or am I barking up the wrong tree
completely, and need to tackle it differently?

--
Stephen Poley

Maybe you're tackling the wrong tree :-).

Use a form that has comboboxes for employee and training along with a
combobox for class that gets filled when the training ID is selected.
When the employee and class are selected the form makes a new record in
tblClassAttendance. So you need a table in addition to the ones Ed
suggested:

tblClassTraining
CTID Autonumber PK
ClassID Long FK
TngID Long FK

that shows the classes that fit under a given training ID. Note that
the same class may fall under multiple training ID's. This table can
also be filled using a form. So now you really need another table that
holds all the names of the classes. A possible schema:

tblEmployee
EmployeeID Autonumber PK
EmployeeLastName Text
....

tblClass
CID Autonumber PK
ClassName Text
ClassDate Date/Time
ClassLocation Text
....

tblTraining
TrainingID Autonumber PK
TrainingCategoryName Text
....

tblClassTraining
CTID Autonumber PK
ClassID Long FK
TrainingID Long FK

tblClassAttendance
CAID Autonumber PK
EmployeeID Long FK
TrainingID Long FK
WasPresent Y/N
Notes Text

So tblEmployee, tblClass and tblTraining are each filled in using a
form. After the classes and training categories are created, another
form associates classes with each training category and the results are
added to tblClassTraining. An enrollment form with three comboboxes
will place the chosen employee into a class chosen from classes that
fall under a chosen training category.

Many here can suggest SQL strings or other means for handling
attendance or other tasks once you have your schema nailed down.
Hopefully we'll be able to suggest a SQL string that is updateable.

James A. Fortune
CD********@FortuneJames.com

[Stephen Poley]

On 27 Feb 2006 13:22:39 -0800, CD********@FortuneJames.com wrote:

On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb******************@xs4all.nl> wrote:

>I have the following situation:
>- a table of employees, keyed on employee-id;
>- a table of training sessions, keyed on session-id;
>- a requirement to log who was present at which session, plus optional
>extra textual information (e.g. "left early due to illness").
>
>The solution I had in mind was:
>1) create a presence table, keyed on employee-id and session-id,
>containing a 'present' yes/no field and a comment text field;
>2) cross-join employees and sessions; (this gives a record-set with all
>the occasions someone could have attended);
>3) left outer-join the result of (2) on the presence table, joining on
>both employee-id and session-id; in principle entering presence data
>should cause a new record to be created with the two foreign keys;
>4) couple the result of (3) as a subform of a session form, with the
>subform coupled on session-id.
>
>The result would thus be a form with information about the session; it
>has a subform with a list of employees and a 'present' field against
>each, and the user can run through the list, ticking off everyone who
>was present at that session. (The total number of employees is only
>around 30, so this is perfectly feasible for the user.)
>
>All fine and dandy except for one thing - the record-set resulting from
>step (3) is non-updateable.
>
>How do I get an updateable record-set? Or am I barking up the wrong tree
>completely, and need to tackle it differently?

Maybe you're tackling the wrong tree :-).

Use a form that has comboboxes for employee and training along with a
combobox for class that gets filled when the training ID is selected.
When the employee and class are selected the form makes a new record in
tblClassAttendance.

<snip>

OK, that would work, but if I understand it correctly, it isn't as user
friendly as one would like. To record that 20 employees attended a given
session, the user would need to:

- select the session in the combobox (the number of sessions is
sufficiently large to require scrolling)
- select the employee in the combobox (the number of employees is also
sufficiently large to require scrolling)
- tick the box
- select next record

.... and repeat that 20 times. (I think - if it's actually much simpler
than this, please correct me.)

I was hoping that the user could:
- select the session in a combobox once
- be presented with a list of employees in a subform, with a checkbox
against each (it would presumably be greyed out initially, as the
records don't yet exist)
- quickly tick off everyone who was present.

Is that possible? In a situation where the table with the checkboxes is
joined to a single table it is, and I was hoping that it also would be
possible if they were joined to a pair of cross-joined tables.

If all else fails I suppose I could just create a record for every
employee/session combination in VBA, but it's not a very nice solution.

--
Stephen Poley

[CDMAPoster]

Stephen Poley wrote:

On 27 Feb 2006 13:22:39 -0800, CD********@FortuneJames.com wrote:

On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb******************@xs4all.nl> wrote:

>I have the following situation:
>- a table of employees, keyed on employee-id;
>- a table of training sessions, keyed on session-id;
>- a requirement to log who was present at which session, plus optional
>extra textual information (e.g. "left early due to illness").
>
>The solution I had in mind was:
>1) create a presence table, keyed on employee-id and session-id,
>containing a 'present' yes/no field and a comment text field;
>2) cross-join employees and sessions; (this gives a record-set with all
>the occasions someone could have attended);
>3) left outer-join the result of (2) on the presence table, joining on
>both employee-id and session-id; in principle entering presence data
>should cause a new record to be created with the two foreign keys;
>4) couple the result of (3) as a subform of a session form, with the
>subform coupled on session-id.
>
>The result would thus be a form with information about the session; it
>has a subform with a list of employees and a 'present' field against
>each, and the user can run through the list, ticking off everyone who
>was present at that session. (The total number of employees is only
>around 30, so this is perfectly feasible for the user.)
>
>All fine and dandy except for one thing - the record-set resulting from
>step (3) is non-updateable.
>
>How do I get an updateable record-set? Or am I barking up the wrong tree
>completely, and need to tackle it differently?

Maybe you're tackling the wrong tree :-).

Use a form that has comboboxes for employee and training along with a
combobox for class that gets filled when the training ID is selected.
When the employee and class are selected the form makes a new record in
tblClassAttendance.

<snip>

OK, that would work, but if I understand it correctly, it isn't as user
friendly as one would like. To record that 20 employees attended a given
session, the user would need to:

- select the session in the combobox (the number of sessions is
sufficiently large to require scrolling)
- select the employee in the combobox (the number of employees is also
sufficiently large to require scrolling)
- tick the box
- select next record

... and repeat that 20 times. (I think - if it's actually much simpler
than this, please correct me.)

I was hoping that the user could:
- select the session in a combobox once
- be presented with a list of employees in a subform, with a checkbox
against each (it would presumably be greyed out initially, as the
records don't yet exist)
- quickly tick off everyone who was present.

Is that possible? In a situation where the table with the checkboxes is
joined to a single table it is, and I was hoping that it also would be
possible if they were joined to a pair of cross-joined tables.

If all else fails I suppose I could just create a record for every
employee/session combination in VBA, but it's not a very nice solution.

--
Stephen Poley

The way I see it, marking the attendance is a different situation than
enrollment. After the enrollment process is over I envisioned marking
the attendance using the entire list available from the enrollment
process in a subform, possibly using a datasheet view. Maybe add CID
from tblClass to tblClassAttendance as a foreign key also. Then the
SQL statement:

SELECT WasPresent, Notes FROM tblClassAttendance WHERE CID = 3;

should be updateable. Note that any inner joins with tblEmployee,
tblClass or tblTraining using the corresponding primary key should not
affect the editability of a query.

So the problem may become, "Can I do the enrollment for a class in one
fell swoop?" Perhaps a multiselect listbox can be used to select all
the employees that are to be enrolled in a given class. If so, you'll
still need to be able to add or delete employees later. You should
keep working out these ideas at the schema level until you are
satisfied that you will be able to get the data you need for your
reports. Your idea of a subform with employees is correct except that
the checkboxes will actually exist if the enrollment and attendance are
treated separately.

James A. Fortune
CD********@FortuneJames.com

[Rich P]

you could try something like this:

Select ty.* from (((Select fld1, fld2, ... From tbl1 Where...) t1 cross
join (select fld1, fld2, ... from tbl2 Where...) t2) tx Left Join
(select * from tbl3) t3 On tx.Id = t3.Id) ty

Rich

*** Sent via Developersdex http://www.developersdex.com ***