I am trying to design a query that is based on 6 tables. What I want to achieve with the query is a weekly schedule of tests that go on at this facility. I want the query to show all of the records that match the criteria I have set forth. As it is the query returns most of the records. No wrong records are returned, just some are missing. I am attempting to get a better understaning of things. It seems that the query only returns all records if there is anyting in all fields. If there isn't it just skips over those fields. So if there is the primary table doesn't have an entry for the forgien key then it skips those records. The reason some fields aren't filled out are because as an example one of the fields is called "Tech", which is a field where the technician working on that record. Until the supervisor assigns it the field is blank. If he doesn't fill in the blank before running the query the record will be skipped over. My question is if there is a better way to avoid this than my plan of action. My plan of action is that when the record is created that I put a place holder in the record until my supervisor updates the record with the "Tech" of his choice.
Multiple table query, how to get all records needed
- Thread starter Dinger_80
- Start date
Rx_
Nothing In Moderation
- Local time
- Today, 11:55
- Joined
- Oct 22, 2009
- Messages
- 2,803
Perhaps a couple of things are going on. Let me assume you are just starting out with queries.
Do you use outer joins? The straight line join only returns records that have a match in two tables. A Left or Right outer join returns all the records from one table, then only recores that exist that match. This for example can Return all of the Jobs Started, and only the records for Jobs Completed. The Jobs completed would just show up blank next to the Jobs Started field.
http://www.access-programmers.co.uk/microsoft-access-tutorials/
Don't know if this site covers the outer joins.
http://www.btabdevelopment.com/ts/qt
Here are some quick tutorials.
You should also be close to being able to post a simple database with your query.
Or, get the 30 day trial of SnagIT and post a screen shot of the query and the output.
Do you use outer joins? The straight line join only returns records that have a match in two tables. A Left or Right outer join returns all the records from one table, then only recores that exist that match. This for example can Return all of the Jobs Started, and only the records for Jobs Completed. The Jobs completed would just show up blank next to the Jobs Started field.
http://www.access-programmers.co.uk/microsoft-access-tutorials/
Don't know if this site covers the outer joins.
http://www.btabdevelopment.com/ts/qt
Here are some quick tutorials.
You should also be close to being able to post a simple database with your query.
Or, get the 30 day trial of SnagIT and post a screen shot of the query and the output.
I am not horribly new just not well versed with using SQL to design them. My database uses every query type and through some trial and error I usually get the results that I am looking for. This one was however totally escaping me. I think that your idea hit the nail on the head. I looked into Left and Right JOIN's and from what I have gathered will work for what I need. I also think it may help me condense some other queries into one. I thank you for pointing me in the right direction, it is very appreciated.
