473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Cross-joins and non-updateable record sets

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
Feb 24 '06 #1
7 3034
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

Feb 24 '06 #2
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
Feb 24 '06 #3
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
Feb 27 '06 #4
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

Feb 27 '06 #5
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
Feb 28 '06 #6
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

Feb 28 '06 #7
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 ***
Mar 1 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: * ProteanThread * | last post by:
but depends upon the clique: ...
3
by: rollasoc | last post by:
Hi, Doing a bit of system testing on a Windows 98 laptop. (.Net 1.1 app). Did a bit of testing. Loaded a previously saved file. A gray box appeared with the text and buttons all white...
4
by: David Peach | last post by:
Hello, hope somebody here can help me... I have a query that lists defects recorded in a user defined date range. That query is then used as the source for a Cross Tab query that cross-tabs count...
23
by: Jeff Rodriguez | last post by:
Here's what I want do: Have a main daemon which starts up several threads in a Boss-Queue structure. From those threads, I want them all to sit and watch a queue. Once an entry goes into the...
7
by: Scott M. | last post by:
How can I disable the cross-site scripting check for one particular page of a site?
8
by: Pieter | last post by:
Hi, I'm having some weird problem using the BackGroundWorker in an Outlook (2003) Add-In, with VB.NET 2005: I'm using the BackGroundWorker to get the info of some mailitems, and after each item...
1
by: Rob Woodworth | last post by:
Hi, I'm having serious problems getting my report to work. I need to generate a timesheet report which will contain info for one employee between certain dates (one week's worth of dates). I...
6
by: Simon | last post by:
Hi All, An experiment i'm doing requires requires a synchronous cross-domain request, without using a proxy. I wondered if anyone had any ideas to help me achieve this. Below is what I have...
6
by: Bart Van der Donck | last post by:
Hello, I'm presenting my new library 'AJAX Cross Domain' - a javascript extension that allows to perform cross-domain AJAX requests. http://www.ajax-cross-domain.com/ Any comments or...
6
by: ampo | last post by:
Hello. Can anyone help with cross-domain problem? I have HTML page from server1 that send xmlHTTPRequest to server2. How can I do it? Thanks.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.