?
Solved

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

Posted on 2007-08-01
5
Medium Priority
?
1,945 Views
Last Modified: 2008-02-01
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
Comment
Question by:jsctechy
  • 3
5 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 19613708
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613713
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
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19613722
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
 
LVL 1

Author Comment

by:jsctechy
ID: 19613814
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613846
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question