Joins in a Query

[Guest]

I have a table with membership information. I have several tables with
information on the rodeos that the members have attended. Not all members
went to all rodeos. I need to have a query that shows a list of all members
and which rodeos they attended. I put in my query the first and last name
from the membership table. Then I put in the location from the first rodeo
table. I have the join in the query as showing all records from membership.
It looks fine with only one table. It shows all members and shows a value in
the location field from the second table. It shows a blank if they did not
attend. My problem is when I add the second table. I get this message: The
SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)
I am new to this, how do I fix this? In the end I will have about 30 tables
in this query. Thank you

 

[Larry Daugherty]

Hi,

I' guessing but I believe you probably have problems at several levels.
When next you post you might include your table names and the names and
datatypes of the first several fields in each table.

At the simple level you've suggested to us, you'd start with two entities:
Cowboys and Rodeo Sites which can be represented in two tables: tblCowboy
and tblRodeoSite.

tblCowboy would have fields CowboyID, Autonumber; LastName, text; FirstName,
text; Middle, text; and any other fields of attributes that apply to a
cowboy.

tblRodeoSite would have fields RodeoSiteID, Autonumber; SiteName, text;
SiteAddress1, text; SiteAddress2, text; SiteCity, text; and so on. Notice
that you don't need several tables about the rodeos, you need and must have
only one.

To show what you want requires a many-to-many relationship: Many cowboys
attend many rodeos. This requies a Junction Table which will end up having
more data in it than the two main tables combined. Lets call that junction
table tblCowboyRodeo.

tblCowboyRodeo's fields will be: CowboyRodeoID, Autonumber; CowboyID, Long
Integer (called a Foreign Key here, it's the Primary Key of tblCowboy);
RodeoID, Long Integer (Foreign Key value tblRodel's primary key); you'll
need to add plenty of other fields to tell what went on there such as
Events, Awards, Winning$, etc.

As you can see, your application is going to center more on tblCowboyRodeo's
than on either of the "main" tables. You will perform some activieies based
on each one. In each case, the other "main" table will probably serve as a
lookup table for what you are doing, ie. If you are adding the names of
cowboys attending a particular rodeo you might open a form based on Rodeo,
find the one you want and then lookup the names of attending cowboys in
tblCowboy with a combobox to add their names without typing anything.

Now, to your issue of the report: You can now design reports to show every
Rodeo and every Cowboy who attended it or to show every Cowboy and every
Rodeo attended.


HTH