Convert Access Dlookup into SQL Server Query

Hello,
I am migrating access queries to SQL Server 2005 Queries.

My Access Query Is:

SELECT qtrade.intordreftrim AS Expr1, qtrade.extordreftrim AS Expr2, qtrade.intinvreftrim AS Expr3, DLookUp("[intinvref]","[trade]","[ledgerno] = " & 4 & "and [intordref] ='" & [intordreftrim] & "'") AS InvNoLookup, qtrade.invline AS Expr4, qtrade.accountid AS Expr5, qtrade.descriptn AS Expr6, DLookUp("[descriptn]","[trade]","[ledgerno] = " & 0 & "and [intordref] ='" & [intordreftrim] & "'") AS [age100-comment], qtrade.qtyinv AS Expr7, qtrade.invdate AS Expr8, qtrade.amtuchg AS Expr9
FROM qtrade
WHERE (((DLookUp("[intinvref]","[trade]","[ledgerno] = " & 4 & "and [intordref] ='" & [intordreftrim] & "'")) Not Like "700*"));

Now Converting above Access Query to Sql Server Query is not a difficult task, But the Results which I am getting from Access Query And Sql Server Query are different.

Now qtrade is also a access query which I have converted into sql query and giving me right output(i.e Number of records.) and I am using resultset of qtrade query in above query.

Trade table contains some repitative entries.So due to repitative entries INNER JOIN is returning more records as compaire to Access Query(Dlookup).

So what could be the resion of this number of records missmatch?


SQL Query:

SELECT A.intordreftrim AS Expr1,A.extordreftrim AS Expr2,A.intinvreftrim AS Expr3,
B.intinvref AS InvNoLookup,A.invline AS Expr4,A.accountid AS Expr5,A.descriptn AS Expr6,
C.intinvref AS [age100-comment],A.qtyinv AS Expr7,A.invdate AS Expr8,A.amtuchg AS Expr9 INTO #FINAL
FROM #qTrade A INNER JOIN NR...Trade B ON A.intordreftrim = B.intordref AND B.ledgerno=4
INNER JOIN NR...Trade C ON A.intordreftrim = C.intordref AND C.ledgerno=0
WHERE B.intordref Not Like '700%'

NR = Linked Server.

re: Convert Access Dlookup into SQL Server Query

Could you post some specific rows of data that are different?

Post some from the tables you're trying to join and the result that it's showing.


-- CK

re: Convert Access Dlookup into SQL Server Query

I am using Linked Server to fetch records in Access.
My Foxpro Tables are very big and contains to much columns ...
Instead of that I give you small example of same scenario.

Consider one table named "Test" which contains one column named "MyNum".Table contains 1,1,2,2,3,3,4,4,5,5 (10 Records.).In this table 1,2,3,4,5 are getting Repeated.

Now Query Using "DLookUp " and "INNER JOIN"

1. "DLookUp"
SELECT Test.ID, DLookUp("[ID]","[Test]","[ID] =" & [ID] ) AS Test1
FROM Test;

Output: (Number of records returning 10)

ID Test1
1 1
1 1
2 2
2 2
3 3
3 3
4 4
4 4
5 5
5 5

2. "INNER JOIN"
SELECT *
FROM Test INNER JOIN Test AS Test_1 ON Test.ID = Test_1.ID;

Output: (Number of records returning 20)

Test.ID Test_1.ID
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
3 3
3 3
3 3
3 3
4 4
4 4
4 4
4 4
5 5
5 5
5 5
5 5

Now, Here I know what happing in INNER JOIN Query,But don't know what exactly DLookup is doing.

When we convert Access Query to SQL Server Query, we use INNER JOIN inplace of DLookUp.Now I expect same output from both the query,Which is not happning here.So is their any other way to convert Access DLookUp query
to SQL Query so that I will get same output?

re: Convert Access Dlookup into SQL Server Query

First, read this to know more about DLookUp. Pay attention on this part:

Your access query would actually translated to:

Happy Coding!

-- CK