Converting MSAccess SQL to TSQL - newbie - Replace Access DMin function with TSQL Min

  • Can someone give me some support in migrating this to SQL Server? The Req_Fin_Priorities are numeric (1, 2, or 3 no nulls).

    In Access, the DMin function returns the minimum value in a specified set of records (or domain). The syntax for the DMin function is: DMin ( expression, domain, [criteria] )

    In TSQL - it appears to need: SELECT Min(column) FROM table WHERE condition

    Access SQL Sub_2 (works)

    SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHL

    FROM sub_1

    WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]","sub_1","[ID_Wells]=" & [ID_Wells])));

    Access SQL sub_1 (works)

    SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL

    FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type

    WHERE (((Wells_SHLBHL.SHLBHL)="SHL"));

  • Slugged through it and came up with this.

    SELECT ID_Wells, Req_Fin_Priority AS MinPriority, ID_SHLBHL, Req_Fin, SHLBHL

    FROM dbo.Sub_1

    WHERE (Req_Fin_Priority =

    (SELECT MIN(Req_Fin_Priority) AS Expr1

    FROM dbo.Sub_1 AS Sub_1_1

    WHERE (dbo.Sub_1.ID_Wells = ID_Wells)))

  • On the MS Access UK site, another suggestion was this.

    It provided the exact same results as my solution above.

    Did not see any difference in the time for 100,000 records.

    It uses the TOP and Order By

    Thought it would be good to share this too.

    MS Access developers could use this solution and be able to migrate to T-SQL easily.

    SELECT ID_Wells, ID_SHLBHL, Req_Fin_Priority AS MinPriority, Req_Fin, SHLBHL

    FROM dbo.Sub_1

    WHERE (Req_Fin_Priority =

    (SELECT TOP (1) Req_Fin_Priority AS Expr1

    FROM dbo.Sub_1 AS Sub_1_1

    WHERE (dbo.Sub_1.ID_Wells = ID_Wells)

    ORDER BY Expr1))

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply