• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2020
  • Last Modified:

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
0
jsctechy
Asked:
jsctechy
  • 3
1 Solution
 
Eugene ZCommented:
Hmm. first()-> see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21479738.html

try
SELECT DISTINCT CUSTNEW.ACCT_P, CUSTNEW.REP_P,
BROKER2.[Last name],
min(([MONTH].REP_P) AS OrigREP_P,
min(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])>Max(tblMonth_dates.LASTDATE)) AND
 (([MONTH].PROCESS_DATE)=Min(Month.Process_Date) and Month.ACCT_BETA=" & [CUSTNEW]![ACCT_BETA])))             <<<--'''put some value for Month.ACCT_BETA to test
GROUP BY CUSTNEW.ACCT_P, CUSTNEW.REP_P, BROKER2.[Last name];
0
 
Eugene ZCommented:
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));
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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...

0
 
jsctechyAuthor Commented:
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])))

0
 
Eugene ZCommented:
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])))            

----
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now