By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Convert Access Dlookup into SQL Server Query

P: 5
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
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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

P: 5
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
Expert 2.5K+
P: 2,878
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.