Query not returning what I would like....

[neuwirth]

I have a query in a pretty simple Access application for which I have posted the SQL code below. My issue is that when there is no value for the field CUSTNMBR in the table dbo_SOP60300, my query returns no results. I need for the query to return results for the other fields even if no value exists for that field in that table. I have tried changing the join properties between dbo_SOP10100 and dbo_SOP60300, but Access doesn't seem to like that and gives me an error message.
Thanks!

SELECT
dbo_SOP10100.SOPNUMBE, dbo_SOP10100.CSTPONBR, dbo_SOP10100.CUSTNAME, dbo_SOP10100.ShipToName, dbo_SOP10100.ADDRESS1, dbo_SOP10100.ADDRESS2, dbo_SOP10100.ADDRESS3, dbo_SOP10100.CITY, dbo_SOP10100.STATE, dbo_SOP10100.ZIPCODE, dbo_SOP10100.COUNTRY, dbo_SOP10100.ReqShipDate, dbo_SOP10200.ITEMNMBR, dbo_SOP10200.ITEMDESC, dbo_SOP10200.QUANTITY, dbo_IV00101.ITEMSHWT, dbo_IV00101.USCATVLS_3, dbo_IV00101.USCATVLS_4, dbo_SOP60300.CUSTITEMNMBR, dbo_SOP60300.CUSTITEMDESC, dbo_IV00101.USCATVLS_5, dbo_SOP10100.CNTCPRSN, dbo_SOP10100.SHIPMTHD
FROM
(
(dbo_SOP10100 INNER JOIN dbo_SOP10200 ON dbo_SOP10100.SOPNUMBE = dbo_SOP10200.SOPNUMBE)
INNER JOIN dbo_SOP60300 ON
(dbo_SOP10200.ITEMNMBR = dbo_SOP60300.ITEMNMBR) AND (dbo_SOP10100.CUSTNMBR = dbo_SOP60300.CUSTNMBR)
)
INNER JOIN dbo_IV00101 ON dbo_SOP10200.ITEMNMBR = dbo_IV00101.ITEMNMBR
WHERE
(
((dbo_SOP10100.SOPNUMBE)="101891") AND ((dbo_SOP10200.CMPNTSEQ)=0)
)
ORDER BY dbo_IV00101.USCATVLS_5;

 

[plog]

What error message? Did you google it?

Can you post a screen shot of this in design view?

 

[neuwirth]

I've attached a PNG file screenshot of the query.
The error message I get is: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."
I don't want to do a separate query because this is in a menu driven application for a user that is even less Access literate than I am. Plus, I'm not really sure what the error message means.
Thanks!

 

[plog]

That image doesn't have enough resolution for me to see any details. I can however see that the first table links directly to the second and the second links directly to the third. Also, the first directly links to the third on a different field than the second table. This might be the cause of that ambigous join.

Also, your reason for not wanting to do a sub-query makes no sense. A sub-query isn't going to cause your computer to implode or have any other negative effect on what you want to ultimately accomplish.

 

[neuwirth]

OK, I tried the sub-query thing, and it still won't pull the records I want. Not sure if I did it right. Let me try to explain what I'm trying to do:
I have one table with data concerning sales orders, including customer numbers and item numbers. In another table, I have those two fields, (CUSTNMBR and ITEMNMBR). For some customers and some item numbers, this second table contains their name and number for the item in other fields. (Not the same CUSTNMBR and ITEMNMBR fields).
So, if the CUSTNMBR and ITEMNMBR fields in the first table match those same two fields in the second table, I need the query to pull the customer's number and description for that item. However, if there is not a match for both fields in table 2, I need it to still give me all the data from table 1 for that order. What I am getting is that if the two fields do not match in both tables, the query comes up empty.
Is everyone as befuddled as I am now?
Thanks!

 

[jdraw]

Suggest you try to get a better screen capture (perhaps jpg) because the one you attached is not readable. I'm confused by your explanation..... maybe you could try with an example showing some data records.

With some data example show the key fields in the tables involved, the relationship between tables and a sample of what you want to get back.

 

[neuwirth]

OK, I tried to put together a Word doc with a picture and description of what I'm trying to do. I hope this makes it plainer, I'm not sure how to explain it.

 

[plog]

Based on your image, you simply need to make both those links LEFT JOINS. Include all data from Table A and only data that matches in Table B. Did you do that for both links, or just one?

 

[jdraw]

Try this query

SELECT TableA.*, TableB.CUSITEMNBR, TableB.CUSITEMDESC
FROM TableA
LEFT JOIN TableB
ON TableA.CUSTNMBR = TableB.CUSTNMBR AND
TableA.ITEMNMBR  = TableB.ITEMNMBR
ORDER BY TableA.CUSTNMBR,TableA.ITEMNMBR

OOOoops: I see plog has responded while I was typing.

 

[neuwirth]

plog, that worked. I think jdraw's is the same thing, isn't it?

Thanks to both of you!

 

[plog]

Yes, its the same advice, he took the time to write specific SQL for you.