Query which performs multiple checks per field

[ParmoArmy]

Hi everyone,



I am new to using Access and have managed to figure out some things myself, but this has got me stumped.

I have built a database for tracking utilities (gas and electricity) invoice information for a portfolio of properties that the company I work for manage on our clients' behalf.

One of the things I need to track if the date of the last accurate invoice for each property and the logic I have built so far is as follows:

SELECT tblInvoiceData.strSiteName, Max(tblInvoiceData.dtmBillToDate) AS MaxOfdtmBillToDate
FROM tblInvoiceData, QryUtilities
WHERE (((tblInvoiceData.strFuelType)="Electricity") AND ((QryUtilities.ysnZTP)=True) AND ((QryUtilities.ysnLiveProperty)=True) AND ((QryUtilities.ysnUtilitiesAgreement)=True) AND ((tblInvoiceData.strR1ReadingType)="Actual"))
GROUP BY tblInvoiceData.strSiteName
ORDER BY Max(tblInvoiceData.dtmBillToDate) DESC;

"tblInvoiceData" is a table which contains all invoicing information and the relevant fields are as follows:

- strSiteName: The name of each of the sites
- dtmBillToDate: The date the bill goes up to
- strFuelType: The type of fuel
- strR1ReadingType: The type of meter reading (whether it is actual/estimated)

The logic works as far as it is successfully identifying the last bill date for each site where an actual meter reading was used. Where it is complicated, however, is that some sites have multiple meters. I need the query to tell me if the meter reading type for ALL of the meters associated with the site have been billed to actual meter readings, as if just one of them is estimated, the query will not do what I need it to do.

There is another column within "tblInvoiceData" called "intMPxN" which identifies the meter that the bill is based on.

Can anyone help with how I can perform this check in my query please?

Thanks in advance.

[Minty]

Add the intMPxN field and group on it as well.

If there is only one meter it will be the only one displayed, if there are more they will all be listed.

[ParmoArmy]

Thank you Minty - that has helped somewhat as the query now identifies that some of the MPxNs have been billed to actual meter readings more recently than others for the same site.

This now tells me, that for a site called Linthorpe Building which has 8 meters:

Meter 1 - Last billed accurately 01/02/2021
Meter 2 - Last billed accurately 01/02/2021
Meter 3 - Last billed accurately 01/02/2021
Meter 4 - Last billed accurately 08/01/2021
Meter 5 - Last billed accurately 08/01/2021
Meter 6 - Last billed accurately 08/01/2021
Meter 7 - Last billed accurately 01/04/2020
Meter 8 - Last billed accurately 01/11/2019

The last remaining issue is that when I run the query, it tells me that the last invoice based on actual meter readings for this site was 01/02/2021 (which is technically correct as 3 of the meters were billed accurately on this date), however I need it to tell me the last date that ALL of the meters were billed accurately.

I tried changing the logic from Max(tblInvoiceData.dtmBillToDate) AS MaxOfdtmBillToDate to Min(tblInvoiceData.dtmBillToDate) AS MinOfdtmBillToDate, but this then just gives me the oldest actual bill date for all sites, which is the opposite of what I need.

Can you help with how I would tweak this please?

Thanks again.

[Minty]

Add another grouping tblInvoiceData.strR1ReadingType
Then you will see the last of both actual and estimated readings.

If not you may need to have a query to only supply meters that have a a last actual reading, and join that to this query to only see those meters?