469,907 Members | 2,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,907 developers. It's quick & easy.

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.
Nov 17 '08 #1
3 8653
ck9663
2,878 Expert 2GB
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
Nov 17 '08 #2
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?
Nov 18 '08 #3
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @test table (ID tinyint)
  3.  
  4. insert into @test values(1)
  5. insert into @test values(1)
  6.  
  7. insert into @test values(2)
  8. insert into @test values(2)
  9.  
  10. insert into @test values(3)
  11. insert into @test values(3)
  12.  
  13. insert into @test values(4)
  14. insert into @test values(4)
  15.  
  16. insert into @test values(5)
  17. insert into @test values(5)
  18.  
  19. select t1.id, id2 = (select top 1 id from @test t2 where t2.id = t1.id)
  20. from @test t1
  21.  
  22.  
Happy Coding!

-- CK
Nov 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by JustMe | last post: by
2 posts views Thread by Andrew Stanton | last post: by
2 posts views Thread by sparks | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.