October 17, 2012 at 3:44 pm
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"));
October 18, 2012 at 8:48 am
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)))
October 19, 2012 at 8:51 am
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