I definitely need an Access jumpStart! Haha. Just a word about realigning priorities. I’ve really been re-vamping my internal business structure and schedule. While I was figuring things out, I have been away from writing, but I think I’m ready to come back now, so my plan is to begin writing every day again. Thanks for not unsubscribing during the last few “silent months”.

That said, today I was working on a crazy Access query. It has multiple sub-queries within the select clause to get individual data, and the where clause. It calls a public VBA function on each row. I’m kinda surprised it runs fast, but it does. It refreshes every time they visit the app home screen and it’s execution time is not really noticeable.

Here it is:

SELECT
    Equipment.EquipID AS [Equipment ID],
    Equipment.EquipType AS [Equipment Type],
    Format(
        IIf(
            Count([datecompleted]) = 0,
            Max([PurchaseDate]),
            Max([datecompleted])
        ),
        "Short Date"
    ) AS [Last Serviced],
    Equipment.Driver AS [User],
    (
        SELECT
            TOP 1 Iif(
                DatePart("yyyy", expsub.SafetyExpiration) = 9999
                AND DatePart("m", expsub.SafetyExpiration) = 12,
                #12 / 31 / 9999 #,
                DateSerial (
                    DatePart("yyyy", expsub.SafetyExpiration),
                    Month(expsub.SafetyExpiration) + 1,
                    1
                ) -1
            )
        FROM
        SERVICE AS expsub
        WHERE
            expsub.EquipKey = Equipment.EquipKey
            AND expsub.SafetyExpiration IS NOT NULL
        ORDER BY
            expsub.ServiceKey DESC
    ) AS [Safety Expiration],
    (
        SELECT
            TOP 1 Iif(
                DatePart("yyyy", expsub.EmissionsExpiration) = 9999
                AND DatePart("m", expsub.EmissionsExpiration) = 12,
                #12 / 31 / 9999 #,
                DateSerial (
                    DatePart("yyyy", expsub.EmissionsExpiration),
                    Month(expsub.EmissionsExpiration) + 1,
                    1
                ) -1
            )
        FROM
        SERVICE AS expsub
        WHERE
            expsub.EquipKey = Equipment.EquipKey
            AND expsub.EmissionsExpiration IS NOT NULL
        ORDER BY
            expsub.ServiceKey DESC
    ) AS [Emissions Expiration],
    Equipment.PurchaseDate,
    Nz ([ServiceAlertSixMonths], False) AS SixMonthAlert,
    Nz ([ServiceAlertTwelveMonths], False) AS TwelveMonthAlert,
    Count(ServiceMain.datecompleted) AS TimesServiced,
    Max(ServiceMain.datecompleted) AS LastServiced,
    GetEquipmentIssues (
        Nz ([ServiceAlertSixMonths], False),
        Nz ([ServiceAlertTwelveMonths], False),
        Count([datecompleted]),
        Max([datecompleted]),
        [PurchaseDate],
        (
            SELECT
                TOP 1 Iif(
                    DatePart("yyyy", expsub.SafetyExpiration) = 9999
                    AND DatePart("m", expsub.SafetyExpiration) = 12,
                    #12 / 31 / 9999 #,
                    DateSerial (
                        DatePart("yyyy", expsub.SafetyExpiration),
                        Month(expsub.SafetyExpiration) + 1,
                        1
                    ) -1
                )
            FROM
            SERVICE AS expsub
            WHERE
                expsub.EquipKey = Equipment.EquipKey
                AND expsub.SafetyExpiration IS NOT NULL
            ORDER BY
                expsub.ServiceKey DESC
        ),
        (
            SELECT
                TOP 1 Iif(
                    DatePart("yyyy", expsub.EmissionsExpiration) = 9999
                    AND DatePart("m", expsub.EmissionsExpiration) = 12,
                    #12 / 31 / 9999 #,
                    DateSerial (
                        DatePart("yyyy", expsub.EmissionsExpiration),
                        Month(expsub.EmissionsExpiration) + 1,
                        1
                    ) -1
                )
            FROM
            SERVICE AS expsub
            WHERE
                expsub.EquipKey = Equipment.EquipKey
                AND expsub.EmissionsExpiration IS NOT NULL
            ORDER BY
                expsub.ServiceKey DESC
        )
    ) AS Issues,
    Equipment.Disposition,
    Equipment.EquipKey
FROM
    (
        SERVICE AS ServiceMain
        RIGHT JOIN Equipment ON ServiceMain.EquipKey = Equipment.EquipKey
    )
    LEFT JOIN
TYPE ON Equipment.EquipType = Type.Type
GROUP BY
    Equipment.EquipID,
    Equipment.EquipType,
    Equipment.Driver,
    Equipment.PurchaseDate,
    Nz ([ServiceAlertSixMonths], False),
    Nz ([ServiceAlertTwelveMonths], False),
    Equipment.Disposition,
    Equipment.EquipKey
HAVING
    (
        (
            (Equipment.PurchaseDate) < DateAdd("m", -12, Date())
        )
        AND ((Nz ([ServiceAlertTwelveMonths], False)) = True)
        AND ((Count(ServiceMain.datecompleted)) = 0)
        AND ((Equipment.Disposition) = "ACT")
    )
    OR (
        (
            (Equipment.PurchaseDate) < DateAdd("m", -6, Date())
        )
        AND ((Nz ([ServiceAlertSixMonths], False)) = True)
        AND ((Count(ServiceMain.datecompleted)) = 0)
        AND ((Equipment.Disposition) = "ACT")
    )
    OR (
        ((Nz ([ServiceAlertTwelveMonths], False)) = True)
        AND ((Count(ServiceMain.datecompleted)) > 0)
        AND (
            (Max(ServiceMain.datecompleted)) < DateAdd("m", -12, Date())
        )
        AND ((Equipment.Disposition) = "ACT")
    )
    OR (
        ((Nz ([ServiceAlertSixMonths], False)) = True)
        AND ((Count(ServiceMain.datecompleted)) > 0)
        AND (
            (Max(ServiceMain.datecompleted)) < DateAdd("m", -6, Date())
        )
        AND ((Equipment.Disposition) = "ACT")
    )
    OR (
        (
            (
                (
                    SELECT
                        TOP 1 Iif(
                            DatePart("yyyy", expsub.SafetyExpiration) = 9999
                            AND DatePart("m", expsub.SafetyExpiration) = 12,
                            #12 / 31 / 9999 #,
                            DateSerial (
                                DatePart("yyyy", expsub.SafetyExpiration),
                                Month(expsub.SafetyExpiration) + 1,
                                1
                            ) -1
                        )
                    FROM
                    SERVICE AS expsub
                    WHERE
                        expsub.EquipKey = Equipment.EquipKey
                        AND expsub.SafetyExpiration IS NOT NULL
                    ORDER BY
                        expsub.ServiceKey DESC
                )
            ) < DateAdd("m", 1, Date())
        )
        AND ((Equipment.Disposition) = "ACT")
    )
    OR (
        (
            (
                (
                    SELECT
                        TOP 1 Iif(
                            DatePart("yyyy", expsub.EmissionsExpiration) = 9999
                            AND DatePart("m", expsub.EmissionsExpiration) = 12,
                            #12 / 31 / 9999 #,
                            DateSerial (
                                DatePart("yyyy", expsub.EmissionsExpiration),
                                Month(expsub.EmissionsExpiration) + 1,
                                1
                            ) -1
                        )
                    FROM
                    SERVICE AS expsub
                    WHERE
                        expsub.EquipKey = Equipment.EquipKey
                        AND expsub.EmissionsExpiration IS NOT NULL
                    ORDER BY
                        expsub.ServiceKey DESC
                )
            ) < DateAdd("m", 1, Date())
        )
        AND ((Equipment.Disposition) = "ACT")
    );

Quite a doozy, right? Some of the reasons for the subqueries are to get the latest date out of a field of numerous tickets as well as various conditions to display different fields depending on the context of what is being looked at. For example, if there’s no tickets yet, it should show the purchase date as the last serviced date field. This is so it can be compared to the actual date and see if it should be included in the list based on whether a 12 or 6 month service flag is selected and if so, show it if required service is coming up. It actually combines multiple concepts and features of the database together.

Then you have the GetEquipmentIssues being called for each row using various date parameters to provide an English language human readable string for why it made the list (out of about 6 possibilities).

Frankly, I don’t really recommend writing queries like this. They are convoluted and complicated and changing something is not simple or easy. It would have been far better for me to break the queries out into different segments based on what is needed for each vehicle. We could place the final data for each into a temporary table and return the results from there.

This would make the logic easier to follow and place the various functional usages of the query into their own place, making it easier to maintain as well. I think the reason I ended up with this complex query is because it started relatively simple with just one check and then gradually had more added as time went on.

The reason I was working on it today was to change when it displays a warning of upcoming expirations from 15 days to 1 month. Thankfully it was an easy change. I had to update the end of the WHERE clauses and then update the public function to make sure it would display the right message as well. It was good that I looked at the function, because if I had not, I would have missed that it had the same criteria of 2 weeks to provide a message to the user.

In any case, if you normally do queries like this, perhaps you’ve also found it to be difficult to go back in and change them. With the new Access Monaco editor, it makes the SQL editing MUCH better than it used to be, but still… this query is just too darn big. Refactoring would be the wise choice for me here.