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

rg1117 (AT) hotmail (DOT) com wrote:
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

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

kevindotcar (AT) gmail (DOT) com wrote in message news:<1110389662.233785.273530 (AT) o13g2000cwo (DOT) googlegroups.com>...