Help! Left join driving me crazy!!

[keyel1971]

Can't figure this one out. I have a left table with field "Product". I have a right table also with field "Product", but also many other fields.

If I left join on "Product", I want all products from left table to show. If no match in right table, I want nulls. Has worked a dozen times before. But now, it's leaving out one product for some reason.

Here's the sql:
SELECT tbl_EMSetal_ProdRef.Product, Count(tbl_pending_queue_historical.cur_amount) AS [Count], Sum(nz([cur_amount],0)) AS Amount
FROM tbl_EMSetal_ProdRef LEFT JOIN tbl_pending_queue_historical ON tbl_EMSetal_ProdRef.Product = tbl_pending_queue_historical.txt_product
WHERE (((tbl_pending_queue_historical.txt_department)="PIRS" Or (tbl_pending_queue_historical.txt_department) Is Null) AND ((tbl_pending_queue_historical.ysn_exclude)=0 Or (tbl_pending_queue_historical.ysn_exclude) Is Null) AND ((tbl_pending_queue_historical.dte_reporting_month ) Is Null Or (tbl_pending_queue_historical.dte_reporting_month) =[Forms]![FormMaster]![RepMo]))
GROUP BY tbl_EMSetal_ProdRef.Product;

Funny thing is....if I copy the left table just for the records where dte_reporting_month = the date from the form, and do the query on that copied table, then it works.

 

[jdraw]

see http://www.w3schools.com/sql/sql_join_left.asp for syntax and examples.

You want to start with a simpler example and build up to what you really want.

 

[keyel1971]

Thank you, but I am "worked up" to what I want. I designed this sql, and it works. In no less than 6 DBs, with 12 different tables (6 pairs). So the sql is fine. I'm trying to discover why all of a sudden it's not working with these two tables, when their design is the same as the others where it does work.

 

[spikepl]

If your query is verified elsewhere, that means that the particular data where "it doesn't work" is not as the other data. So the query works just fine, and does EXACTLY what you told it to do.

 

[keyel1971]

Yes, exactly correct. The data types in the "doesn't work" tables versus the data types in the "it works" tables are the same. So there must be something in the values of the fields themselves. I was seeing if anyone else has ever had this happen, and if so, and they solved it, what the issue was. I wanted to take a shot in the dark in a forum before I scroll through 10,000 values.

 

[spikepl]

This is nothing anyone else can do anything about but you. You have some IS NULL conditions - screw ups may have put some zero lengths strings in there, or spaces, so it on the surface visually appears ok.

Do a query to find such fields. Do queries to verify your other "where" data. You already have a pointer - the left-out product. Verify that all the contents are as they should be, each and one field.

 

[keyel1971]

Yes, another forum actaully suggested the same thing about 10 minutes ago and I found it......some users are entering blanks in fields instead of skipping them. Thanks!

 

[spikepl]

Crossposting without notification that you have done so is disrespectful of ppl's time and voluntary effort.

 

[keyel1971]

You mean posting in more than one forum is disrespectful? I'm sorry, but I have a VP breating down my neck and I wanted to find help as soon as possible so I thought I'd widen my chances by posting to more than one forum. Didn't know that wasn't allowed. As for not stating that I found the problem......I did do that about 30 seconds after I found it. It took me 10 minutes to find the blanks. Sorry if that's not quick enough for you.

 

[spikepl]

Crossposting without notification that you have done so is disrespectful of ppl's time and voluntary effort.

I don't care if the entire universe is breathing down your neck. You are wasting people's time - in this cae mine - by not telling them that elsewhere someone else perhaps is working on a solution, as may be the case, when you have asked the same question many places.

 

[keyel1971]

You must be in management. Are you trying to say that what I should've done is add to my post that "hey guys, I'm looking for a speedy response, so I did post this in several places, so if you don't have time to look at this right now, there are others who most likely will either here or elsewhere". I say you must be in management because only a manager wouldn't be able to put it nicely like I just did.

 

[Alansidman]

Keyel1971;

Spikepl is absolutely on target here. It is not that you told us after the fact, but that you didn't tell us before the fact.

Read this for a further explanation as to why this is a such a big no-no in forums. We are all volunteers with priorities.

http://www.excelguru.ca/content.php?184

Alan

 

[keyel1971]

Thank you Alansidman. I will head this in the future. And thank you for not assuming I'm an a$$hole, but rather just a newbie, who does have a lot of experience and can help others out with their problems, and be someone of value on here to others hopefully.

There's a nice way....and a mean way to get a point accross. Thanks for doing it the nice way.

 

[keyel1971]

Although I do admit that I'm the dummy for not thinking of the blank versus null thing. I do appologize for wasting anyone's time on such a stupid thing on my part.