How to identify if records have correct status based on values (by year) in second table?

[MsAxes]

Hi all



I have the following tables:

Table Account_Records which can have about 24k records depending on the day of the month (I have no control over this table).
Table Montior_File which can tell us if the customer was 'monitored' in that specific year. (A table I created).

The goal is to identify if the customers in the 'Account_Records' table were properly marked to be 'monitored' for that specific year that is in the Monitor_File table. I can't think of what formula (not sure if a macro is needed) would look at the rows/columns to do this.

Any guidance is welcomed.

I attached an excel file of what the end results should be (see column E).

[June7]

Consider this approach which works with your current structure:
SELECT Account_Records.cust_id, Account_Records.Account, Account_Records.Year_Opened,
Nz(DLookUp("[" & [Year_Opened] & "]","Monitor_File","cust_id=" & [cust_id]),"N") AS Status,
IIf([Monitor_Status]=[Status],"Correct","Incorrect") AS MonitorStatus
FROM Account_Records
ORDER BY Account_Records.cust_id, Account_Records.Year_Opened;


Another approach requires rearranging Monitor table to a normalized structure, which would look like:

cust_id	Status	Yr
456123	Y	2012
456123	Y	2013
456123	Y	2014
456123	Y	2015
456123	Y	2016
456123	Y	2017
456123	Y	2018
456123	Y	2019
456123	Y	2020
456123	Y	2021

I used a UNION query to rearrange the data to that structure. There is no designer/wizard for UNION, must type or copy/paste in SQLView. Limit of 50 SELECT lines. UNION query looks like:

SELECT cust_id, Nz([2012],"N") AS Status, 2012 AS Yr FROM Monitor_File
UNION SELECT cust_id, Nz([2013],"N"), 2013 FROM Monitor_File
UNION SELECT cust_id, Nz([2014],"N"), 2014 FROM Monitor_File
UNION SELECT cust_id, Nz([2015],"N"), 2015 FROM Monitor_File
UNION SELECT cust_id, Nz([2016],"N"), 2016 FROM Monitor_File
UNION SELECT cust_id, Nz([2017],"N"), 2017 FROM Monitor_File
UNION SELECT cust_id, Nz([2018],"N"), 2018 FROM Monitor_File
UNION SELECT cust_id, Nz([2019],"N"), 2019 FROM Monitor_File
UNION SELECT cust_id, Nz([2020],"N"), 2020 FROM Monitor_File
UNION SELECT cust_id, Nz([2021],"N"), 2021 FROM Monitor_File
UNION SELECT cust_id, Nz([2022],"N"), 2022 FROM Monitor_File;

Can use the UNION to create a new table in that structure.
Now use the UNION query (or new table) in another query:

SELECT Account_Records.cust_id, Account_Records.Account, Account_Records.Year_Opened, Account_Records.Monitor_Status, MonitorUNION.Status, IIf([Monitor_Status]=[Status],"Correct","Incorrect") AS MonitorStatus
FROM MonitorUNION INNER JOIN Account_Records ON (MonitorUNION.Yr = Account_Records.Year_Opened) AND (MonitorUNION.cust_id = Account_Records.cust_id)
ORDER BY Account_Records.cust_id, Account_Records.Year_Opened;


Domain aggregate functions and UNION queries can slow performance and use of either indicates a poorly designed schema.

[MsAxes]

This works! Of course you knew that - shame on me for not ever learning Union Queries.

So are Union Queries used to change the data structure? Is there any other use for them?

I'm a bit lost with your last comment with aggregate functions, I don't see the queries you provided do that.

[June7]

UNION query can rearrange a single table's fields or merge multiple tables. Either is essentially manipulating structure.

I made several edits to post, perhaps you read it before I edited. The first SQL has DLookup() function.

[MsAxes]

I see - I've never been a fan of Dlookup with the aggregate functions because it does tend to slow down the query as you stated earlier.

I appreciate your help and quick lesson in Union Queries - I'm YouTubing them now. This knowledge could have helped in other cases.

Thanks again