Unique values in a left join with multiple conditions

[Demiurgous]

Short version: Tracking insurance policies. Running a query to determine whether what we have on file:

1. Is current but doesn't meet requirements
2. Null
3. Is expired

I can do all three pretty easy, but the trouble comes when conditions one and three are met. We have received a new policy (that doesn't meet requirements) AND that particular company has a previous policy (expired) on file in the DB. How can I modify this Left Join to return only one record per company?

SELECT ActiveInsReqsSummary.BusinessLegalName, ActiveInsReqsSummary.CountOfContractCode, ActiveInsReqsSummary.AL, ActiveInsReqsSummary.ALPer, AL.ReqsMet, ActiveInsReqsSummary.ID, AL.PolicyEnd
FROM ActiveInsReqsSummary LEFT JOIN AL ON ActiveInsReqsSummary.ID = AL.Provider
WHERE (((ActiveInsReqsSummary.AL)>0) AND ((AL.ReqsMet)=False) AND ((AL.PolicyEnd)>Date())) OR (((ActiveInsReqsSummary.AL)>0) AND ((AL.Provider) Is Null)) OR (((ActiveInsReqsSummary.AL)>0) AND ((AL.PolicyEnd)<Date()));

ActiveInsReqsSummary is a query of all current contracts that summarizes their insurance requirements.
ActiveInsReqsSummary.AL is a count of how many contracts, per company, require a particular type of insurance.

AL is a table where I store a particular type of insurance information.
AL.ReqsMet is a simple True/False calculated field that says whether the requirements are met.

The trouble I have is when the first condition is met, indicating that an active policy doesn't meet requirements:

WHERE (((ActiveInsReqsSummary.AL)>0) AND ((AL.ReqsMet)=False) AND ((AL.PolicyEnd)>Date()))

AND the third condition is met, indicating a policy on file has expired:

OR (((ActiveInsReqsSummary.AL)>0) AND ((AL.PolicyEnd)<Date()))

This results in a company being listed in the query twice. In this case, I'd prefer to drop the second requirement, as the current policy is most important. Can I do this somehow with a MAX(PolicyEnd) that will still be unique per company?

And I also have to find a way to combine this idea with 6 other tables (7 total tables of various insurance types).

Any help is greatly appreciated. Please let me know if I need to clarify anything, or if another method would be a better approach.

[June7]

First of all, why are there separate tables for insurance types? Are the fields different?

So you want only the records that have not expired? Seems like just need to drop the OR criteria expression.

[Demiurgous]

First of all, why are there separate tables for insurance types? Are the fields different?

Each table has one or two unique fields. There is enough similarity that they could reside in a single table, and unique fields would have a null value. I'd add a field to indicate the type of policy.

Question for you on this: I had read about data normalization, and the mantra of "Normalize until it hurts, denormalize until it works" kind of stuck in my head. Did I go a bit overboard here? The largest table has 22 fields, so combining all 7 would result in maybe 33-35 fields, tops. Would this be recommended?

So you want only the records that have not expired? Seems like just need to drop the OR criteria expression.

I do want expired records, if a newer record for that company does not exist.

[June7]

I like that mantra phrasing, it's what I follow. It is a balancing act between normalization and ease of data input/output. I tolerate a LOT of null fields for the sake of my sanity. Combining the 7 tables is what I would do. Can't be sure without knowing more about the data, but some of those unique fields might even be combined somehow.

The filter requires criteria that considers a value in another record of same table. That is always tricky. Might require a nested subquery or a DLookup(). Will get back to you.

[Demiurgous]

I like that mantra phrasing, it's what I follow. It is a balancing act between normalization and ease of data input/output. I tolerate a LOT of null fields for the sake of my sanity. Combining the 7 tables is what I would do. Can't be sure without knowing more about the data, but some of those unique fields might even be combined somehow.

It's all pretty simple stuff, really. Pull out your insurance card for auto insurance to get an idea. Insurer, effective dates (begin and end), policy number, coverage limits, etc. Tie back to individual companies with relationships.

It would be pretty easy to throw it all into one table. I'm not sure how it would affect the queries, as the same result would still happen. However, I'm sure it's easily surmountable.

I admit to being a complete DB noob all around. This is really my first venture into it (at least in Access. Did some pretty simple web-based MySQL with Pythn and Django in the past). Pretty pleased so far, despite the quirks of M$.

The filter requires criteria that considers a value in another record of same table. That is always tricky. Might require a nested subquery or a DLookup(). Will get back to you.

Yup. I thought about just doing a couple of queries, and then querying those queries to a final results that can be used in Reports (really letters to be mailed/emailed). I just hate doing things that way, because I feel like it's really crappy design to have helper queries. And what's easiest isn't always what's best, either in approach or performance.

That said, I again defer to the experience of those who travel this path far more often than I.

[June7]

This gets really complicated if company can have multiple expired and multiple current. What did Null from item 2 pertain to?

Want to provide some sample data? Post in thread or attach file. Follow instructions at bottom of my post.

[Demiurgous]

Firewall blocks outbound data, so I'll do my best to give a sample layout of the data and an explanation.

ActiveInsReqsSummary is just a query (that can be replaced, but let's forget that for now). This query simply provides a sum of insurance requirements for all active contracts, summed by company. So it would look like:
Company | Count of Active Contracts | Sum of contracts where Type 1 (AL in this case) is required | Sum of Type 2 | ... | Sum of Type 7

What I'm trying to do is match that against each type of insurance (again, residing in its own table). So I need to find:
Companies with an active policy on file that doesn't meet our requirements; and
Companies without an active policy on file

Now, each Insurance Type table has a look like:
PrimaryKey | Company | Insurance Producer | Policy Number | Policy Begin | Policy End | (Various pertinent coverage fields) | ReqsMet

That last ReqsMet field is calculated, where it looks all of the information to determine if requirements of met.

So it's pretty easy to run a query to grab active policies (Begin <Date() AND End >Date() ) where requirements aren't met ( AND ReqsMet = False )

To get to your latter question, the Null is handy here, as it lets me know if a Company has a requirement, but is not in the AL table.

What I'm struggling with is how to return expired policies (End < Date() ) but ONLY when that company isn't already in the first criteria. Perhaps nesting or helper queries, but even then I'm not 100% positive on how to approach it.

And again, if there's a better method, I'm all ears. I'm taking a look at combining the policy tables, but even then I'll still have the same issue of how to return the right data correctly.

And if it makes the difference, the data will be used to load into reports in Access: one for internal use and verification; the second to send out love letters of non-compliance.

Really appreciate the help!

[June7]

You are not able to attach a zip file to post?

I need some structure and data to test with. I don't want to build from scratch. If cannot attach file, can build tables in post with the Advanced post editor.

[Demiurgous]

ProviderID	CountOfContractCode	CL	AL	WC	EL	PL	SL
1	11	11	11	11	11	11	11
2	5	5	5	5	5	5	5
3	3	3	3	3	3	3	3
4	5	5	5	5	5	5	3
5	1	1	1	1	1	1	1
6	2	2	2	2	2	2	2
7	3	3	3	3	3	3	3
8	3	3	3	3	3	1	3
9	2	2	2	2	2	2	2
10	8	8	8	8	8	8	8

ID	Provider	Producer	PolicyNumber	PolicyBegin	PolicyEnd	ReqsMet
121	1	37	PHPK1038750	7/1/2013	7/1/2014	True
1	2				6/1/2012	False
2	3				10/1/2012	False
143	3	31	201300967NPO	6/25/2013	6/25/2014	False
3	5	38	CAP2253638	6/1/2013	6/1/2014	False
115	6	22	2013-08472-NPO	7/1/2013	7/1/2014	True
4	7	31	201307347NPO	4/1/2013	4/1/2014	False
5	8				9/2/2012	False
6	9				9/18/2012	False
7	10				10/17/2012	False

Even zipped files are blocked.

So a query should do this check:
Where ActiveInsReqsSummary.AL > 1 (All 10 providers)

Providers with no policy entered into AL (Strictly Provider 4 is missing, and should thus be in my query results)

Providers with an active Policy on file that does not meet requirement:
Where AL.ReqsMet = False AND PolicyEnd > Date ()

The trouble comes when I also want to check for expired policies without something more recent. Provider 3, if you use my previous SQL statement from the first post, shows in my query results twice:
ID 2 can be dropped from the query results because ID 143 is more recent.

Should I just query the most recent AL Policies per provider, and then I can return WHERE ReqsMet=False OR WHERE AL.Provider Is Null?

That may be the easiest way.

[June7]

This is somewhat confusing: Providers with no policy entered into AL (Strictly Provider 4 is missing, and should thus be in my query results)
If there is no record then cannot be in query results. If you want provider 4 to show then need to join AL table to Providers table (join type "Include all records from Providers and only those from AL that match").

Just pulling the most recent AL policy per provider might be the solution. However, listing all providers even if there is no policy is complication which requires a join described above to be done at some point.

This will probably involve a series of queries (or nested queries) or domain aggregate.

Pulling the most recent policy could possibly be done with a TOP N per group nested query. Review: http://allenbrowne.com/subquery-01.html#TopN

Alternative is to use DMax, like:

SELECT * FROM AL WHERE ID = DMax("ID", "AL", "Provider=" & Providers.ID);

or

SELECT * FROM AL WHERE PolicyEnd = DMax("PolicyEnd", "AL", "Provider=" & Providers.ID);

[Demiurgous]

This is somewhat confusing: Providers with no policy entered into AL (Strictly Provider 4 is missing, and should thus be in my query results)
If there is no record then cannot be in query results. If you want provider 4 to show then need to join AL table to Providers table (join type "Include all records from Providers and only those from AL that match").

The way I have the query structured in the OP works beautifully, and does indeed return 4.

But it also returns 3 twice.

Appreciate the link for a TOP N. I may tinker with that and see what I can come up with. I'd hate to add a separate query just for DMax, and then query a query. I hope that makes sense. Just seems bad form, even if much easier in the long run. At any rate, I'll play with TOP N and see what I can do.

[June7]

Consider:

SELECT Providers.ID, AL.Provider, AL.PolicyEnd
FROM Providers LEFT JOIN AL ON Providers.ID = AL.Provider
WHERE (((AL.PolicyEnd)=DMax("PolicyEnd","AL","Provider=" & [Providers].[ID]) Or (AL.PolicyEnd) Is Null));

Domain aggregate functions are known for slow performance with a lot of records.

[Demiurgous]

Works like a charm! Really appreciate that.

Domain aggregate functions are known for slow performance with a lot of records.

Now we can get into design a bit then. I have a few questions on this statement.

Is performance roughly the same if all records lie in one table, versus querying seven tables? Same records, really. Curious how I can design things to perform better.

Is TOP N considered a domain aggregate function? Any idea how the performance compares to DMax?

[June7]

TOP N is not a domain aggregate function. It is SQL. Don't remember ever directly comparing with TOP N but I have see domain aggregates completely stall a query to the point had to quit Access with Task Manager.

Query performance with 7 tables should not be slower but can cause database bloat. 7 tables - does that mean 7 of everything to get things done - queries, forms, reports? Or more VBA to make one set serve?

What if you want to retrieve all records from over the years for a particular company or policy type or whatever? Now have to use a UNION query to get records into a single dataset.

[Demiurgous]

June, really appreciate all of that!

I'll be working on combining the tables into one, and nesting TOP N into my query. Right now, data is fairly limited. The DB is pretty new, so combining things will be fairly easy. The trouble, obviously, is when we get to the point of having a few years of data for several hundred companies (350 companies or so a year, 500 contracts, and between 3 and 7 insurance policies per company, combined with logging waiver requests and approvals, and notices sent....).

But hey, I'd rather go through a bit of work now to have the best set up possible once we get to the point of having much more data.