Software

Ask something
Discussion Locked

Query probs using more than two tables.

0 helpful
5 replies

by froehlke-km

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
When creating a query that requires data pulled from more than two linked tables, I get all kinds of funny problems. If any of the records in the linked tables have blank fields that I want displyed in the output, the record simply isn't included. I'd like the record from the primary table included even if the data from the linked tables is missing. Some is better than none. The problem is only records with all the fields filled in all the linked tables appear! I assume this is a limitation of the query tool when it converts it into SQL. I know just enough SQL to get by and add a little extra here and there. I probably need to start getting into SQL scripting a bit deeper, as what they want is outrunning what I'm able to provide. I've temporarily overcome this problem by filling otherwise null fields with "N/A" and providing dummy linked records that would otherwise not need to be there. This way, all records in all linked tables have a linked record in the others and all fields are filled. Now, instead of being blank, null fields say "N/A". It's cumbersome and creates a lot of waste data, but it fixed the problem. Now, will someone please tell me how to do it right? If someone should happen to go into the tables and accidentallydelete one of the dummy records because it doesn't look right, it will screw everything up! I should add that I'm a PC tech/Network administrator, not an Access developer. I don't have any formal training or resources outside of the help files, so an obvious problem to others may escape me. You have to be sharp at a bit of everything these days it seems.

5 replies

by jboardman

You need to set up a left or right join relationship within the query. In SQL, it looks like this:
SELECT DISTINCTROW [table1].[field] FROM [table1] LEFT JOIN table2 ON [table1].linkkey = table2.linkkey WHERE (where clause);

table1 is the tableyou want to show ALL the records from and table2 is the table from which you only want records that match anything in table1.

This should also solve the problem with the blank fields.

You may find, if it's a complex query, that you'll have to break it into sub-queries doing one or two joins and filters on the way up. Let me know if you have a problem.

by froehlke-km

Thank you! I knew it had to be something simple. It didn't even strike me to click the relationships in the query window. I'll be an Access guru yet!

by rashmigopinath

You would need to use an 'Outer Join Query' for your case.
An outer join would return all the records that satisfy the join condition and also the records from one table for which no records from the other table would satisfy the join condition.
To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, the query would return null for any select list expressions containing columns of B.

by froehlke-km

Thank you! I knew it had to be something simple. It didn't even strike me to click the relationships in the query window. I'll be an Access guru yet!

This discussion is closed to new replies.