I need some help figuring this one out. I'm sure I did something like it
awhile back
but I can seemed to find that database or remember how I did this. Any way,
the
database tables are currently as follows...
tblClients tblProperties tblProjects
ProjectID
PropertyID 1----------8 PropertyID
ClientID 1----8 ClientID |-8 ClientID
Client | PropertyLocatio n | Project
|______________ ____________| ...
The form I am working on is a project worksheet. The control source a query
of the
projects table. At the top is a combo box that is to contain all of the
client names
from tblClients. Once the user selects a client, a second combo box becomes
active
for the user to select a property. cboProperties is to only contain the
properties
associated with the selected client. At this point, the user can enter all
of the
appropriate project information.
As you can see from the diagram above, there is a one-to-many relationship
between
tblClients.Clie ntID and tblProperties.C lientID; a one-to-many relationship
between
tblProperties.P ropertyID and tblProjects.Pro pertyID and I currently have a
one-to-many relationship between tblClients.Clie ntID and
tblProjects.Cli entID. I am
thinking that putting ClienID in tblProjects is redundant but I don't think
that this
is necessary creating my problems. I could be wrong, however, but I don't
want to delete it from the table just yet if it is not necessary.
How should I go about setting up this forms RecordSource so that I can
accomplish this. I have tried setting it as a query the uses the three
tables with their inherent relationships but I do not get all of the
records...
SELECT tblClients.Clie ntID, tblClients.Clie nt, tblProjects.Pro jectID,
tblProjects.Pro ject, tblClientProper ties.PropertyID ,
tblClientProper ties.PhysicalAd dress
FROM tblClientProper ties INNER JOIN (tblClients INNER JOIN tblProjects ON
tblClients.Clie ntID = tblProjects.Cli entID) ON (tblClients.Cli entID =
tblClientProper ties.ClientID) AND (tblClientPrope rties.PropertyI D =
tblProjects.Pro pertyID);
I have tried to alter the join properties on each of the joined fields but
if I change them I get an error telling me that I have ambiguous outer
joins.