Double Left Joins Form

[rg1...@hotmail.com]

Hi all,

I have a question regarding creating Left Joins. Suppose I have a
database with the following tables:

Person
{
(primary) id (Autonumber),
name Text
}

Activity
{
(primary) id (Autonumber),
name Text
}

Person_Activity
{
(Reference to Person.id) Person (Number),
(Reference to Activity.id) Activity (Number),
involved YES/NO
}

So basically, I have a list of people, a list of possible activities
that the people can perform, and list of activities that each person
performs (marked by a Yes in the 'involved' field).
I have a form which goes through each person, within that I want a
subform with a list of all the activities so that I can simply
identify them by ticking a checkbox, like this;

Activity1 []
Activity2 []
Activity3 [x]
...
etc,

The problem is that by default when I create a new person or activity,
there is no record in the Person_Activity table that matches with
them. I have to create that record explicitly. What I would like is
for the subform to give me a list of all activities even if none
exists in the Person_Activity table for that particular person. I have
tried using Left Joins, but the problem is I essentially need a double
left join for this to work, something that is not possible in the MS
ACCESS version that I have.

Can anyone provide a solution for this. Ideally, I looking for a pure
SQL solution, without any scripting of any kind. My plaform is Windows
XP Pro, MS Access 2002 SP3.

Any help is greatly appreciated.

Many Thanks,

RG

[kevin...@gmail.com]

Are you insistent about having the checkbox-scenario on the form? If
not, you could make your subform a "data entry" view and pretty much be
done with it; otherwise, yes, you will have to manage record insert
actions with a bit more logic.

Sticking with the idea of a data entry form (keep in mind, TIMTOWTDI);
I would
make a subform with edits and inserts enabled with these fields;

Person_Activity.Activity | [Person.ID]

Where [Person.ID] is invisible and defaults
to Form!Parent!Person.ID

...AND (this is the tricky part) ...

1. Person_Activity.Activity is a bound data field on
your subform that is multiple colums, with record
souce set as;
SELECT ID, Text from Activity

2. The Person_Activity.Activity column is defined as
multiple columns with the "bound" column being ID,
but the width of he column is 0 inches - this makes
your data entry form display the text of the Activity,
but it will actually store the numeric value.

HTH

KC

[rg1...@hotmail.com]

Hi there,

Thanks very much for your reply. Unfortunately, I am inisistent on the
checkbox scenarios. Simply because I think that this a better user
interface given the number of items I have to deal with.

Thanks,

Rishabh

kevin...@gmail.com wrote in message news:<1110389662.2...@o13g2000cwo.googlegroups.com>...