Assigning records in one table based on allowed categories listed in another table

[firebirdta84]

I need some assistance with assigned records in an Access 2010 database table, based on criteria found in another table in the same database.

Two tables for this example:

1. tbl_Master: Each record is a separate report. These reports are given one or multiple categories based on criteria that is pre-determined and not significant here.

2. tbl_Users: This is the master user list, with each record being a user and what categories they are allowed to work.

Currently, I have a form where the user will select the number of records they want assigned to them via a combo box, and it will update tbl_Master with their name in the "AssignedTo" field, where AssignedTo is null, ordered ascending by ID. This piece works perfectly, but it doesn't include the additional criteria of the categories.

A user can be assigned work in tbl_Master to any report as long as the given report does NOT include a category that the user cannot work.

Note that the categories in both tbl_Master and tbl_Users are not in any specific order.

See attached example.

How can I make sure that a user isn't assigned a Report that lists a category that they are unable to work?

Any guidance to steer me in the right direction would be much appreciated!

Thanks
Joe

 

[MajP]

Depends if you want to fix this and do it correctly or have a work around. The problem with a work around is it will give you a quick fix, but does not cure the underlying condition. If you fix it then it will make future efforts much easier. The fix is to normalize your data. You need

tblCategories
  categoryID
  categoryName
  other Category unique fields.

then you need two junction tables since these are many to many relationships

tblUsers_AllowedCategories
  UserID_FK
  Category_FK

so you would have records like

John Doe   1
Jane Smith 7
Jane Smith 8
Jane Smith 12
Jane Smith 15

Then you need

tblReport_Categories
  ReportID_FK
  CategoryID_FK

Now all your solutions can be done in pure sql without code.

If you join Reports to reports_categories by rerport_ID and then users_AllowedCategories by categoryID you would get a list of reports with allowed categories by user. Then filter of that user and where assigedto is null.

 

[MajP]

To do the work around you need a function to split apart both lists and then check each value of allowed categories against report categories to see if there is a match.

 Public Function ListInList(listOne As Variant, listTwo As Variant, Optional Delimeter As String = ",") As Boolean
  Dim arrListOne() As String
  Dim arrListTwo() As String
  Dim i As Integer
  Dim j As Integer
  'See if values from ListOne are in ListTwo. Ensure you do it in the correct order.
  If Not IsNull(listOne) And Not IsNull(listTwo) Then
    arrListOne = Split(listOne, Delimeter)
    arrListTwo = Split(listTwo, Delimeter)
    For i = 0 To UBound(arrListOne)
      For j = 0 To UBound(arrListTwo)
        If arrListOne(i) = arrListTwo(j) Then
          ListInList = True
          Exit Function
        End If
      Next j
    Next i
  End If
End Function

You could use this in a form or use it in a query to get each user and the reports allowed

SELECT 
 tbl_Users.User, 
 tbl_master.Report, 
 tbl_master.Categories, 
 tbl_Users.[Categories Allowed], 
 listInlist([categories allowed],[Categories]) AS ReportAllowed
FROM 
 tbl_master, 
 tbl_Users
WHERE 
  listInlist([categories allowed],[Categories])=True
ORDER BY 
 tbl_Users.User, 
 tbl_master.Report;

But this workaround only does one thing. Where if you normalized your data you are set to do many queries and data validation. Example the boss wants to see a access report showing each category and those who have permission. Or wants you to validate that every category has at least 2 people assigned. Or wants to ensure that categories 1, 12, and 15 are allowed by everyone.

 

[firebirdta84]

Thanks, MajP! I definitely don't want to do the workaround if there's a better, more efficient solution best suited for long term use. That being said, I am a bit confused with your first post on how to normalize the data. Can you elaborate a bit more on what I'd need to do?

Thanks!
Joe

 

[MajP]

Maybe I jumped the gun depending on what type of fields you have. Are your Categories and Categories_Allowed fields MultiValue fields or are they just text fields? If they are truly MV fields than your data is normalized behind the scenes and you can do this in sql. If they are text fields hodling a string "1,7,8,12,15" then you will need to add some child tables.

 

[firebirdta84]

They are text fields holding a string, currently.

 

[MajP]

A proper designed database does not store multiple values inside a single field. You severely limit your database's capability. A proper design is called "Normalized". You can google and read in detail on this subject. But if you do design your database correctly, everything afterwards is much easier.

So if I was properly designing your db
Tbl_Master would not have a Categories field. I would store that information as child records in another table.
Tbl_Users would not have a Categories_Allowed field. That is stored in a different child table.

You may already have this, but I would first have a categories table.

Name: Tbl_Categories
Fields:
  categoryID - A primary key. Has to be unique
  other fields that uniquely describe a Category like
  categoryName 
  categoryDescripton

Since each Report can have many categories and each category can be related to many reports I need a child table. Since it is a many to many it is a little tricky.

Name: TblReport_Categories
Fields:
   ReportID_FK - This is a foreign key relating a report to the report table
   CategoryID_FK - this is a foreign key relating category to a category table

So for your data you would have

ReportID_FK   CategoryID_FK
1             1 Report with ID of 1 (127) has cat 1
1             8 Report with ID of 1 (127) has cat 8
1             7
2             7
2             1
.....
7             12 Report with ID of 7 (191) has cat 12
7             15 Report with ID of 7 (191) has cat 1

Personally do not know why you need a seperate ID field in your tbl_Master because a report already has a unique number which would make a good key. Do not know why I would want report 127 to have an ID of 1. I would have its Report # be the primary key.

Now you would do the same with users and allowed categories. You need a unique UserID in your users table. I am guessing that your name field is your unique user ID (primary key), which is OK as long as you will never get duplicates like 2 John Smiths. May want to add a autonumber for ease.

Name:TblUsers_AllowedCategories
Fields: 
 UserID_FK - relates a user to the user table (unless you add a user ID to your user table this would have to be your name field)
 Category_FK - relates a category to the category table

So again you will have a record for every person and every category they are allowed

UserID_FK  CategoryID_FK
John Doe   1
Jane Smith 7
Jane Smith 8
Jane Smith 12
Jane Smith 15
....
Jake Houser 27

Unfortunately, many to many relations are a more advanced topic and you have two to start with. But if you fix your table structure then future efforts will be much easier.
One other thing. You may want to do yourself a favor and do not but spaces or special characters in field names.
Report_No not Report #
Categories_Allowed not Categories Allowed.
Also be descriptive since you will have multiple tables especially with primary keys
Report_ID not ID.

I would then probably have separate forms for adding and editing Categories, Users, and Reports. Then a form with a subform to add/edit child allowed categories to a user. Then a form to add/edit child categories to a Report. With these tables and relations you could easily build a form that allows a user to only select reports that no one is assigned and that they have permission.

Now all of this can actually be done with a multivalue field. It will build these child tables behind the scenes. However, because it is done behind the scenes it can really get confusing if you do not know the basics.