Convert Access Dlookup into SQL Server Query

[Kunal Desale]

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.

[ck9663]

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

[Kunal Desale]

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

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?

[ck9663]

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

The DLookup() function returns one value from a single field even if more than one record satisfies the criteria. If no record satisfies the criteria, or if the domain contains no records, DLookup() returns a Null.

Your access query would actually translated to:

 
declare @test table (ID tinyint)
 
insert into @test values(1)
insert into @test values(1)
 
insert into @test values(2)
insert into @test values(2)
 
insert into @test values(3)
insert into @test values(3)
 
insert into @test values(4)
insert into @test values(4)
 
insert into @test values(5)
insert into @test values(5)
 
select t1.id, id2 = (select top 1 id from @test t2 where t2.id = t1.id)
from @test t1
 
 

Happy Coding!

-- CK