Access Query hide duplicates

Question

Hello,

I am trying to create a query with client's first and last name, their addresses, city, home phone, and cell phone and 1st date of service.  I want the query to only show unduplicated clients based on their 1st date of service for the current fiscal year July-June.

I also need to have total services for each client... Total Trips, Total Repairs, Total hours...etc (fields) for each unique client.

There are tables for each program:

Home Repairs      Field: Total Repairs

Transportation     Field: Total Trips

Chore                   Field: Total Hours

Visiting                 Field: Total Hours

 Etc...

Each table  has the same contact info for clients but different data for services.

I will be exporting this info to an updatable excel spreadsheet that will populate that information.

Will this need to be two separate queries, or can i do this on one?  Please help, Thank you

-Kim

Answer 1

You haven't given us much to go on, but assuming for simplicity that you have only two tables, Clients and Services, related on ClientID, then the query would be along these lines:

SELECT FirstName, LastName, Address, City, HomePhone, CellPhone

MIN(ServiceDate) AS FirstService,

COUNT(*) AS TotalTrips,

SUM(IIF(ServiceType = "Repair",1,0)) AS TotalRepairs,

SUM(Hours) AS TotalHours

FROM Clients INNER JOIN Services ON Clients.ClientID = Services.ClientID

WHERE AcctYear(ServiceDate,7,1) = AcctYear(DATE(),7,1)

GROUP BY FirstName, LastName, Address, City, HomePhone, CellPhone;

The query calls the following function, which returns the accounting year for the date passed into the function:

Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String

    Dim dtmYearStart As Date

    If MonthStart = 1 And DayStart = 1 Then

        ' accounting year is calendar year, so return single year value

        AcctYear = Year(DateVal)

    Else

        ' get start of accounting year in year of date value

        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)

        ' if date value is before start of accounting year

        ' accounting year starts year previous to date's year,

        ' otherwise it starts with date's year

        If DateVal < dtmYearStart Then

            AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")

        Else

            AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")

        End If

    End If

End Function

Answer 2

ciao Kim,

I think more details should be provided by you to have clear ideas about table structure of your scenario.

Anyway, assuming we are facing 1>M relationship between clients and services, try to get a look at this sql predicate applied to NorthWind table example database  put at our disposal by MS :

it allows you to retrive for each customer total order value for the first order placed filtering June and July month of current year.

SELECT Ordini.IDCliente,

            Ordini.DataOrdine,

           sum([quantità]*[prezzounitario]) AS totale

FROM

       Ordini

INNER JOIN

       [Dettagli ordini] ON Ordini.IDOrdine = [Dettagli ordini].IDOrdine

where

          dataordine=(select min(dataordine) from ordini as o where o.idcliente=ordini.idcliente)

and

         ( month(dataordine)=6 or month(dataOrdine)=7)

and

          year(dataordine)=year(date())

group by

            Ordini.IDCliente,

            Ordini.DataOrdine

order by

     idcliente

Try inspiring on it :-)

HTH.

Ciao, Sandro.