jsctechy
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])>D Max("[LAST DATE]","tb lMonth_dat es")) AND ((MONTH.PROCESS_DATE)=DMin ("[Process _Date]","M onth","[AC CT_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])>D Max("[LAST DATE]","tb lMonth_dat es")) AND (([MONTH].PROCESS_DATE)=DM in("[Proce ss_Date]", "Month","[ ACCT_BETA] =" & [CUSTNEW]![ACCT_BETA])))
GROUP BY CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name];
Thanks,
jsctechy
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])>D
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])>D
GROUP BY CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name];
Thanks,
jsctechy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pretty straightforward - each of these evaluates to a scalar SELECT (returns one value)
DMax("[LASTDATE]","tblMont h_dates"))
>>
(SELECT MAX(lastdate) FROM tblMonth_dates)
DMin("[Process_Date]","Mon th","[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...
DMax("[LASTDATE]","tblMont
>>
(SELECT MAX(lastdate) FROM tblMonth_dates)
DMin("[Process_Date]","Mon
>>
(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...
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])>D Max("[LAST DATE]","tb lMonth_dat es")) AND ((MONTH.PROCESS_DATE)=DMin ("[Process _Date]","M onth","[AC CT_BETA]=" & [CUSTNEW]![ACCT_BETA])))
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])>D
access: WHERE (((CUSTNEW.[START-DATE])>D Max("[LAST DATE]","tb lMonth_dat es")) AND
sql server: WHERE (((CUSTNEW.[START-DATE])>M ax(tblMont h_dates.LA STDATE)) AND
--------------
access: ((MONTH.PROCESS_DATE)=DMin ("[Process _Date]","M onth","[AC CT_BETA]=" & [CUSTNEW]![ACCT_BETA])))
sql server: (([MONTH].PROCESS_DATE)=Mi n(Month.Pr ocess_Date ) and Month.ACCT_BETA=" & [CUSTNEW]![ACCT_BETA])))
----
sql server: WHERE (((CUSTNEW.[START-DATE])>M
--------------
access: ((MONTH.PROCESS_DATE)=DMin
sql server: (([MONTH].PROCESS_DATE)=Mi
----
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));