473,842 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Convert Access Dlookup into SQL Server Query

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

My Access Query Is:

SELECT qtrade.intordre ftrim AS Expr1, qtrade.extordre ftrim AS Expr2, qtrade.intinvre ftrim AS Expr3, DLookUp("[intinvref]","[trade]","[ledgerno] = " & 4 & "and [intordref] ='" & [intordreftrim] & "'") AS InvNoLookup, qtrade.invline AS Expr4, qtrade.accounti d AS Expr5, qtrade.descript n 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.extordr eftrim AS Expr2,A.intinvr eftrim AS Expr3,
B.intinvref AS InvNoLookup,A.invline AS Expr4,A.account id AS Expr5,A.descrip tn 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 9074
ck9663
2,878 Recognized Expert Specialist
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
Kunal Desale
5 New Member
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".Tab le 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 Recognized Expert Specialist
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

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

Similar topics

23
2704
by: JustMe | last post by:
I don't know if this has anything to do with AccessXP running on Terminal Services with Access97/2000 also installed, but here is one example of a query that does not work any longer: SELECT DLookUp("MajorCategory","AutoTypes","='1'") AS MajorCategory, PreOwned.PaintColor FROM PreOwned; When I try to run the above query I get the following error:
1
2179
by: Peter Monica | last post by:
I am trying to calculate distance from a point entered by a user usng a function in Access in a query that calls tables in an Oracle database as well as in Access. I am getting the following message: Undefined function 'Distance' in expression. Here is the query: SELECT people.first_name , jobs.job
1
8060
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan records and validate added data. My question is..
2
3506
by: Andrew Stanton | last post by:
Hi, I have a query that returns a sum for all values found that match the criteria. I am wanting to place this value in a textbox on its own so I can refer to it elsewhere. I have used the dlookup function in the control source of the textbox to do this, however I only seem to get an '#Error' returned.
3
2508
by: DFS | last post by:
FYI, Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers each time it's called. Anyone know why?
5
2943
by: m_t_hill | last post by:
Running MS Access 2000 MS Windows XP Pro This has caused me a lot of hair loss in the last few days so would appreciate any help. I am running code to append/update a local access database from a remote MS SQL database. The method of determining which records to append is primarily using the last date an update was carried out; I appreciate that there is probably a better way of doing this but time
8
7259
by: s_wadhwa | last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber, UCASE(Buildings.BuildingName) AS BuildingName, Buildings.MasterPlanCode, Buildings.UniformBuildingCode, Buildings.FunctionalCategoryCode, Buildings.OwnershipCode, Buildings.ConditionCode, Format$(,"0000000") AS dBasicGrossArea, Format$(,"0000000") AS dCoveredUnenclosedGrossArea, IIf(Month()>9,Month(),"0" & Month()) & Year() AS dDateOccupancy, Buildings.YearConstructed,...
2
1630
by: sparks | last post by:
First question is there a way to tell who is logged into a database on a server? I did some checking and found several things at MS support for access 2007, log in run some code and get a list. The problem is all of these databases are access 97 and 2003 The databases are not set for exclusive access, the problem comes when we have to make a change and someone left it on their desktop and forgot to log out.
0
2764
by: Sebastian | last post by:
Hello I develop my applications in Access 2002. My development system is running Windows XP SP2 and I have Microsoft Office XP Developer. Microsoft Office XP is at SP3. I used Inno Setup (great product) to install my applications. When the Access Runtime was needed on the system I simply ran 'accessrt.msi' from Microsoft Office Developer discs. When another version of Access (other than Access 2002) was on the system then I simply...
0
9865
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10668
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7853
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.