Multple Connditions on a JOIN - Works fine in SQL Server

[bwhalen1]

Hello,

New IT professional here - working in SQL - Need to deliver query to End Users via Access - I have this Query in SQL server which runs as expected. Access has all sorts of problems with the join. Would anyone be able to lend a hand?

USE *********
SELECT SS.DatePromised
, SS.Customername
, SS.CustomerNum
, ID.MfgCode
, ID.CatNum
, ID.ProductDescription
, ID.Price
, ID.PriceUOM
, SS.OrigRegNum
, SS.CustPO
, PO.PONum
, ID.OrderQty
, PO.PORecdQTY1
, PO.PORecdDate1
, PO.PORecdQTY2
, PO.PORecdDate2
, PO.PORecdQTY3
, PO.PORecdDate3
, ID.SalesType
, SS.SalesRepNumIN
, SS.SalesRepNumOUT
FROM (dbpenInvoiceDetail AS ID INNER JOIN dbo.openSalesSummary AS SS ON ID.OpenSalesSummaryID = SS.OpenSalesSummaryID)
LEFT JOIN dbo.PODetail AS PO ON SS.OrigRegNum = PO.BOCustRegNum1 AND ID.CatNum = PO.CatNum
WHERE SS.DatePromised < getdate()
ORDER BY ID.OpenSalesSummaryID, Id.InvLine;


Thank you in Advance!

 

[SkipVought]

hi,

Do some testing.

I believe that in MS Access this table
dbpenInvoiceDetail syntax would be
dbpenInvoiceDetail

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Use a PassThru query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bwhalen1]

Hey PHV & Skipvought - Thank you for the reply - that's what I was thinking: Im not a big access fan - and still learning.

@skip: my table names are diff in access so I just drop the dbo but yes I believe you are right.

@ PHV
Pass through query:

USE *********
SELECT

...
PO.PONum
, PO.PORecdQTY1
, PO.PORecdDate1
, PO.PORecdQTY2
, PO.PORecdDate2
, PO.PORecdQTY3
, PO.PORecdDate3
...
FROM
...
LEFT JOIN( SELECT
PONum
,PORecdQTY1
,PORecdDate1
,PORecdQTY2
,PORecdDate2
,PORecdQTY3
,PORecdDate3
FROM dbo.PODetail ON SS.OrigRegNum = BOCustRegNum1 AND ID.CatNum = CatNum) AS PO
..
...
....

Will I place Inside the second SELECT statement? I was playing with it and access was still yelling at me : /. I need to join on those two conditions within the second SELECT "SS.OrigRegNum = BOCustRegNum1 AND ID.CatNum = CatNum"

Does Access know what SS and ID are yet - access is frustrating <_<

Am I close ?

Thanks again

 

[bwhalen1]

OOPS I MEANT WHERE instead of on above "WHERE SS.OrigRegNum = BOCustRegNum1 AND ID.CatNum = CatNum

 

[PHV]

In a pass through query you use the native SQL code, not JetSQL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bwhalen1]

Hey PHV - I am having a hard time finding some good examples out there - still pretty new to this - do you have any resources that could illuminate this to me. I apologize for my lack of knowledge in this subject, but maybe you could point me in the right direction.

 

[bwhalen1]

Well I figured out the pass-through query- but now I get another error.

First Query:

SELECT SS.DatePromised, SS.Customername, SS.CustomerNum, ID.MfgCode, ID.CatNum, ID.ProductDescription, ID.Price, ID.PriceUOM, Cint(SS.OrigRegNum) AS OrigRegNum, SS.CustPO, ID.OrderQty, ID.SalesType, SS.SalesRepNumIN, SS.SalesRepNumOUT
FROM OpenInvoiceDetail AS ID INNER JOIN openSalesSummary AS SS ON ID.OpenSalesSummaryID = SS.OpenSalesSummaryID;



Then my second:


SELECT openinvoicesub.*, PODetail.PONum, PODetail.PORecdQty1, PODetail.PORecdDate1, PODetail.PORecdQty2, PODetail.PORecdDate2, PODetail.PORecdQty3, PODetail.PORecdDate3
FROM openinvoicesub LEFT JOIN PODetail ON (openinvoicesub.CatNum = PODetail.CatNum) AND (openinvoicesub.OrigRegNum = PODetail.BOCustRegNum1)
WHERE openinvoicesub.DatePromised < [Date];

I get the error :

"This expression is typed incorrectly, or is too complex to be evaluated..."


Any ideas?

 

[vbajock]

I usually attach the table to Access and then use the Query GUI to build the SQL code, doing it that way your project would take about 5 minutes