Create a Full Outer Join Query for Multiple Tables

Question

Create a Full Outer Join Query for Multiple Tables

Anonymous

Hello,

Hoping I can get some help with this. I have a database containing sales and marketing data for a large company. This data is all broken down into weeks, so I have weekly profit numbers, weekly impression numbers, weekly costs, etc for each individual store the company owns. I am trying to create a query which will allow me to get a total number of impressions for each store each week. There are different types of impressions, which are each broken out into the following individual tables:

Table Name	tblAllDI	TblRadio	tblPM	tblAdWords	tblAltPubs	tblOOH	tblStoreList
Fields	Impressions	IMP	Impressions	IMP	IMP	IMP
	StoreID	StoreID	StoreID	StoreID	StoreID	StoreID	StoreID
	Date	Date	Date	Date	Date	Date

I also included the other data I'd like to include in the query (StoreID and Date)

I was planning to pull the data, linked by the store ID and the date, and create a calculated field adding the 6 types together.

The main problem I am having is that I need to pull ALL the data, not just what matches up. Most stores will not run all 6 types of advertising at once, so when I pull the query a majority of my data is missing.

I believe I need to create a Full Outer Join Query in Access, or at least do something similar because I understand Access does not currently have that capability. I was looking online, but could only find useful information for combining 2 tables. I'm hoping someone here can help me out with doing this for multiple tables.

Please let me know if I need to clarify anything...I was having a difficult time explaining the problem.

Thank you in advance!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

The main problem I am having is that I need to pull ALL the data, not just what matches up. Most stores will not run all 6 types of advertising at once, so when I pull the query a majority of my data is missing.

That sounds more like a LEFT OUTER JOIN form tblStoreList to each of the others. A full outer join is one which works in both directions simultaneously, which I don't think is what you are looking for here. A LEFT OUTER JOIN would return the base data, but you will almost certainly get duplication, as not all referencing tables will have exactly the same number of rows per store per week.

John's suggestion of a UNION ALL query is more likely to give you what you want, in which each part of the UNION ALL operation sums the impressions for a specific type of advertising medium per store, then using this as the basis for a crosstab query. In each part of the UNION ALL operation you'd return the type of medium in a column as a constant, which would then be the 'column headings' for the crosstab query, while the StoreID and Date would be the 'row headings'. The total number of impressions in the last column would simply be a summation of the values in the impressions column returned by the UNION ALL query.

Answer 2

Ok, so basically I want the columns in the final output to be as follows:

StoreID, Date, tblAllDI_Impressions, tblRadio_IMP, tblPM_Impressions, tblAdWords_IMP, tblAltPubs_IMP, tblOOH_IMP, TotalImpressions (This last one would be a Sum Field)

I just don't know how to make it work without losing or duplicating data....

Answer 3

A Full Outer Join will be a major pain to implement and will probably give lousy performance even if it can be done!

I'd suggest instead trying a UNION query stacking the tables end to end rather than side to side. It's not clear to me what final output you want; you might need to base a Crosstab query on the UNION. If the data is in SQL/Server or another "big iron" database you might want to design a stored procedure to extract the data you need.

Share via

Create a Full Outer Join Query for Multiple Tables

3 answers