Join with multiple Conditions - Passthrough Query

[bwhalen1]

Hey there,



Created code in SQL server, I need to deliver these queries in Access SQL or to my end users- Just having a little trouble getting it converted over: Here is what I created in SQL (which works great)-

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 (dbo.OpenInvoiceDetail 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;

Now I know in access its a little different - I was told that I need to use pass through queries - So I took a stab at it...

pass through 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 I referred to it with this query:

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];

Now I am getting the error "This expression is typed incorrectly, or is too complex to be evaluated..."

Any guidance here would be greatly appreciated, as I have already spent waaay too much time tooling with access to get it to work. If only I could just use SQL server : /

Thanks in advance!

[pbaldy]

Well, a passthrough query would be passed through to SQL Server, so the T-SQL syntax would work fine (in fact it would have to be T-SQL). Does it need to be an Access query?

[bwhalen1]

Unfortunately Yes - Our end users have to be able to run this query with Access on their machines - As a preference I prefer to work in SQL, so Ill create it on my end and import it into their Access for them. Still learning - this task has just been a bit challenging. Basically just want to get that first SQL query in an Access friendly format, and was playing with different ideas.

Like your title btw - just picked up Atlas Shrugged again the other day :P

[bwhalen1]

[edit ]

[pbaldy]

The only thing I see wrong in the last SQL is the brackets around Date, which would make Access think it was a field, but I would expect that to throw a parameter prompt. The fields in the joins are of compatible data types?

[bwhalen1]

Right on the Brackets - I did that so that it would throw the parameter prompt - The DBAs who designed the DB made a mistake and in my second query the

PODetail.CatNum is actually of type number (says access) and SS.OrigRegNum from the frist query is actually a text ( says access) so thats why i cast it as type int "Cint(SS.OrigRegNum)"
so when I compared them in query 2 "ON (openinvoicesub.CatNum = PODetail.CatNum)" it wouldn't throw an error. It was before I did that.

[pbaldy]

I thought that was a pass through query? CInt() isn't valid in T-SQL, so that must just be an Access query. Is it possible that text field is Null in a record? That would cause the CInt() to error, and perhaps cause this problem when Access tries to evaluate the joined fields.

[bwhalen1]

Ohh you were right about the cInt what I did instead was use the Val function so it looks like:


Val(SS.OrigRegNum) AS OrigRegNum

in the first query - The types matched and no errors - results match my SQL Server query!


Thank You for your troubleshooting help!

[pbaldy]

No problem, and welcome to the site by the way!