can values in a table be used as criteria to filter a query

[Navy Ken]

A query may not be the best way to handle this.
I have many employees who can potentially work in many areas and areas can have many employees. So we need a joining table. I want to use a combo box to assign the employees to the areas (saved in the joining table) that they are authorized to enter but I want to avoid duplicate assignments. Is it possible to use the values from the AreasTable to populate the combo box but filter out areas they have already been assigned to that are saved in the joining table?

 

[GaP42]

Short answer: yes - using an 'is not in' query as the data source for the combo. Also employ a unique index on the combination of empId and WorkAreaID - although that may depend upon whether history is retained in that table.

 

[Navy Ken]

Not sure what you mean by history. While I am not a fan of deleting records, I do fully intended on deleting records in the joining table to remove employees from areas.

 

[The_Doc_Man]

The question about deleting assignments must always include you will at some future point have to ask the question, "Was Joe Schmuckatelli ever assigned to the Supply department?"

 

[Navy Ken]

The question about deleting assignments must always include you will at some future point have to ask the question, "Was Joe Schmuckatelli ever assigned to the Supply department?"

No, that is not something that needs tracking. I do have fields for when the employee was granted access and who authorized the access, but once they are removed form a work area it is, for the purposes of this database, a non-issue.

 

[Navy Ken]

The question about deleting assignments must always include you will at some future point have to ask the question, "Was Joe Schmuckatelli ever assigned to the Supply department?"

Doc, I noticed in another thread you were Navy. When did you serve?

 

[The_Doc_Man]

Doc, I noticed in another thread you were Navy. When did you serve?

I was always a Navy contractor doing system admin work for - primarily - Navy Reserve HQ, CNRFC, and later when the Navy rearranged stuff, with what was then called SPAWAR. But since US Space Force became real, they changed it again to reflect an "information system" orientation. I started in 1988 and retired in 2016, totaling 28 1/2 years continuous affiliation. I never actually served in uniform, but my assigned system was the one that fed data to DFAS to authorize paycheck disbursement for reservists. I stayed home and supported the war fighters.

 

[The_Doc_Man]

Is it possible to use the values from the AreasTable to populate the combo box but filter out areas they have already been assigned to that are saved in the joining table?

Several ways to do this. I don't know your comfort level with the coding that would be required, but here are a few ways to skin this particular cat.

You could place a unique compound key on the action that would make an entry and then trap any attempt to enter duplicates.

OR you can change the combo box recordsource to only select the combinations that have NOT been expressed. Look up the "NOT IN" clause and sub-queries to achieve this method. (Basically, let the combo only select the areas the person is NOT IN, with a sub-query for the record that person IS in. Sort of a backwards logic query.

OR you can probe the table by doing a DCOUNT() where the criteria would be the based on matching the Person's ID and the Area's ID, in which case if the count is not zero you would be attempting to enter a duplicate.

 

[ebs17]

Inconsistency search: All records from table A that are not in table B.
table A ... complete set = all combinations of employees and areas
table B ... existing set = your junction table

SELECT
   A.ID_Employees,
   A.ID_Areas
FROM
   (
      SELECT
         EM.ID_Employees,
         AR.ID_Areas
      FROM
         tblEmployees AS EM,
         tblAreas AS AR
   ) AS A
      LEFT JOIN tblEmployeesAreas AS B
      ON A.ID_Employees = B.ID_Employees
         AND
      A.ID_Areas = B.ID_Areas
WHERE
   B.ID_Employees IS NULL

 

[GaP42]

just a further thought about how this might be managed:

• Create the record if none exists when testing for the specific occurrence
• Set IsActive flag on the record if it does exist
• Clear the isActive flag when the occurrence is not needed, instead of deleting the record.

 

[Navy Ken]

Inconsistency search: All records from table A that are not in table B.
table A ... complete set = all combinations of employees and areas
table B ... existing set = your junction table

SELECT
   A.ID_Employees,
   A.ID_Areas
FROM
   (
      SELECT
         EM.ID_Employees,
         AR.ID_Areas
      FROM
         tblEmployees AS EM,
         tblAreas AS AR
   ) AS A
      LEFT JOIN tblEmployeesAreas AS B
      ON A.ID_Employees = B.ID_Employees
         AND
      A.ID_Areas = B.ID_Areas
WHERE
   B.ID_Employees IS NULL

Sorry EBS, I have a little experience with VBA, enough to where I'm not scared of it, and it tends to make sense to me. I have absolutely zero experience with SQL.

 

[Navy Ken]

Several ways to do this. I don't know your comfort level with the coding that would be required, but here are a few ways to skin this particular cat.

You could place a unique compound key on the action that would make an entry and then trap any attempt to enter duplicates.

OR you can change the combo box recordsource to only select the combinations that have NOT been expressed. Look up the "NOT IN" clause and sub-queries to achieve this method. (Basically, let the combo only select the areas the person is NOT IN, with a sub-query for the record that person IS in. Sort of a backwards logic query.

OR you can probe the table by doing a DCOUNT() where the criteria would be the based on matching the Person's ID and the Area's ID, in which case if the count is not zero you would be attempting to enter a duplicate.

which of those do you think is the least complicated and most closely follow database normalization best practices?

 

[The_Doc_Man]

which of those do you think is the least complicated...

DCount("*", "table-name or query-name", "( [UserID] = " & Me.UserID & ") AND ( [AreaID] = " & Me.AreaID & ")" )

or something similar to that conceptually speaking. Capture the count before you allow the entry to be saved. If the count comes back anything other than zero, that combination of UserID and AreaID is already in use in the junction table.

and most closely follow database normalization best practices?

None of them differ with respect to normalization that much. All of them would work about the same if your database is normalized properly. All of them would more or less have trouble with a non-normalized DB.

 

[Navy Ken]

DCount("*", "table-name or query-name", "( [UserID] = " & Me.UserID & ") AND ( [AreaID] = " & Me.AreaID & ")" )

or something similar to that conceptually speaking. Capture the count before you allow the entry to be saved. If the count comes back anything other than zero, that combination of UserID and AreaID is already in use in the junction table.



None of them differ with respect to normalization that much. All of them would work about the same if your database is normalized properly. All of them would more or less have trouble with a non-normalized DB.

Just to be clear this would be the control source for the combo box?

 

[The_Doc_Man]

Not necessarily. It would depend on what is making the entry in the junction table. You would put some code BEFORE the update. Since I can't see the form, I can't tell where you have this test. But it would be a structure similar in concept to this:

IF DCount( similar to the one shown above) <> 0 Then
    --- MSGBOX "Attempted entry of duplicate assignment", vbOKOnly, "Disallowed"
    --- GoTo DoSomethingElse
ELSE
    --- Do the Junction Table update
END IF

What else you do depends in detail on your form and your logic flow for this process, so I can't be more specific.

 

[Navy Ken]

Not necessarily. It would depend on what is making the entry in the junction table. You would put some code BEFORE the update. Since I can't see the form, I can't tell where you have this test. But it would be a structure similar in concept to this:

IF DCount( similar to the one shown above) <> 0 Then
    --- MSGBOX "Attempted entry of duplicate assignment", vbOKOnly, "Disallowed"
    --- GoTo DoSomethingElse
ELSE
    --- Do the Junction Table update
END IF

What else you do depends in detail on your form and your logic flow for this process, so I can't be more specific.

Would you be willing to have a look at what I have so far. I'll be up front my naming convention is a bit of a mess, still trying to get the hang of that. Also I am fairly new to Access and I'm operating outside my comfort box, which is good. If your comfortable your not learning.

 

[Gasman]

If your comfortable your not learning.

Or it could mean you actually understand it?

 

[ebs17]

I have absolutely zero experience with SQL.

Should it stay that way? SQL is the native language in a database, you can't ignore it permanently.
So it would be a good time to invest some effort in knowledge or run away and hand the whole thing over to someone else.

The query in #9, supplemented with a filter on the current ID_Employees, is assigned as a RowSource to the ComboBox in the form.
This would then offer all available and not yet assigned areas for a selected Employee to choose from. That is what a user needs. He only needs to select from the correct ones and cannot make any errors. In this way, one could also offer and implement multiple selection.

My criticism of the DCount suggestion is that it is a trial and error process. You think of an ID and check whether it is in the table and can be entered. This gets frustrating very quickly.

 

[The_Doc_Man]

Would you be willing to have a look at what I have so far.

I generally don't do that. I'll SOMETIMES take a peek but for various personal reasons, I limit my involvement at that level of detail. It's partly a matter of style and partly because I'm trying to avoid lengthy entanglements. No offense intended, but you know how things tend to drag on if you let them.

 

[Navy Ken]

I generally don't do that. I'll SOMETIMES take a peek but for various personal reasons, I limit my involvement at that level of detail. It's partly a matter of style and partly because I'm trying to avoid lengthy entanglements. No offense intended, but you know how things tend to drag on if you let them.

No, no I totally understand, that's why I asked before I threw the file at you.