Ambiguous Outer Joins?

[lszuma]

Attached is a pdf of the query window showing the relationships and table structure; (sorry for the quality) the linkage is also permanent at the relationship window. I created a form (columnar) of Rooms; loaded a subform (columnar) of the projects; and then loaded the students (tabular) as a subform on the projects subform. The data entry is flawless; tabs through each field and form to form in sequence.

After entering several rooms data I tested it at the query level by loading the three tables: rooms, projects, and students, and the permanentely established linkage with junctions came in automatically. I thought I was home free--but when I run the query, I get zero records.

When I attempt various joins, thinking this will yeild all records from the many tables and their match, I get "ambiguous outer joins" and it says to run a separate query and add it to the SQL Statement?

Thanks for any help,

Almost funtional in Ann Arbor........

Oh, and thanks Pat Hartman for the tip on linkage -- although I may have screwed it up anyway.

 

[Pat Hartman]

If you are using a left join between tbl1 and tbl2, you also need a left join between tbl2 and tbl3. Outer joins constrain the joins that follow them. You could use an inner join between tbl1 and tbl2 and an outer join between tbl2 and tbl3 without a problem. If you must have an an outer join between tbl1 and tbl2 but you want an inner join between tbl2 and tbl3. In this case you would create a query for outer join and then join the query to tbl3 with an inner join.

 

[lszuma]

I give up!

Thanks, but feeling quite dejected about all this. Access has a way of stripping away any sensible form of intellilgence I have left. And speaking of left, right, outers........arhg! Although I understand the theory of joins and junctions, and relationships, putting them into practice is humiliatingly painful...are all multiple table joining this complicated? Let me do one-to-many and many-to-many all day long!

Initially, I did not set any join properties at the query level (nor did I set joins at the relationship window because I want to determine my output on the report based on the query). I simply requested all three tables (using the query wizard) in this order: rooms(tbl1), projects(tbl2), students(tb3). The query brought in the junctions and relationships between the three and their junctions as they were set in the relationship window in the following sequence: rooms, projects, projects-to-rooms junction, students, students-to-projects junction. I guess assuming this and the functionality of the form for data entry was not enough, as the result was zero records from the query.

So, reading from left to right for consistencey, I keep the arrows all flowing to the right but nothing. I give up!

Option 1: Tell my boss to stick the report and get used to excell
Option 2: Tell my boss he can have the report without students
Option 3: Quit my job and become a file clerk

I still think I'm missing something in the relationship window

 

[lszuma]

I scapped the whole many-to-may idea and linked my rooms to projects as one-to-many with integrity and cascade deletes. I then linked projects to students and one-to-many with integrity and cascade delete. If I deleta whole room, every project and every student goes. If I delete only a project in that room it will go with its students, leaving the other projects with their students and leave the room. If I delete a student, only students are deleted.

At the query level I chose join type two to show all ones even if there were no manys.

Who cares if the same project can be assigned to two rooms, there's still one room with many projects and even if the same student is in different rooms working of different projects, one can now be deleted in one room and still stay as a record in the other. I can delete one project in a room and all of its students and still have the room and other projects if there were any. I can delete only one student on a project and still have the project., etc.

The query works, the form works, the report is beautiful. I think I way over-analyzed my data...

Of course, it could all go to **ll tomorrow.....!

 

[Pat Hartman]

You have several other posts revolving around the same problem and you never answered the question I asked regarding the relationship of students to rooms. Your solution isn't correct since you do have many-to-many relationships. You just never finished defining them so that I could tell you how the relationship would work.

When posting messages to this fourm do not post questions in the references area. No one looks there for questions. Post your questions in the discussions area and stick to a single thread for a single problem.

 

[lszuma]

Yes, I realize I still have many-to-many's, but I am just happy that under the current structure I can produce the reports I want. Now that I am in the report phase--and the data entry phase for that matter--I can see the importance of sound tables and relationships. As I learn I can modify the design to rectify these issues. I am also happy that I can now left join other tables off the rooms, such as equipment, the tech who services it, and so on...

So, are the students important in relation to the room? Yes and no. They are only there because they are assigned to a project. And I am talking roughly 60 students and only a few work on different projects--so I make the exception and enter their name twice. At least with the current right joins from rooms to projects to students (both one-to-many), referential integrity cascade deletes, and join property 2, I can ensure their and their projects' existence in the room--even though there are many rooms.

Ideally, I would like a combo box lookup to tabel field on the projects table derived from a list of names queried from the student database, which currently does no exist, but I am working on that too. Our department is 10 or more peas all in different pods. I have the lovely task of tying as many of us together as possible (uh, and we don't even have a common shared folder, much less a drive on the server that we can all network to!) But it is rumored that we will soon have that folder so we can at least access a centralized database in that fashion. Me? I'm just an administrator that know a little bit about a lot of things in Access. It really is a wonderful tool with many capabilities to master.

Thank you for taking the time to address some of my issues. I do appreciate your comments and input. I also find some of the posts here quite useful and will learn to navigate the forums correctly.

I do have a specific question on that combo box. However, I will address that in forms (I think), as my field keeps coming up with the ID number instead of the list.

I love to hate Access.......thanks again.

 

[Pat Hartman]

so I make the exception and enter their name twice

- bad move. It is better to normalize before you get a lot of data in the database. It only gets harder.

 

[lszuma]

I already know.......! I'm sunk...hmmm...file clerk is sounding real good right now,,,,,,,,,,

So the scenario begins again with many rooms, in those rooms can be many projects, that same project can be in many rooms. Many students work on many projects in many different rooms. Also off this same structure is a many to one linking the project to a specfic project grant information string such as number, sponsor, end date, and budget (the combo box for a choice since I know that information and can create a table to select the correct grant with the appropriate project). For example, we get a 6M grant from NASA that has 10 projects which may be assigneed to different rooms and each has students, but the grant remains the same.

Goal: track room allocation based on its use and purpose (placement of the project is determined by the rooms capability (detiled in the room description with acronyms--no table necessary here). I would also like this database to track not only the labs rooms, but student office assignments (select value list for room type: lab, student office, other--to include the storage closets in the description--piece of cake to select query for reports based on type).

I do have a table for student information, in infancy, with at least a list of names and some other student data such as addreess and degree sought(separate linked table since there are usually many degrees), which will eventually roll into an alumni record by selecting alum (check box) rather than student. Of course I could select students by Yr Graduate is not Null, but I like the check box feature in some instances and if not over used. The larger end of this database is really a record database rolling students to alums, and includes faculty and donors, or anyone who wants to or should receive our newletter or other mailings (students and faculty can eventually become donors), or a repository for names and addresses, and includes various tables linked to store relevant data such as faculty awards and service functions, or donor giving history. So this larger database is the trunk.

What I have describe thus far is what I have alread done and I am begining to enter the data as it is known (most of which is paper records, word files, excel files, or just someones scratched notes). That was the easy part, lots of simple one's to many's and I am able to store records and produce multitudes of reports from this single soure(s).

The room allocation is just a larger, more complicated branch that I have complilcated further by not starting it from the true source. It will probably take me months to pull it all together but I know when it is right it will be something great. But I simply do not have the knowlegede to link this monster. So I copy the database and contiue to test myself with linkage of this particular aspect. What I described to you thus far about room allocation is a structure isolated from the tree and although it works, and for now it will suffice with reporting data for its intended and immediate purpose, it belongs in the forest.

I get it. I just can't "get" it.

 

[Pat Hartman]

You added a lot more information but never answered the question - Does a student work on a specific project in only ONE room or may he work on the project in ANY room assigned to the project?
tblProjectRooms
ProjectRoomID
ProjectID
RoomID
tblStudentProjects
StudentProjectID
StudentID
ProjectID (if it doesn't matter which room)
or
ProjectRoomID (if it does mattter which room)

 

[lszuma]

NO. The student can work on any project in any room. As a general rule he/she is assigned there because the faculty (faculty choice is a drop down static list of names) awardee of the project/grant is their advisor and they hire them (to learn and supplement their income) or they are there as part of a department "course" project.

 

[Pat Hartman]

You don't seem to be "getting" the question. Once the student is assigned to a project in a specific room can he work on the project in other rooms? Look at my suggestion for tblStudentProject. The answer to this question determines which relationship applies. The relationship is Student-Project if it doesn't matter which room he works in but the relationship is Student-ProjectRoom if it does matter.

 

[lszuma]

Yes. The student (and the project) can be worked on in another room--depending on the specifics of the task or experiment. For example, certain rooms may have capabilities that require the project (and the stundent) to utilize the equipment in that room to perform the task or experiment (vibration studies, wind resistence, ect.). However, that same student may also be assigned to another project.