Frustrating three-table join problem

[The_Doc_Man]

In my attachment I have a relationship diagram for a problem we have at our site. We are a hosting site for several branches of the U.S. government. We are tracking the patches and security updates and other mandatory actions required to keep our systems certifiably secure. When you get over 600 servers, it is more likely to drive you certifiably nuts.

I was trying to build a query that involves three tables so that we could set up bulk updates of our action records in many cases. Here's the situation

tIAV is the Information Assurance Vulnerability alert table. It lists actions that must be checked for applicability against all servers (optionally qualified when the action is specific to only one operating system.) For servers where applicable, the action must then be performed.

tSRV is the list of servers. We have over 600. They fall into various categories based on what they do and what they run. Some UNIX, some Windows, a few others. Some web-only, some database-only, some applications-only, a few mixed-use.

tUSR is the list of users who can touch the database at all. I am using it to roll my own security at the form level. In that table, one of the roles (SARole) is System Admin (actually, code 2, but a rose by any other name...)

tSRVADM is a junction table that shows which servers are assigned to which administrators. An admin has either primary, secondary, or emergency duty. The primary admins have a backup (secondary) admin and in a few critical systems, we have a second backup (emergency) admin. In theory, any admin should be able to update records for any server to which they play one of these three roles.

tSRVIAV is a junction table that shows which IAVs apply to which servers - and also shows whether/when the work is done. Since IAVs don't apply to all systems, the junction is sparse. For example, UNIX servers do not need to be tested for a Windows IAV and vice-versa.

What I was trying to do is build a form from which my administrators could do bulk updates of their servers' action records. On this form, I know who they are based on their domain login identification and they use a drop-down to select a particular IAV to consider. Thus, by filtration I can build a list of servers administered by this administrator for which a given IAV applies and is not closed yet (SAClosed = FALSE).

So basically I want to write a query that selects a specific Action ID and a list of server IDs based on the servers assigned to the administrator whose ID I know. Trying to join tSRVIAV with tSRVADM, my end goal is a query that will let me include the SAIASelect field, a Yes/No that feeds a checkbox on the form. I also want to include the server's name in this query so there has to be a JOIN with tSRV across the SRVID fields so I can pick up that name.

If this worked as intended, the administrator would log in, open the form, select one IAV by its title in a drop-down, and get a sub-form with a list of the servers s/he administers for which that action is still pending. The admin could check selected boxes, use another drop-down to select a status, and do a bulk records-update. This is because many times the IAV says "patch program X" but only a few of our servers even TRY to run program X. With this bulk-select form, the admin could click "Does not apply" then check the box for every server to which the IAV does not apply, then one more "Apply" click. The records get updated, the tSRVIAV records get closed, and our reports reflect that action. That's the goal.

Here's the speedbump on that road. No matter how I try to write the query, it is not updateable. I'm trying a different tack right now involving a multi-select list box and some VBA to do recordset operations. Haven't finished that yet, but it is about the last "clean" approach available to me.

I'm wondering if someone sees an obvious reason why this fool query is not updateable.

Here are two relevant queries that DON'T work:

The SQL of "qServersWithAdmins" (list of servers and their administrators) is :

SELECT tSRV.SRVID, tSRVADM.SAUID, tSRVADM.SSRole, tSRV.SRVName
FROM tSRV LEFT JOIN tSRVADM ON tSRV.SRVID = tSRVADM.SRVID
WHERE (((tSRV.SRVActive)=True) AND ((IsNull([SAUID]))=False))
ORDER BY tSRV.SRVID, tSRVADM.SAUID, tSRVADM.SSRole;

The above query feeds this one. The SQL of "qIwSaA" (that I wanted to use to drive the form) is:

SELECT tSRVIAV.ActID, qServersWithAdmins.SRVID, qServersWithAdmins.SAUID, qServersWithAdmins.SSRole, qServersWithAdmins.SRVName, tSRVIAV.SAStatus, tSRVIAV.SAClosed, tSRVIAV.SAIASelect 
FROM tSRVIAV RIGHT JOIN qServersWithAdmins ON tSRVIAV.SrvID = qServersWithAdmins.SRVID
ORDER BY tSRVIAV.ActID, qServersWithAdmins.SRVID, qServersWithAdmins.SAUID;

I was going to further FILTER this query through the form to select by ACTID being a specific number. This would leave me a list of from 5 to 100 servers administered by one person and to which one IAV would be applicable.

From there, checking the SAIASelect box would have enabled me to run a simple query to go through and update the tSRVIAV records that were checked, then reset the checks. But even when I directly open the qIwSaA query, bypassing the form, I cannot manually check the checkbox.

I know this means something is ambiguous but I'm damned if I see it at the moment. Any ideas out there?

 

[Banana]

It seems to me that the problem lies in the fact you are joining in different directions.

In qServersWithAdmins, you have a LEFT JOIN, but you then do a RIGHT JOIN into qServersWithAdmins for the form's recordsource.

Another possibility is that the columns participating in the join is not indexed (more importantly, the fields in the join are unique and thus guaranteeing that there won't be a question of "geez, which row does that row map to?").

If you can't update qServersWithAdmins standalone, then any other queries building on this query won't, either. To be honest, I intend to avoid outer joins in queries that I use for forms- they usually cause more trouble than they're worth. I would be looking at whether I could re-write the query to use a INNER JOIN instead or move the criteria into a EXISTS clause so you can SELECT the columns with a INNER JOIN but retrieve only IDs that matches the criteria.

Hope that helps a bit.

 

[The_Doc_Man]

Normally Access would build the inner join automatically, but not when a query is involved in the second query. It apparently doesn't pick up the relationship.

I saw that I had a left join and a right join, but I was hoping that I was always set up uniquely. I'm still playing with it.

 

[Banana]

This is off my memory so I could be wrong but what I seem to recall is that when you mash together two outer join query, it's very likely that even if either return only unique values and were updatable individually, the act of mashing them together causes it to lose any unique and Access will have to ask the dreaded question, "which source row does this query row map to?"

For this reason, this is why I try to see if I can avoid doing any outer joins.

 

[The_Doc_Man]

I didn't think I was doing an outer, but I'll look at it again in the original and see if maybe my relationships are missing something.

 

[boblarson]

I didn't think I was doing an outer, but I'll look at it again in the original and see if maybe my relationships are missing something.

When the SQL says LEFT JOIN or RIGHT JOIN then those are outer (and each of the ones you showed had LEFT in one and RIGHT in the other). INNER JOIN is the normal join.