473,739 Members | 8,875 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3055
You need a third table to handle this common many-many situation.

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

tblTraining
TngID -key

tblClassAttenda nce
ClassID -primary key
ClassDate
EmpID - foreign key
TngID - foreign key

-Ed
"Stephen Poley" <sb************ ******@xs4all.n l> wrote in message
news:m6******** *************** *********@4ax.c om...
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*********@wd n.com>
wrote:
You need a third table to handle this common many-many situation.

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

tblTraining
TngID -key

tblClassAttend ance
ClassID -primary key
ClassDate
EmpID - foreign key
TngID - foreign key

-Ed
"Stephen Poley" <sb************ ******@xs4all.n l> 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.n l> 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.n l> 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
tblClassAttenda nce. So you need a table in addition to the ones Ed
suggested:

tblClassTrainin g
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
EmployeeLastNam e Text
....

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

tblTraining
TrainingID Autonumber PK
TrainingCategor yName Text
....

tblClassTrainin g
CTID Autonumber PK
ClassID Long FK
TrainingID Long FK

tblClassAttenda nce
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 tblClassTrainin g. 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********@Fort uneJames.com

Feb 27 '06 #5
On 27 Feb 2006 13:22:39 -0800, CD********@Fort uneJames.com wrote:
On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb************ ******@xs4all.n l> 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
tblClassAttend ance.


<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********@Fort uneJames.com wrote:
On Fri, 24 Feb 2006 17:37:57 +0100, Stephen Poley
<sb************ ******@xs4all.n l> 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
tblClassAttend ance.


<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 tblClassAttenda nce as a foreign key also. Then the
SQL statement:

SELECT WasPresent, Notes FROM tblClassAttenda nce 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********@Fort uneJames.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
3877
by: * ProteanThread * | last post by:
but depends upon the clique: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=954drf%24oca%241%40agate.berkeley.edu&rnum=2&prev=/groups%3Fq%3D%2522cross%2Bposting%2Bversus%2Bmulti%2Bposting%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den ...
3
3114
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 rectangles with a big red cross in it. Pressed a button (the one I thought might be ok). My file appeared to load. Then when I clicked on any button on my form, the button was replaced with a white rectangle with a big red cross in it.
4
5582
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 of defect type by calendar month. Defect types are stored in one table, defect transactions in another along with date etc. When I cross-tab the results, defect types that have no defects recorded against them appear as a blank (null) value. That...
23
6541
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 queue, grab it and run a system command. Now I want to make sure that system command doesn't hang forever, so I need some way to kill the command and have the worker thread go back to work waiting for another queue entry.
7
3913
by: Scott M. | last post by:
How can I disable the cross-site scripting check for one particular page of a site?
8
4855
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 I want to raise the ProgressChanged-event to update the DataGridView. It works fine when only one Progresschanged is fired, but at the second, third, fopurth etc it raises everytile a 'Cross-thread operation not valid"-exception on lmy...
1
2764
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 have a table containing records for each job done, the records contain date, employee name, job done (a code representing the type of job), cost code (another code), regular hours, and overtime hours. The tricky part is that more than one job can...
6
8632
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 tried, including my conclusions/assumptions (which i'll happily be corrected on if it solves my problem!): The requirement not to use a proxy means I can't use the synchronous
6
5481
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 suggestions are welcome. --
6
3988
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
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9272
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9211
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8218
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2748
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.