Search Across Multiple Tables plus Fuzzy Relationships

[Denise2020]

I am trying to work this out in my head and so far I am having little luck and thought perhaps all the geniuses here might have some insight for me. I apologize ahead of time if I supply more information than needed but it does help to talk it out. Thanks for your patience!

Short story: I have a database with four main tables and forms for each. Ideally, I would like all four table groups to work together, but am struggling with how to set up those relationships to make it work.

Very long background:
I have a very complex (to me) database that I created for work as a total novice. I was given four gigantic and unwieldy excel documents years ago and originally created four databases. It is in use by four people. Over the years I have tried to read up on best practices (I am youtube-taught so my skills are about zero) to make (hopefully) improvements.

There was so much cross-over information in the four databases I decided to combine them into one, which has worked quite well. Right now it comprises:

1) an Ordnance Table (ammunition, rockets, bombs, mines, etc.) listing all objects in our files. Each object has an official ObjID# and ObjName for reference.

2) a Library Table listing all physical and digital media we have, including books, pdf files, physical discs, catalogues, and more that has to do with ordnance but often not just about one object, but many, sometimes none at all. A book might be about military in general and not reference any objects specifically; but a single book or catalogue could reference a hundred different items. There is no "ObjName" field specifically to simply create a relationship to in the Ordnance table and I am having trouble figuring out how to relate one book to multiple ordnance objects. This table currently has a "Notes" field where I list all objects that the book references (not pretty, but it works).

3) a Blueprints Table cataloging all blueprints (8000+ at the moment) of individual items of ordnance which are too large (A0 size) to fit in the file cabinets, which are scanned into a digital file. There is also no ObjName field here, HOWEVER, I am working on creating one. The person I inherited this list from had an insane excel file that no one could figure out as he retired and had notes only he understood. One Ordnance object can have several blueprints, but no blueprint can belong to more than one Ordnance object.

4) a Jobs Table of all requests for help/info we get from outside sources which creates a job for one of us, which we call support errands, and reference ammunition ordnance from the ordnance table. Many jobs can relate to one or many objects, or the same object several times. There is no specific ObjName field here either, and is typically referenced either in the JobTitle, JobDescription, or JobResult (my field names are different but I am writing for ease of reference).

So some examples:

- We have a book that references 28 objects and I would like to note on each of those objects that there is a book available that contains information on said object/s.

- We receive a request for help from the police or another military agency where they are trying to identify an object. Once we have identified it, a reference to that object should be made somehow (I think of this like a product and a sale, since there can be many "sales" (requests for information) on one single product).

So right now I have this all set up as four tables with four different forms but they don't work together. If i want to find an object in the #1 catalogue/file cabinets, I go to that form. If I want to see if there are also books in our library or digital files about that object, I have to switch over to the library form, and of course the same goes for Support Errands and Blueprints. I have yet to figure out how to create relationships as it isn't quite as simple as "Product Cheese", "Sales of Cheese", "Pictures of Cheese", and a list of "Books about Cheese", at least in my head.

Is there a way to do this? The easiest one I can think of joining is the blueprints to objects tables, but even that one I cannot really figure out which fields to join in relationships without creating a new field in the blueprints table specifically for referencing to the Ordnance Table. The Support Errands and Library are even more difficult for me.

So again, ideally, I would like all four table groups to work together, with one single search form, so that if I search one object name, not only do I get that object as a result from the ordnance table, but also can see if we have any books which reference that object, any blueprints, and if there are any current or past jobs that have referenced that object. Am I totally in over my head?

I am truly thankful for your patience and kind help in reading through all of this and lending your thoughts. Have a great day and looking forward to your replies!

 

[MajP]

The best thing would be for us to see you table structure. The easiest may be to copy each table and select "Strucuture Only" then post a database with those tables and no data. I assume this data may not be distributable due to its nature.
1. We can recommend how to clean up the table structure
2. How to move your existing data into the new structure
3. How to ensure proper database relationships
4. How to ensure data integrity

Sounds like you need to understand a many to many junction table.
Search that
Here is one tutorial

Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Let's revisit a popular aspect to your Microsoft Access Tutorial database design; Creating a many-to-many relationship. Firstly, watch this quick video which explain the key components and the starting steps to getting to the many-to-many state for your Access database.

accessdatabasetutorial.com

 

[Denise2020]

Thank you so much! I am attaching a copy of the backend database. Would you need the front end as well? I am simultaneously excited to hear your input and embarrassed to have you see what I have done as a novice.

Edit: I should note that there is a big of a language blend here, so there is some Swedish blended with the English, apologies for that. Table called tblKrigsarkivet is the one with blueprints. those with ptbl are "parent" tables with their own relationships to their respective child tables.

Three of the four main tables are here and the fourth is in a separate database (due to our current working from home/in office structure):

tblKrigsarkivet - blueprints
tblInfoReg - ordnance list
tblLibrary
tblstodarende - support errands (separate database)

 

[arnelgp]

are you contractor to a military?
i believe the military has what you need.
they have all sorts of Standards and your Inventory listing is among them.

 

[Denise2020]

are you contractor to a military?
i believe the military has what you need.
they have all sorts of Standards and your Inventory listing is among them.

I work in the military actually. I am the one (frightening enough as it sounds) upgrading the old lists to something slightly more usable.

 

[MajP]

My first look at this and it does not look too bad. I am pretty sure you mainly need some junction tables to do many to many. It seems like you have several of these needed. Once you make the junction table it is a little tricky the first time to make the form interface to support it. Once you see an example it is not that hard.

A book might be about military in general and not reference any objects specifically; but a single book or catalogue could reference a hundred different items. There is no "ObjName" field specifically to simply create a relationship to in the Ordnance table and I am having trouble figuring out how to relate one book to multiple ordnance objects

If a book can relate to many pieces or ordnance, and a single ordnance can relate to many books then you need a junction table for a many to many.

tblBooks_Ordnance
--BookID_FK   ' foreign key to book table
--OrdnanceID_FK  ' foreign key to ordnance table

so the following records

1    123
1    234
2    123
2    345
2    678

book 1 is related to ordnance 123, 234
book 2 is related to 123, 345, 678

You used descriptive names for some of you lookup table IDs.
ptblAmTypes ptblAmTypes

AmTypeID
ASW Ammunition
Bomb
Explosive Initiation Accessories
Explosives
Fuze/Firing Device
Hand Grenade
Landmine
Missile

ptblCountries ptblCountries

CountryID
Afghanistan
Åland Islands
Albania
Algeria
American Samoa
Andorra

Normally we would use an autonumber ID field. Then your "countryID" field would be "Country Name", and the autonumber field is "CountryID" This may or may not be an issue, especially in your case. If for example your name has a single apostrophe ' or a number # then that will cause issues. Since you have European characters (I think Swedish) it may or may not be an issue. Like I said, it may not be an issue, but if it was me I would add autonumbers and update the tables to the new foreign key.

 

[MajP]

here is an example of a Form to allow a many to many entry.

Creating multiple records from one form

Hi All, I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7) I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups]) The basic...

www.access-programmers.co.uk

Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet.

I will create a emp_training table as you described to me yesterday. my certificates is just a check box letting me know I received their certificate.

www.access-programmers.co.uk

 

[arnelgp]

i am surprised that you don't have any Standard for that matter.
if you can look onto another branch, maybe you will find that it was
really used after all. this proven standard existed even before we were
born.

 

[Denise2020]

My first look at this and it does not look too bad. I am pretty sure you mainly need some junction tables to do many to many. It seems like you have several of these needed. Once you make the junction table it is a little tricky the first time to make the form interface to support it. Once you see an example it is not that hard.


If a book can relate to many pieces or ordnance, and a single ordnance can relate to many books then you need a junction table for a many to many.

tblBooks_Ordnance
--BookID_FK   ' foreign key to book table
--OrdnanceID_FK  ' foreign key to ordnance table

so the following records

1    123
1    234
2    123
2    345
2    678

book 1 is related to ordnance 123, 234
book 2 is related to 123, 345, 678

You used descriptive names for some of you lookup table IDs.
ptblAmTypes ptblAmTypes

AmTypeID
ASW Ammunition
Bomb
Explosive Initiation Accessories
Explosives
Fuze/Firing Device
Hand Grenade
Landmine
Missile

ptblCountries ptblCountries

CountryID
Afghanistan
Åland Islands
Albania
Algeria
American Samoa
Andorra

Normally we would use an autonumber ID field. Then your "countryID" field would be "Country Name", and the autonumber field is "CountryID" This may or may not be an issue, especially in your case. If for example your name has a single apostrophe ' or a number # then that will cause issues. Since you have European characters (I think Swedish) it may or may not be an issue. Like I said, it may not be an issue, but if it was me I would add autonumbers and update the tables to the new foreign key.

Thank you SO MUCH! I cannot begin to tell you how much I appreciate your taking your time to look at this for me. You are right about the autonumber ID field. Many tables were made very early on and I haven't updated them all as switching to an autonumber and changing all related code and forms was a bit daunting to me, but I have been gradually changing them over. Ironically, my "Drawer table", which has only numbers, uses an autonumber ID. Go figure.

I will study the examples you provided and probably will have more questions but until then, thank you again so much for your time.

arnelgp, I am actually a little unclear by what you mean about there being standards about this kind of thing? Sorry that I don't understand. I can say that there are a frankly terrifying number of excel lists used at my job.

 

[arnelgp]

there are plenty of "standards" in the armed forces.
they are called Technical Manuals/Field manuals, etc.
you just need to figure it out what is for Armament Inventory system or the likes.

 

[Denise2020]

there are plenty of "standards" in the armed forces.
they are called Technical Manuals/Field manuals, etc.
you just need to figure it out what is for Armament Inventory system or the likes.

I see, yes, we have that for our own inventories. What we deal with in my department is outside the context of those manuals (foreign materials).

 

[MajP]

You are right about the autonumber ID field. Many tables were made very early on and I haven't updated them all as switching to an autonumber and changing all related code and forms was a bit daunting to me, but I have been gradually changing them over. Ironically, my "Drawer table", which has only numbers, uses an autonumber ID. Go figure.

An autonumber key is not necessarily better, but it is always as good or better as any other key. So they are always the point of least resistance IMO. They are streamlined, efficient, always available, never repeating, unique. "Natural Keys" can be fine, but not always. They require some thought process.
If the natural key has some weird charactoers (', or #) that may cause problems.
One example I had with a DB I developed was that the user wanted the employee id to be the PK. That sounded fine since it was simple and unique. The user called me back because they would enter an employee and would have to wait days until HQ assigned a new employee ID. So using an autonumber is always available at time of data entry. Most likely you will not have a problem, but you might. Do not know how umlauts and other special characters are handled in SQL.
Autonumbers are NOT necessarily better, but they are always Correct.