Relationships Between Linked Tables

[gunslingor]

I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management. To give you an idea:


So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:

Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.

So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.

So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:


But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.

Here are the issues. So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result:


But of course, for this form to be useful I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:


Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed:
Patch_key control source to Patch_ID and
Device_key control source to UNID (which is in the cyber assets table)

Here's the result:


As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.

Now the question: what am I doing wrong? lol. Is there a better way to do this? Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.

I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.

Any help is appreciated.

 

[Pat Hartman]

You can't enforce RI between tables in different databases. That is why your join is undefined. I would keep the tables in the same BE unless you have size issues.

I would need to see the table data to see why your form isn't working.

If you intend to use this form for updating, the first two controls will need to be combos and the RecordSource should only include the junction table.

 

[gunslingor]

You can't enforce RI between tables in different databases. That is why your join is undefined. I would keep the tables in the same BE unless you have size issues.

I would need to see the table data to see why your form isn't working.

If you intend to use this form for updating, the first two controls will need to be combos and the RecordSource should only include the junction table.

Good point about the combo boxes, but basically I'm not there yet. The problem is that form appears unable to pull data from the linked table, at least based on the values in the junction table.

 

[gunslingor]

Okay, I pretty much merged the databases and I've gotten everything to work as it should, or at least a lot closer to what it should be. It's pretty much as described above, except merged.

One question: In my junction table... how can I auto-populate it (I realize usually this isn't possible, but I need it in this case)? To clarify, I have these relationships defined (except the undefined relationship type has been corrected by the merge):


I then have a form who's record source is all the fields in the Cyber_Assets table, and nothing else. This form has a subform. This subform is primarily based on the tbl_Patches_Cisco, all fields, plus a field from patches_by_device called device_key (which should allow linking between the master and child forms). Now, in that subform I defined the record source relationship to include all records in table tbl_patches_cisco and only those records in table patches_by_device where the joined fields are equal (****in theory, this should display all patches in the subform even if an associated record doesn't exist in table patches_by_device; but it doesn't work). Currently, I can cycle devices in the parent form and all devices are shown; also, the subform works in that it shows all the patch data when the device key in the parent form (from the cyber_assets table) matches the device key in the child form (from the patches_by_device table).

So, this is very close to what I'm looking for, except now I need to essentially autopopulate the junction table. I realize that usually the DB wouldn't know which records to add, i.e. which patches to put with which devices, but in general for the moment, it should be all (or ideally, "all that have manufacturer equal to 'Cisco'"). It doesn't have to get put in the table patches_by_device until the device assessment is actually complete, but I do need it to show up in the subform, even though there is not yet a record in the junction table (so that people see it there and don't have to add it manually before completing the device specific assessment). The only way I can see to do this is either with auto population of the junction table or by implementing the item marked **** above, that did not work.

Any knowledge? You see what I'm trying to do right? Basically, this will get bigger and bigger as the project progresses... basically, on this form, I'm trying to show all devices in the parent form and to show all patches that are going to be applicable to a device in the child form, even if and especially if the patch hasen't been assessed yet (i.e., nothing in the junction table). What criteria determines what patches to show for which device? Simple, if you answered yes to the initial assessment and the patch vendor equals the device vendor. Make sense? Its like I need an Inner or Outer Join between the parent and child FORMS LINK FIELDS Property... but access doesn't have this functionality, so what do I do?

I suspect the only option is the following, but I'd prefer something else: basically, put a button above the subform called "check for patches" or something. When the user presses the button, it adds all relevant patches, for that device, to the patches_by_device table... this will allow me to dynamically determine what patches go with which devices as the project expands... but users may forget to press the button in all cases.

EDIT: I created the button and it works, but its slow... especially considering 1 insert for each patch*device = 500 * 200 = 100,000 inserts to do them all!!!... assuming a worst case scenario. Is there a better approach?

 

[Pat Hartman]

1. The query for the subform should NOT include the table of the parent mainform.
2. It doesn't make sense to me that all patches apply to all devices. If there are categories of devices and categories of patches, you can reduce the number of patches the user sees in his combo to only those for the matching category.
3. Are you talking about using this brute force method to populate the junction table initially but as new devices or patches are added, the user will pick and choose himself?
4. How "smart" is the application supposed to be - what happens when you add a new device? Is it supposed to go out and magically figure out which patches apply? What happens when you add a new patch? Is it supposed to go out and figure out which devices will be patched?

 

[gunslingor]

1. The query for the subform should NOT include the table of the parent mainform.
2. It doesn't make sense to me that all patches apply to all devices. If there are categories of devices and categories of patches, you can reduce the number of patches the user sees in his combo to only those for the matching category.
3. Are you talking about using this brute force method to populate the junction table initially but as new devices or patches are added, the user will pick and choose himself?
4. How "smart" is the application supposed to be - what happens when you add a new device? Is it supposed to go out and magically figure out which patches apply? What happens when you add a new patch? Is it supposed to go out and figure out which devices will be patched?

Thanks for all the questions =).
1. It does not. The form pulls all variables from table cyber assets and the subform pulls all variables from table patches_cisco, plus 3 fields from table patches by devices. The link between the child and parent is by device key, which appears in both the patches_by_device table and the cyber assets table...
2. That's the plan, yes. I can widdle it down further based on manufacturer, model number, operating system, services, etc.... so that I can reduce the 100,000 number down significantly. But it is complicated, each Vendor implements patching totally differently.... so the criteria will change and it needs to be dynamic...i.e. I'm not there yet because right now, its only capable of showing (in the subform) patches that have already been assessed for a device... i.e. if there's nothing in the junction table, I can't get the patch to show up in the subform... If I can just get them all to show up, then I can modify the resulting query dynamically as more ways to reduce the scope of work become apparent... but I cant even get to that point because it only shows the few that have been assessed currently.
3. No, whatever I do it'll be permanent. So basically any time cisco releases a new patch, we'll import the data into our DB. We'll then fill in a field called applicable (Y/N) and a couple others like timestamp, name, etc. When ever we answer Y, meaning we think its probably applicable, Nos are definitely not applicable, then further analysis is needed on a per device basis and this is the part we are talking about here. But to do this FINAL assessment, I need a form that shows devices and relevant data which has a sub form that shows all the relevant patches. The problem now is that it only shows the ones that have assocaited records in the junction table, so any amount of fancy querying isn't going to reduce the quantity down further... this is why I said, for now, all patches apply to all devices. If I can get this query made and put in the right spot, then I should be able to reduce it based on model number, OS, etc, further myself. Right now the button works in that in does the brute force job of adding all patches marked as 'yes, may be applicable" to the current form record, but only if the MFR of that device is cisco. It works great except I'd need to occasionally run thru every device record and press this button (or similar via code loop), but the code first runs a Dlookup to make sure its not already in there, followed by an INSERT query when it is.... so its kinda slow and I'm looking for alternatives. I know I can probably just populate the subform controls with VB a lot easier to get the results I want, but I've found when using access, its best to use the Toy-foo-foo way of doing things in Access... don't treat it like an SQL server.
4. lol, Ideally and eventually, yes, in some cases. The issue is complicated because every vendor is different, some things will be possible and some won't be. The actual assessments could be automated, but only if the vendor provides relevant assessment data in its patching tables like what model or OS it applies to (which is rare); instead cisco, for example, typically lists the vulnerability in paragraph form which would require a lot of brain power to convert into a more definitively query-able form.... nothing is off the drawing board for now, full automation is the desire.... semi-automation with the APPEARANCE of full integration with other network management software is the goal of this project to support security and CIP Compliance...

 

[Pat Hartman]

1. The query you posted shows THREE tables. It should only contain TWO.

As for the rest of it, you need to create append queries that append patches for each new device and that append new patches to each device. If you can categorize them to limit the updates, all the better. If you can't, I'm not sure what good the database will be since you aren't really "applying" anything. You are just using a shotgun blast to sort of identify where they MIGHT apply.

 

[gunslingor]

1. The query you posted shows THREE tables. It should only contain TWO.

Thanks... The query of the subform only contains two tables, the query of main form contains one table.... each table is different in this set of 3. However, in the database relationships, all three tables are linked together. The image I used above was an attempt to show the relationship without having to take another screenshot.

As for the rest of it, you need to create append queries that append patches for each new device and that append new patches to each device. If you can categorize them to limit the updates, all the better. If you can't, I'm not sure what good the database will be since you aren't really "applying" anything. You are just using a shotgun blast to sort of identify where they MIGHT apply.

Alright, so manually filling the table sounds like the right way to go regardless, good. Regarding eliminating the shotgun blast and NOT applying all patches to all devices, I'm still working on this, but I think the way it really needs to work is that a) complete general assessment to determine if we even have anything discussed in the patch b) after this, all those marked as yes, may be applicable, should be associated with every device from that vendor. c) widdle down this number even further with logic.... I'm thinking the safest thing to do from a compliance standpoint is to have every vendor patch associated with every vendor device... then when I want to widdle this down based on whatever logic and fields I can, rather than remove the association I'd set it to not applicable.

Thanks for the help.