MS Access - Query Refactoring Assistance

Question

I have a page in one of my client's websites that generates an extensive report pulling data from numerous tables throughout the website's MS Access database. One of the unfortunate architectural issues of the website is the existence of two nearly identical tables that represent the same "type" of data, but one is an "old" version and the other is a "new" version. When the report is generated I need to perform some aggregate operations on the two similar tables. The initial query joined these tables into the rest of the data and called the appropriate aggregate functions on the joined tables. Soon I realized that a join would not work because the two tables do not necessarily have the same row count, thus causing the aggregate function to improperly group the rows from both tables...

Were this MSSQL or MySQL I would probably create a VIEW containing the composite data from both tables, but unfortunately I'm stuck in MS Access where such "novel" concepts do not exist... The solution I was able to come up with works, but has got to be some of the ugliest SQL I have ever seen. Basically, I create a SQL query including all of the appropriate columns from multiple joined tables and one of the two similar tables. Then I create a second SQL query containing all of the same fields and join the other similar table. Finally, I UNION the two queries and wrap them into a subquery within the FROM clause of an outer query.

The end result is a massive query with a ton of duplicate selects that I included only because of the need to aggregate data from the two similar tables. I really would like to refactor the query into something less awful, but I'm not sure where to start... Any advice?

SELECT contractid,
 pholderid,
 policyholdername,
 policyholdercity,
 policyholderstate,
 vehicleyear,
 vehiclemake,
 vehiclemodel,
 Iif(claimmileage > vehiclemileage, claimmileage, vehiclemileage)                       AS mileage,
 clientname,
 contracttype,
 contractmonths,
 wholesaleprice,
 begindate,
 cancelleddate,
 cancelledalphatotal,
 paiddate,
 voided,
 Sum(claimscost)                                                                        AS totalclaimscost,
 Sum(claimscount)                                                                       AS totalclaimscount,
 DateAdd('m', contractmonths, begindate)                                                AS expirationdate,
 Iif(paiddate IS NOT NULL AND contractmonths > 0,
    Iif(voided = true,
     Iif(cancelleddate IS NOT NULL,
      Iif(((cancelleddate - begindate) / (364.25 / 12)) >= contractmonths,
       1,
       ((cancelleddate - begindate) / (364.25 / 12)) / contractmonths),
      Iif(((Date() - begindate) / (364.25 / 12)) >= (contractmonths),
       1,
       ((Date() - begindate) / (364.25 / 12)) / contractmonths)),
     ((Date() - begindate) / (364.25 / 12)) / contractmonths),
    0)                                                                                   AS earnedfactor,
 (earnedfactor * wholesaleprice)                                                         AS earnedpremium,
 Iif(voided = true, 0, (wholesaleprice - earnedpremium))                                 AS unearnedpremium,
 Iif(voided = true AND cancelledalphatotal IS NOT NULL,  cancelledalphatotal, 0)         AS refund,
 Iif(earnedpremium > 0,totalclaimscost / earnedpremium, 0)                               AS lossratio
   FROM     (SELECT contracts.id                     AS contractid,
    policyholders.id                 AS pholderid,
    policyholders.firstname
   + ' '
   + policyholders.lastname       AS policyholdername,
    policyholders.city               AS policyholdercity,
    policyholders.state              AS policyholderstate,
    vehicles.yr                      AS vehicleyear,
    vehicles.make                    AS vehiclemake,
    vehicles.model                   AS vehiclemodel,
    vehicles.mileage                 AS vehiclemileage,
    clients.coname                   AS clientname,
    contracttypes.name               AS contracttype,
    coverageavailable.contractmonths AS contractmonths,
    contracts.contractwholesalecost  AS wholesaleprice,
    contracts.begindate              AS begindate,
    contracts.cancelledon            AS cancelleddate,
    contracts.cancelledalphatotal    AS cancelledalphatotal,
    contracts.paidon                 AS paiddate,
    contracts.voided                 AS voided,
    Sum(Iif(claims.totalrepaircost IS NULL,0,claims.totalrepaircost))                              AS claimscost,
    Count(claims.id)                    AS claimscount,
    Max(Iif(claims.currentmileage IS NULL,0,claims.currentmileage))                              AS claimmileage
   FROM   claims
    RIGHT JOIN (coverageavailable
       INNER JOIN ((((policyholders
          INNER JOIN clients
            ON policyholders.clientid = clients.id)
         INNER JOIN contracts
           ON policyholders.id = contracts.policyholderid)
           INNER JOIN vehicles
          ON contracts.vehicleid = vehicles.id)
          INNER JOIN contracttypes
         ON contracts.contracttypeid = contracttypes.id)
      ON coverageavailable.id = contracts.termid)
   ON claims.policyholderid = policyholders.id
   WHERE  contractmonths > 0
    AND contracts.begindate IS NOT NULL
    AND contracttypes.id <> 3
   GROUP BY contracts.id,
  policyholders.id,
  policyholders.firstname,
  policyholders.lastname,
  policyholders.city,
  policyholders.state,
  vehicles.yr,
  vehicles.make,
  vehicles.model,
  vehicles.mileage,
  clients.coname,
  contracttypes.name,
  coverageavailable.contractmonths,
  contracts.contractwholesalecost,
  contracts.begindate,
  contracts.cancelledon,
  contracts.paidon,
  contracts.voided,
  contracts.cancelledalphatotal
    UNION
    SELECT contracts.id                     AS contractid,
    policyholders.id                 AS pholderid,
    policyholders.firstname
   + ' '
   + policyholders.lastname       AS policyholdername,
    policyholders.city               AS policyholdercity,
    policyholders.state              AS policyholderstate,
    vehicles.yr                      AS vehicleyear,
    vehicles.make                    AS vehiclemake,
    vehicles.model                   AS vehiclemodel,
    vehicles.mileage                 AS vehiclemileage,
    clients.coname                   AS clientname,
    contracttypes.name               AS contracttype,
    coverageavailable.contractmonths AS contractmonths,
    contracts.contractwholesalecost  AS wholesaleprice,
    contracts.begindate              AS begindate,
    contracts.cancelledon            AS cancelleddate,
    contracts.cancelledalphatotal    AS cancelledalphatotal,
    contracts.paidon                 AS paiddate,
    contracts.voided                 AS voided,
    Sum(Iif(claim.inspector1paidout IS NULL,0,claim.inspector1paidout))
       + Sum(Iif(claim.inspector2paidout IS NULL,0,claim.inspector2paidout))
       + Sum(Iif(claim.mechanicpaidout IS NULL,0,claim.mechanicpaidout))
       + Sum(Iif(claim.partdealerpaidout IS NULL,0,claim.partdealerpaidout))                              AS claimscost,
    Count(claim.id)                    AS claimscount,
    Max(Iif(claim.mileage IS NULL,0,claim.mileage))                              AS claimmileage
   FROM   claim
    RIGHT JOIN (coverageavailable
       INNER JOIN ((((policyholders
          INNER JOIN clients
            ON policyholders.clientid = clients.id)
         INNER JOIN contracts
           ON policyholders.id = contracts.policyholderid)
           INNER JOIN vehicles
          ON contracts.vehicleid = vehicles.id)
          INNER JOIN contracttypes
         ON contracts.contracttypeid = contracttypes.id)
      ON coverageavailable.id = contracts.termid)
   ON claim.contractid = contracts.id
   WHERE  contractmonths > 0
    AND contracts.begindate IS NOT NULL
    AND contracttypes.id <> 3
   GROUP BY contracts.id,
  policyholders.id,
  policyholders.firstname,
  policyholders.lastname,
  policyholders.city,
  policyholders.state,
  vehicles.yr,
  vehicles.make,
  vehicles.model,
  vehicles.mileage,
  clients.coname,
  contracttypes.name,
  coverageavailable.contractmonths,
  contracts.contractwholesalecost,
  contracts.begindate,
  contracts.cancelledon,
  contracts.paidon,
  contracts.voided,
  contracts.cancelledalphatotal)
GROUP BY contractid,
  pholderid,
  policyholdername,
  policyholdercity,
  policyholderstate,
  vehicleyear,
  vehiclemake,
  vehiclemodel,
  vehiclemileage,
  clientname,
  contracttype,
  contractmonths,
  wholesaleprice,
  begindate,
  cancelleddate,
  cancelledalphatotal,
  paiddate,
  voided,
  Iif(claimmileage > vehiclemileage, claimmileage, vehiclemileage)
ORDER BY clientname,
  begindate DESC

Hopefully all that makes at least some sense...

Answer 1

QueryDef in Access, is similar to a VIEW in other RDBMS.
Based on the first glance, it is better to get the UNION part of the query in a querydef.