Help: Do I need a "join" table?

I guess I don't really understand Join Tables (or maybe Portals).

Here is a simplified version of my DB, and my problem.
ANY help would be greatly appreciated, as always!

Table 'EVENT' with fields:
pkEvent_ID
EventDate
EventCity
Worker1 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker2 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker3 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker4 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)

It is populated with approx 10 'events' (records 1-10).

Table 'WORKERS' with fields:
pkWorkers_ID
fkEvent_ID
WorkersName
WorkersSS

It is populated with approx 10 'workers' (records 1-10).

PROBLEM:
For each Event Record I want to choose the 4 workers for that event via
popup menus VL.
So far so good.

BUT then I want the WorkersSS to appear next to the Workers Name.

Should this really be done with a join table, or a portal, or both?
How would I go about that?

Table 'EventWorkersJoin'
with fields:
pkEventWorkersJoin_ID
fkEvent_ID
fkWorkers_ID
Worker1 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker2 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker3 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)
Worker4 (from Value List from 'Workers' Table: pkWorkers_ID, WorkersName)

Then place a portal or the Join Table on the Event Layout?

Thanks
JP

Re: Help: Do I need a "join" table?

Jonathan Peirce wrote:

> I guess I don't really understand Join Tables (or maybe Portals).

You can relate relate 2 tables directly it's 1:n, i.e. one record in the
parent table has one or more child records.

In your case, workers can be assigned to many events and events are
given many workers (many meaning more than one). The relation is n:m.
Here's where you need a join table.

In the join table, every record is one assignment of one worker to one
event. The join tables need two fields only, eventID and workerID.

You relate the join table to the event table via enventID and to the
workers table via worderID.

Now you can use portals to assign workers to events or events to
workers. There's no more need for those "Worker1" etc fields in the
event table.
--
http://clk.ch

Re: Help: Do I need a "join" table?

On Nov 30, 2:22 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> Jonathan Peirce wrote:
> > I guess I don't really understand Join Tables (or maybe Portals).
>
> You can relate relate 2 tables directly it's 1:n, i.e. one record in the
> parent table has one or more child records.
>
> In your case, workers can be assigned to many events and events are
> given many workers (many meaning more than one). The relation is n:m.
> Here's where you need a join table.
>
> In the join table, every record is one assignment of one worker to one
> event. The join tables need two fields only, eventID and workerID.
>
> You relate the join table to the event table via enventID and to the
> workers table via worderID.
>
> Now you can use portals to assign workers to events or events to
> workers. There's no more need for those "Worker1" etc fields in the
> event table.
> --http://clk.ch

Self-Join/Equijoin and you'll be able to portal records from within
the same table to a related record.

In the relationships graph, just make another occurance of the table
and link any way you want/need and then go to the layout you need and
drop a portal showing records from TABLE_NAME 2.

Good luck!

Re: Help: Do I need a "join" table?

On 2007-11-30 02:22:48 -0500, clk@freesurf.ch (Christoph Kaufmann) said:

> Jonathan Peirce wrote:
>
>> I guess I don't really understand Join Tables (or maybe Portals).
>
> You can relate relate 2 tables directly it's 1:n, i.e. one record in the
> parent table has one or more child records.
>
> In your case, workers can be assigned to many events and events are
> given many workers (many meaning more than one). The relation is n:m.
> Here's where you need a join table.
>
> In the join table, every record is one assignment of one worker to one
> event. The join tables need two fields only, eventID and workerID.
>
> You relate the join table to the event table via enventID and to the
> workers table via worderID.
>
> Now you can use portals to assign workers to events or events to
> workers. There's no more need for those "Worker1" etc fields in the
> event table.

Christophe:
thanks!
JP

Re: Help: Do I need a "join" table?

On 2007-11-30 19:17:55 -0500, "wilsonistrey@gmail.com"
said:

> On Nov 30, 2:22 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
>> Jonathan Peirce wrote:
>>> I guess I don't really understand Join Tables (or maybe Portals).
>>
>> You can relate relate 2 tables directly it's 1:n, i.e. one record in the
>> parent table has one or more child records.
>>
>> In your case, workers can be assigned to many events and events are
>> given many workers (many meaning more than one). The relation is n:m.
>> Here's where you need a join table.
>>
>> In the join table, every record is one assignment of one worker to one
>> event. The join tables need two fields only, eventID and workerID.
>>
>> You relate the join table to the event table via enventID and to the
>> workers table via worderID.
>>
>> Now you can use portals to assign workers to events or events to
>> workers. There's no more need for those "Worker1" etc fields in the
>> event table.
>> --http://clk.ch
>
> Self-Join/Equijoin and you'll be able to portal records from within
> the same table to a related record.
>
> In the relationships graph, just make another occurance of the table
> and link any way you want/need and then go to the layout you need and
> drop a portal showing records from TABLE_NAME 2.
>
> Good luck!

Thanks!
Now that I have it working with 2 tables, I'll try it with one.
JP