Outer Join Problem

[CarlRostron]

I have two tables.

tblTrafficFormat (This tables lists all the possible formats available):
TrafficFormatID, Format, SummaryFormat
(I am happy this table has the correct info within it)

tblTrafficNational (This table lists all the volumes by Office where each volume is a particular format)
TrafficID, OfficeID, TrafficFormatID, DateID, TrafficVolume
(I am happy this table has the correct info within it)

Problem:
I want to run a query which gives me the Summed Volumes by Office by Format from tblTrafficNational. The problem is that there are offices with volumes in this table which dont have ALL format listed from the traffic table. I want to include ALL formats in my query even if they sum to zero.

Currently I am running the following LEFT OUTER join but it doesnt work for me:

This is the SQL I am running:

SELECT tblTrafficNational.OfficeID, tblTrafficNational.TrafficFormatID, Sum(tblTrafficNational.TrafficVolume) AS SumOfTrafficVolume
FROM tblTrafficFormat LEFT JOIN tblTrafficNational ON tblTrafficFormat.TrafficFormatID = tblTrafficNational.TrafficFormatID
GROUP BY tblTrafficNational.OfficeID, tblTrafficNational.TrafficFormatID
HAVING (((tblTrafficNational.OfficeID)=7617));

This is the output I am getting but I want to see ALL formats and zeros where appropriate and there are 14 formats in the formats table not 7 that are shown below:
Query3OfficeIDTrafficFormatIDSumOfTrafficVolume1110063Delivered Walksorted Letters35199611110063Delivered Walksorted Flats8704101110063Delivered Sequenced Letters26599101110063Delivered Manual Letters1339561110063Delivered Packets17869101110063Delivered Special Delivery1910331110063Delivered Walksorted Packets1185504

The current tblTrafficFormat Table is:
tblTrafficFormatTrafficFormatIDFormatSummary FormatWeighting1Delivered Walksorted LettersLetters0.372Delivered Walksorted FlatsFlats0.533Delivered Sequenced LettersLetters0.314Delivered Manual LettersLetters0.535Delivered FlatsFlats0.766Delivered PacketsParcels3.077Delivered RM TrackedTracked7.668Delivered Special DeliverySD6.919Delivered Walksorted PacketsParcels1.9410LettersLetters
11FlatsFlats
12ParcelsParcels
13SDSD
14TrackedTracked


Please help.

 

[plog]

HAVING (((tblTrafficNational.OfficeID)=7617));

When you apply criteria to the LEFT JOINED table(tblTrafficNational), you have defeated its purpose and in essence turned it into an INNER JOIN. The LEFT JOIN says 'show all from table A and if table B doesn't have data, show the record anyway'. The HAVING clause says 'only show records from Table B with this criteria.' You can't have it both ways, so the HAVING clause undoes your LEFT JOIN.

I would need sample data to help you get what you want, because like the query, your SQL makes it unclear to me what results you want. Post some sample data from both tables and then what you expect as the result and I can help you more specifically.

 

[CazB]

I think the problem is you've got the join the wrong where round... it should be

FROM tblTrafficNational LEFT JOIN tblTrafficFormat

rather than

FROM tblTrafficFormat LEFT JOIN tblTrafficNational

 

[CarlRostron]

I have now uploaded some decent sample data with the formats and data volumes for the two tables as required.

Ok so here is my tblTrafficFormat table headings in my DB are TrafficFormatID, Format, SummaryFormat


1 Delivered Walksorted Letters
2 Delivered Walksorted Flats Flats
3 Delivered Sequenced Letters Letters
4 Delivered Manual Letters Letters
5 Delivered Flats Flats
6 Delivered Packets Parcels
7 Delivered RM Tracked Tracked
8 Delivered Special Delivery SD
9 Delivered Walksorted Packets

The tblTrafficNational table has the following headings TrafficID, OfficeID, TrafficFormatID, DateID, Traffic Volume
I have included an Excel file of some Data as too much to put here.

So in Essence the Repoprt I want is something like this, Headings OfficeID, TrafficFormatID, SumOfTraffic

111063 Delivered Walksorted 10,000
111063 Delivered Walksorted Flats 120,000
111063 Delivered Sequenced Letters 130,000
111063 Delivered Manual Letters 26,000
111063 Delivered Flats 134,000
111063 Delivered Packets 0
111063 Delivered RM Tracked 500
111063 Delivered Special Delivery 0
111063 Delivered Walksorted Packets 0

These numbers are completely made up I'm just showing the format is Summed Volumes and the ALL formats are shown and where no formats exists for that Office in the tblTrafficNational table it displays a Zero or at least a null for the summed volume.

NB. I have tried switching the joins around and that has not worked for me..

 

[plog]

I have no idea what is what. Can you post and simply label 3 sets of data:

A. Starting sample data from tblTrafficFormat

B. Starting sample data from tblTrafficNational

C. Expected output of your query, based on A & B.

Be sure to include labels to identify what is what (A, B & C) and field names atop your data and delimit your data fields with commas:



A
TrafficFormatID, Format, SummaryFormat
1, Delivered, Walksorted Letters
2, Delivered, Walksorted Flats Flats

 

[CazB]

something like this?

 

[plog]

Nope. I asked for 3 sets of data, clearly marked. That's one set and I have no idea which set it represents.

 

[CazB]

In the spreadsheet you attached, the link between the formats table and the National table has to be made on the 'wrong' field as the National table has the description in it rather than the ID for the format... but I built that query like below:

SELECT tblTrafficFormat.Format, Nz([OfficeID],"None") AS Office, Sum(Nz([TrafficVolume],0)) AS SumOfTraffic
FROM tblTrafficNational RIGHT JOIN tblTrafficFormat ON tblTrafficNational.TrafficFormatID = tblTrafficFormat.Format
GROUP BY tblTrafficFormat.Format, Nz([OfficeID],"None")
HAVING (((Nz([OfficeID],"None"))="none" Or (Nz([OfficeID],"None"))="1110055"));

screen shot of design view attached

 

[CazB]

plog... I was asking the OP if that's what they were after, based on the data supplied... not trying to answer your question

 

[plog]

Sorry, too many 'Ca' names.

 

[CarlRostron]

Thanks for looking into this btw, I do appreciate it.

I have reattached the spreadsheet so it is clear for you in the format A,B & C as you have asked for it.

It should make sense now let me know if it doesnt.

 

[CarlRostron]

Just seen the previous posts, yes Caz that's what I am after in post #6 although it is lacking Walksorted Packets? Maybe i can take anther look at your query. How was your different to mine?

 

[plog]

You made an error in your data:

Tab B, TrafficFormatID

This column should be numeric shouldn't it? It contains the Format value, not TrafficFormatID. Can you fix please?

 

[CarlRostron]

This is how it came out of Access as the Field is a 'Lookup' from another table and hides the column ID.

 

[plog]

What data type is TrafficFormatID in tblTrafficNational?

 

[CarlRostron]

TrafficFormatID is a Lookup Field into the tblTrafficFormat Table and is an integer but displays the Looked up value and is bound on column 2 which is the Format column in the tblTrafficFormat Table

I have included the Table Deisgn as an image here.

 

[CarlRostron]

The TrafficFormatID fireld in the tblTrafficNational Table is an Integer. It only shows text because it is a lookup from the other table and hides the Unique Key column.

I have attcahed the table deisgn here for the tblTrafficNational Table

 

[plog]

Then I would need data that matches that datatype.

 

[CarlRostron]

No problem I have made the change and reattached.

Please let me know what you think and where maybe going wrong or if there is anything else you require.

 

[plog]

The real issue is that you want every OfficeID/TrafficFormatID permutations to exist, not just every TrafficFormatID. For that you need more than a table with every TrafficFormatID, you need a datasource that has every OfficeID/TrafficFormatID permutation.

For that you will need to use this query:

SELECT tblTrafficNational.OfficeID, tblTrafficFormat.TrafficFormatID
FROM tblTrafficNational, tblTrafficFormat
GROUP BY tblTrafficNational.OfficeID, tblTrafficFormat.TrafficFormatID;

Paste that into your database and name the query 'sub_OfficeFormats_1'. It will be the table to which your LEFT JOIN your data. Then to get your results, use this query:

SELECT sub_OfficeFormats_1.OfficeID, sub_OfficeFormats_1.TrafficFormatID, Sum(tblTrafficNational.TrafficVolume) AS Volume
FROM sub_OfficeFormats_1 LEFT JOIN tblTrafficNational ON (sub_OfficeFormats_1.TrafficFormatID = tblTrafficNational.TrafficFormatID) AND (sub_OfficeFormats_1.OfficeID = tblTrafficNational.OfficeID)
GROUP BY sub_OfficeFormats_1.OfficeID, sub_OfficeFormats_1.TrafficFormatID;