If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Joins in a Query
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 |
#2
|
|||
|
|||
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 -- -Larry- -- "B Earl" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return repeats info in "8s" | Joy Rose | Running & Setting Up Queries | 14 | October 13th, 2004 10:07 PM |
sql query syntax - joins without 'join' syntax | djc | Running & Setting Up Queries | 4 | September 10th, 2004 06:29 PM |
Show records from query 1 or query 2 depending | Running & Setting Up Queries | 1 | September 10th, 2004 12:35 AM | |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |