Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Angry Unanswered: MS Access vs. Transact-SQL – Conditional SQL Select Statement Problem

    Hi All,

    I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.

    My BASIC Select Statement is:

    SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
    Address2, Suburb, State, Postcode, Phone
    FROM Customers
    WHERE Customer_No IN
    (SELECT Customer_No FROM temp_Customers)

    Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:

    SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
    FROM Customers
    GROUP BY Customer_No
    ORDER BY First(Cust_Ref_No), Customer_No;

    There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?

    P.S. I really need to run the select statement as one step rather than splitting it up into parts.

    Regards, Trog28
    Last edited by trog28; 12-13-04 at 23:51.

  2. #2
    Join Date
    Dec 2004
    Posts
    22

    Might work

    Not sure but think the below should work.
    But not quite sure what you want, and haven't got chance to test the below.

    SELECT Cust_Ref_No AS Cust_Ref_No, Customer_No
    FROM Customers
    where
    Cust_Ref_No In (
    SELECT Max(Reference Number) AS Cust_Ref_No
    FROM Customers
    GROUP BY Customer_No);

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    as an afterthought, you can establish SQL Server ansi 92 syntax compatibility in ms access by performing the following

    tools > options
    Tables/Queries > SQL Server ANSI 92 syntax
    this is only available in a few editions. i think 2000 and up

  4. #4
    Join Date
    Apr 2004
    Posts
    10
    Fantastic,

    Thanks oak12 "Max" did just what I was after.

    Trog28

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •