Link to home
Start Free TrialLog in
Avatar of jsctechy
jsctechyFlag for United States of America

asked on

MS access to MS SQL (Dmax - Dmin First functions)

hi there,
i need to convert this MS acces to SQL but there are two functions that i dont know the replacement in SQL. Can you take a look and help me resolving this issue?

MS ACCESS Query:
SELECT DISTINCT CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name], First(MONTH.REP_P) AS OrigREP_P, First(BROKER2_1.[Last name]) AS [OrigLast name]
FROM ((BROKER2 INNER JOIN CUSTNEW ON BROKER2.REP_P = CUSTNEW.REP_P) INNER JOIN [MONTH] ON CUSTNEW.ACCT_P = MONTH.ACCT_P) INNER JOIN BROKER2 AS BROKER2_1 ON MONTH.REP_P = BROKER2_1.REP_P
WHERE (((CUSTNEW.[START-DATE])>DMax("[LASTDATE]","tblMonth_dates")) AND ((MONTH.PROCESS_DATE)=DMin("[Process_Date]","Month","[ACCT_BETA]=" & [CUSTNEW]![ACCT_BETA])))
GROUP BY CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name];

MS SQL:
SELECT DISTINCT CUSTNEW.ACCT_P, CUSTNEW.REP_P,
BROKER2.[Last name],
First([MONTH].REP_P) AS OrigREP_P,
First(BROKER2_1.[Last name]) AS [OrigLast name]
FROM ((BROKER2 INNER JOIN CUSTNEW ON BROKER2.REP_P = CUSTNEW.REP_P) INNER JOIN [MONTH] ON CUSTNEW.ACCT_P = [MONTH].ACCT_P) INNER JOIN BROKER2 AS BROKER2_1 ON [MONTH].REP_P = BROKER2_1.REP_P
WHERE (((CUSTNEW.[START-DATE])>DMax("[LASTDATE]","tblMonth_dates")) AND (([MONTH].PROCESS_DATE)=DMin("[Process_Date]","Month","[ACCT_BETA]=" & [CUSTNEW]![ACCT_BETA])))
GROUP BY CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name];

Thanks,
jsctechy
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
dmax dmin
example from http://www.techonthenet.com/access/functions/domain/dmax.php

Let's take a look at a simple example:

DMax("UnitPrice", "Order Details", "OrderID = 10248")

In this example, you would return the maximum UnitPrice from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:

SELECT Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));
pretty straightforward -  each of these evaluates to a scalar SELECT (returns one value)

DMax("[LASTDATE]","tblMonth_dates"))
>>
(SELECT MAX(lastdate) FROM tblMonth_dates)

DMin("[Process_Date]","Month","[ACCT_BETA]=" & [CUSTNEW]![ACCT_BETA])
>>
(SELECT MIN(Process_Date) FROM [Month] WHERE [ACCT_BETA] = @ACCT_BETA)

for the last one you will have to declare the variable @ACCT_BETA first and set the value...

Avatar of jsctechy

ASKER

now you experts just lost me completly.
i see the concept you are trying to put there but i done see how to apply to my query.
how this part would looks like in my SQL query?

WHERE (((CUSTNEW.[START-DATE])>DMax("[LASTDATE]","tblMonth_dates")) AND ((MONTH.PROCESS_DATE)=DMin("[Process_Date]","Month","[ACCT_BETA]=" & [CUSTNEW]![ACCT_BETA])))

access:  WHERE (((CUSTNEW.[START-DATE])>DMax("[LASTDATE]","tblMonth_dates"))  AND

sql server: WHERE (((CUSTNEW.[START-DATE])>Max(tblMonth_dates.LASTDATE)) AND

--------------
access: ((MONTH.PROCESS_DATE)=DMin("[Process_Date]","Month","[ACCT_BETA]=" & [CUSTNEW]![ACCT_BETA])))

 sql server: (([MONTH].PROCESS_DATE)=Min(Month.Process_Date) and Month.ACCT_BETA=" & [CUSTNEW]![ACCT_BETA])))            

----