• Status: Solved
• Priority: Medium
• Security: Public
• Views: 150

# Aggregated Function from Access (First)

Hi

I need to know what the same function in SQL server 7 is for (First) the Aggregated function for Access.

e.g.

SELECT dtYear,dtMonth,
Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume,
First(dtSales.dtRepCode) AS dtRepCode,
FROM tblCustomers INNER JOIN dtSales
0
Rab
1 Solution

Commented:
you may be able to do it with something like this:
SELECT TOP 1 dtYear,dtMonth,Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume
FROM tblCustomers
INNER JOIN dtSales
ORDER BY (whichever field you want the top value for)

0

Billing EngineerCommented:
azrasound: using TOP 1 like this will produce 0 or 1 rows at all, you need to modify (see below)
Rab: you need to use GROUP BY:

SELECT S.dtYear,S.dtMonth,
Sum(S.dtBulkValue) AS dtBulkValue,
Sum(S.dtBulkVolume) AS dtBulkVolume,
( SELECT TOP 1 SI.dtRepCode FROM dtSales SI WHERE SI.dtYear = S.dtYear AND SI.dtMonth = S.dtMonth) AS dtRepCode,
( SELECT TOP 1 dtTradeChannel FROM dtSales SI WHERE SI.dtYear = S.dtYear AND SI.dtMonth = S.dtMonth) AS dtTradeChannel
FROM tblCustomers C INNER JOIN dtSales S
GROUP BY S.dtYear, S.dtMonth

0

Author Commented:
Hi angelIII
If I use the query as above the SQL query returns two more records that the
Access query. Why would this be.

query e.g.

SELECT dtYear,dtMonth,
Sum(dtSales.dtSingleValue) AS dtSingleValue,
Sum(dtSales.dtSingleVolume) AS dtSingleVolume,
Sum(dtSales.dtMultiValue) AS dtMultiValue,
Sum(dtSales.dtMultiVolume) AS dtMultiVolume,
Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume,
( SELECT TOP 1 dtSales.dtRepCode FROM dtSales SI WHERE SI.dtYear = dtSales.dtYear AND dtSales.dtMonth = dtSales.dtMonth) AS dtRepCode,
( SELECT TOP 1 dtTradeChannel FROM dtSales SI WHERE SI.dtYear = dtSales.dtYear AND dtSales.dtMonth = dtSales.dtMonth) AS dtTradeChannel

FROM tblCustomers  INNER JOIN dtSales
ON tblCustomers.CustomerID=dtSales.dtCustomerID
WHERE CNumber = '1000022120' Or CNumber = '1000022130'