Left Join Issues

[Rustin788]

Hey,

I am having issues getting a left join to work properly. I created a table that simply list our 7 Product Classes and I am trying to join that to one of our sales databases via the Product Class field in that table. When I do this it acts line an inner join because there is no data in the second table to fill it.

SELECT Table2.Field1, Table1.AllProductClass, Sum(Table2.NetSalesValue) 
AS SumOfNetSalesValue, DatePart('yyyy',[InvoiceDate]) AS [Year]
FROM ProductClass LEFT JOIN Table2 ON Table1.ProductClass = Table2.ProductClass
GROUP BY Table2.Field1, ProductClass.AllProductClass, DatePart('yyyy',[InvoiceDate])
HAVING (((Table2.Field1)="Customer1") AND ((DatePart('yyyy',[ Date]))=2016));

I simplified the code a little bit.



This table eventually is combined and compares 2015 vs 2016.

There are several post on Google about this but none have helped me so far.

Thanks for taking a look.

[JoeM]

I see three tables referenced in the SQL code, ProductClass, Table1, and Table2, but only Table1 and Table2 are included in the Join statement.
That seems a bit odd to me.

Also, when you have a query involving multiple tables, it is best to qualify all the fields being used in it. In your code, I cannot tell which table [InvoiceDate] or [Date] come from.

It might also be helpful if you could post a small sample of data from each of your tables, along with what you want your expected output to look like, so we can see what you are working with, and what your end goal is.

[Rustin788]

I apologize that was from my poor attempt at simplifying the code. There are only two tables, I'll post the full code below:

SELECT DatePart("yyyy",[InvoiceDate]) AS CalendarYear, dbo_ArTrnDetail.Customer, dbo_ArTrnDetail.ProductClass, 
Sum(dbo_ArTrnDetail.NetSalesValue) AS SysproSalesFROM ProductClass 
LEFT JOIN dbo_ArTrnDetail ON ProductClass.AllProductClass = dbo_ArTrnDetail.ProductClass
GROUP BY DatePart("yyyy",[InvoiceDate]), dbo_ArTrnDetail.Customer, dbo_ArTrnDetail.ProductClass
HAVING (((DatePart("yyyy",[InvoiceDate]))=IIf(DatePart("m",Now())>3,DatePart("yyyy",Now()),DatePart("yyyy",Now())-1)) 
AND ((dbo_ArTrnDetail.Customer)=[Forms]![BusinessReviewSalesInformation]![CustomerName]));

The final output should look something like below. This report is used by someone that creates Business Review Powerpoints for customers and she gets her data from the table. I know I can probably easily have it show the years vertically with no issues but this format makes it much easier for her. This table below is essentially combining 4 of the above queries. The issue comes in when someone stops purchasing or starts purchasing from a new product class.

Customer	ProductClass	LY-ProdClassSales	LY-PaperSales	LY-MixPerc	TY-ProdClassSales	TY-PaperSales	TY-MixPerc
ABC	CPT	$100	$100	19.00%	$100	$100	16.00%
ABC	HWT	$100	$100	75.00%	$100	$100	26.00%
ABC	SWT	$100	$100	4.00%	$100	$100	4.00%

Thanks for the quick reply.

Edit**
The output for the SQL Code should be very simple with just Year, Customer, ProductClass, and Sales with any product with 0 sales still being represented.

[JoeM]

Can you post data samples from the two tables that make up the expected you posted?
I like to try to recreate the whole scenario on my side, so it is helpful to see that so I don't make any assumptions that are incorrect.

[Rustin788]

Sure. The Product Class table is just a simple table like this:

ID	AllProductClass
1	CPT
2	JRT
3	PRT
4	HHRT
5	FLD
8	HWT
9	SWT

The trnDetail table is a monster with 79 columns. Here is what the particular fields I'm pulling from look like:

CalendarYear	Customer	ProductClass	SysproSales
2016	ABC	CPT	100
2016	ABC	HHRT	100
2016	ABC	HWT	100
2016	ABC	JRT	100
2016	ABC	PRT	100
2016	ABC	SWT	100
		FLD

Essentially I just need the table to show the FLD product class like I added in Red above.

Thanks for the help.

[JoeM]

This should do that:

SELECT trnDetail.CalendarYear, trnDetail.Customer, ProductClass.AllProductClass, trnDetail.SysproSales
FROM ProductClass 
LEFT JOIN trnDetail 
ON ProductClass.AllProductClass = trnDetail.ProductClass;

[Rustin788]

That's pretty much what I have isn't it? Even when I take away the Customer and Year Criteria is is not pulling in the empty Product Class fields.

[JoeM]

No, you are pulling Product Class from the wrong table. In your SELECT clause, you are trying to pull it from the table it which it does not exist (dbo_ArTrnDetail.ProductClass) instead of pulling it from the table where it does exist (ProductClass.AllProductClass).

Remember, in a left join, it returns all records from Table1, even if they do not have a match it Table2. So it DOES matter which table you return the field you are matching on from. Always pull from the one where all records are being returned.

[Rustin788]

I'm still a little confused. I'm not seeing the difference between what I have and what you wrote out as far as the left join goes.

SELECT DatePart("yyyy",[InvoiceDate]) AS CalendarYear, dbo_ArTrnDetail.Customer, dbo_ArTrnDetail.ProductClass, Sum(dbo_ArTrnDetail.NetSalesValue) AS SysproSalesFROM ProductClass 
LEFT JOIN dbo_ArTrnDetail ON ProductClass.AllProductClass = dbo_ArTrnDetail.ProductClass
GROUP BY DatePart("yyyy",[InvoiceDate]), dbo_ArTrnDetail.Customer, dbo_ArTrnDetail.ProductClass
HAVING (((DatePart("yyyy",[InvoiceDate]))=IIf(DatePart("m",Now())>3,DatePart("yyyy",Now()),DatePart("yyyy",Now())-1))  AND ((dbo_ArTrnDetail.Customer)=[Forms]![BusinessReviewSalesInformation]![CustomerName]));

 SELECT trnDetail.CalendarYear, trnDetail.Customer, ProductClass.AllProductClass, trnDetail.SysproSales
FROM ProductClass 
LEFT JOIN trnDetail  
ON ProductClass.AllProductClass = trnDetail.ProductClass;

**Just reread. I've tried both product classes in the select ,separate and even together, and nothing seems to want to pull in the blank fields.

[JoeM]

Can you stick to the fields listed in the query examples you made (InvoiceDate is not part of it)?
It is difficult to interpret results if you do not have an apples-to-apples comparison.

Note. It isn't just in the SELECT clause, but in the GROUP BY clause to. Access does NOT like it if you try to Group By NULL/missing fields. You should only Group By fields that exist in your table where you are returning all records.

My suggestion is to take the code that I gave you that works, use that as your starting point and add to it, one piece at a time until you get you want. Check after each step to make sure everything is still as it should be. If it is not, then you know the issue is with the last step you did.

[Rustin788]

I finally figured out. I believe because the TrnDetail table has transaction data for every single customer in order for the join to work properly I needed to have a query pull just one customers info before attempting to connect it to the ProductClass table. Once that query was completed I just needed to create another query and do the join on that one.

Thanks for your help and you patience .

[JoeM]

I still think you can get it to work the other way, if you follow the rule I gave you, about not using the fields from the second tables in your Group By clause.
But if you a solution that works for you, go with it!