Multi table Query

[kanjeep]

I have a query that includes 4 different tables. It only shows info for the Sales Orders that have information in all four tables.
Is there a way to get it to show any information available? For instance, if it only has info in two of those tables, it would show that info and the fields for the other two tables would be blank?

 

[John_W]

You could add in a master table which shows every possible value of your key field. Like maybe if it's a date, have a calendar table which contains every date you're interested in and join this to each of the other tables such that the query displays every record in the calendar table and any matching records in each of the others. (in the query builder, double-click on the line representing the join between tables to see the join options.)

 

[kanjeep]

The key field is the Sales Order so that isn't going to work. There are WAY too many variables.

 

[boblarson]

You could add in a master table which shows every possible value of your key field.

Sorry, but very bad idea. There is no need. All that you have to realize is that you aren't using the correct join if you have that occuring. You are currently using an INSIDE JOIN which means that you will return any records if there are records that match a key field on both sides.

What you need is an OUTER JOIN which you can get by right-clicking on the link between the tables and select JOIN TYPE and then change it to

Show all records from TABLE A and only those records that match in TABLE B.

Start with the main table and you will need to either change the join to OUTER for all 4 tables, or do a separate query (you can do it with one SQL statement, but I'm not good at that) to first do the OUTER JOIN and then take that query and match it up with the other tables on an INSIDE JOIN.

 

[Simon_MT]

One way is to split your query for each of the 4 tablles. On these queries you start on:

Sales_Order Left Join on Table1
Sales_Order Left Join on Table2 etc

always putting the Sales_Order into the query. The result will be every Query will be populated with the Sales Order form the Sales Order table and you simply add the information that you need from each table.

If the record does not exist all the fields except the Sales Order will be empty.

ie. You start with the pivotal field ensure that it is in all the queries and deal with the unpopulated fields. You could say for the numeric fields

if(isNull([Table1].[NumField]),0,[Table1].[NumField])

Simon

 

[kanjeep]

That worked Bob. its giving me all of the records for the first two tables just like I was needing. THANKS everyone for your input!!!!