So I created a database (1 Viewer)

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
So I created a database. Looking for insight to whether or not I am on the right track and where to go next.
The need rose from gather fire call data in a rural volunteer fire dept. Most medical calls are responded to personnely, no fire apparatus. An incident is recorded with Google Forms that I put in to Google sites (web page). that data is downloaded and parsed automatically when the Access is opened and every 12 hrs if the the program is left open.
My database is messy, old queries that I used to step me to the ones that worked, same with reports and forms. Not constructed for the masses, just for me to get the data and puke it out for our local governing bodies. So this is where I am at. I am lost within my own mind :)
1596292449322.png
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:31
Joined
Feb 28, 2001
Messages
17,334
The first thing I would do is find a large dry-erase board. Get some suitable markers and some sticky-note pads. IGNORE that diagram and go back to first principles. Draw out diagrams of what you track. You said you had a bunch older queries. I see some things in that diagram that have the word "DELETE" as part of the title. Working with what you showed us, I can understand your concern because you are surely caught in the "can't see the forest for the trees" trap. So don't work in that environment. Plant a forest that doesn't have a tone of underbrush blocking your vision. (I tend to like colorful turns of phrase, forgive me if I get heavy-handed with it...)

Just remember, Julius Caesar knew what he was doing with his "Divide and conquer" strategy. Break down your problem into smaller parts that you will re-join to each other later. Right now you are overwhelmed by this monolithic task.
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
The first thing I would do is find a large dry-erase board. Get some suitable markers and some sticky-note pads. IGNORE that diagram and go back to first principles. Draw out diagrams of what you track. You said you had a bunch older queries. I see some things in that diagram that have the word "DELETE" as part of the title. Working with what you showed us, I can understand your concern because you are surely caught in the "can't see the forest for the trees" trap. So don't work in that environment. Plant a forest that doesn't have a tone of underbrush blocking your vision. (I tend to like colorful turns of phrase, forgive me if I get heavy-handed with it...)

Just remember, Julius Caesar knew what he was doing with his "Divide and conquer" strategy. Break down your problem into smaller parts that you will re-join to each other later. Right now you are overwhelmed by this monolithic task.
Scared to delete items since I was in a pinch to catch up with needed info. So I bulldozed a pile and and moved to the next disaster, creating organized chaos of piles. I am not currently chasing the next disaster so I can take a breath and see the damage I have done. So now, IT WORKS-DON"T TOUCH IT MAY FALL DOWN
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:31
Joined
May 21, 2018
Messages
3,267
A couple things.
1) NO spaces in any names in any access objects. That includes table names.
2) You have a fire dept delete table, which looks like deleted records. This is a common discussion point, but the normal recommendation is not to delete and move to another table, but simply tag the record deleted.
3) This does not matter, but may make your relationship window cleaner. You seem to add the table 43 Fire Dept multiple times, and you probably just need one table and then make the links to there. There is a big difference between creating a relation and a sql join. You do not have to build all the joins in the relationship window. You need to do relationships only for referential integrity. See @isladogs excellent discussion.
Recommend everyone read it.
4) Do not over normalize. I often see people have a table that has a single field and an ID. Like
1 Red
2 White
3 Blue
If that is all the data, IMO just store the text in the data table. You gain little doing it this way. I kid you not, someone sent me a db with a Priority look up table
PriorityID Priority
1 1
2 2
3 3
..
5 5

One of your tables looks like that.
5) If you want someone to look at this, you are going to have to move the tables around so it is not a spider web. Unlike other dbs, and db development tools, there is no autoformat to do this. But you can clean up to make more readable.
6) This is personal preference and others may disagree, but works for me. I never have the same names in 2 tables unless they are the same field (foreign key) Just gets real confusing writing queries and code. I would NEVER EVER have a primary key in two different tables with the same name and non descriptive
NEVER "ID" instead
Person_ID
FireStation_ID
etc.

Personally I like to tag any foreign keys.
As a PK it would be FireStation_ID
in another table as a foreign key I would
FireStation_ID_FK

In this case I would even have
Person_Address
and
Station_Address
(same for the other names)

FYI, If you rearrange to clean up the spider web it probably will not show in a single screen shot. Just scroll over and take a couple screen shots. Still will be a lot easier to get any feedback. Also make sure to expand all the tables to show all the fields.
 

mike60smart

Registered User.
Local time
Today, 07:31
Joined
Aug 6, 2017
Messages
579
Hi
Are you able to upload a zipped copy of the database?
 

Isaac

Well-known member
Local time
Today, 07:31
Joined
Mar 14, 2017
Messages
1,513
Maj gave some excellent advice. Tiny divergence, I name PK's, always, just ID. But I name the foreign key that contains that value, something that refers to the parent table + ID. To me that makes for readable sql
Code:
select
    *
from
    parent
    inner join child on parent.ID = child.parentID
I have seen this approach a lot in corporate data warehouses as well.

I think it is fairly common for people to give a disproportionate amount of attention to Relationships. It's not like they're going to help you in joins, as Maj pointed out, you'll still have to make a judgment call about the proper join at the time of creating the query. And it is NOT a foregone conclusion that every time you join, it will always only be one way. Sometimes you'll want an inner join, sometimes you'll want a left join, sometimes you may want to join on another column for an ad hoc reason.
Much is made of the cascading deletes on enforced integrity, but how often should you be 'deleting' records anyway ??
IMHO, the relationships window biggest value is probably just for display and demonstration purposes.....But then again, if you want to create an ERD, you might as well just do that, and not necessarily in that clunky window.
Which is leading into: Reinforcing what Doc said about leaving that window behind for a while to essentially lay out your stuff in non-Access terms at first. My focus would be 100% on identifying ENTITIES / EVENTS vs. ATTRIBUTES. If you get that perfect on paper, it will naturally inform what the tables ought to be.
 

mike60smart

Registered User.
Local time
Today, 07:31
Joined
Aug 6, 2017
Messages
579
Hi Falcone

OK can you describe how you need to record information about a specific Fire
 

mike60smart

Registered User.
Local time
Today, 07:31
Joined
Aug 6, 2017
Messages
579
Hi Alan

What does tblRuns and tblFF record information about?
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
Hi Falcone

OK can you describe how you need to record information about a specific Fire
The main start of the database came from when I worked for Arkansas Forestry, I needed to report local fire departments and Forestry equipment in use. tblPer and "43 Fire Dept" were the main two tables. the database sat for years when I left forestry and became a paramedic and volunteering on two local fire depts. For the record, I am a 20 plus year USAF retired C-130 maintenance troop.

The NIFRS folks may need more info but I limited it to the basics, i.e. Structure Fire, Medical, Grass/brush etc. NIFRS has more info than what I am gathering. As for specific fires. Every one fire is different. Structures, dollar loss, exposures, fatality. These we pray are scarce. We have more livestock than residences. But we are a tourist attraction, we have a state park in our response area. Boost one up for mountain rescues.

Our local fire board started an "Incentive Program (IP)", firefighter would be reimbursed for calls they run on. I was put as Chief, I declined due to work schedule, but the VFD members would not let me. So, to retain members, using the IP as a retainment and recruitment tool I gathered info for all calls, not just fires. IP are reported every quarter.

I could have used a spreadsheet and hack marks, but we are so rural the only time we get a truck, where our run sheets were, is on major rescues and fires. So I implemented the use of Google sheets for tracking run info.

I scrambled and rushed this as a priority to keep the five members we had, and seems to have paid off, we now have 17 members.

So now the board pushes down new requirements and stipulations requesting training info, so I create and link that info.
As chief, I need to report all inventory, gather and create that.

So the monster has grown. I don't have much time to devote to the dept so I worked fast and hard so I would not have to. Trying to take care of our dept personnel.
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
Hi Alan

What does tblRuns and tblFF record information about?
tblRuns are the calls we run on, to include, type of call, actions taken, mutual aid, apparatus used, responders and the Incident Commander or "Fall Guy".
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
A couple things.
1) NO spaces in any names in any access objects. That includes table names.
2) You have a fire dept delete table, which looks like deleted records. This is a common discussion point, but the normal recommendation is not to delete and move to another table, but simply tag the record deleted.
3) This does not matter...
Realizing that as the monster grows. Trying to have a naming convention, but I rushed to get things done in between work.
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
The first thing I would do is find a large dry-erase board. Get some suitable markers and some sticky-note pads. IGNORE that diagram and go back to first principles. Draw out diagrams of what you track. You said you had a bunch older queries. I see some things in that diagram that have the word "DELETE" as part of the title. Working with what you showed us, I can understand your concern because you are surely caught in the "can't see the forest for the trees" trap. So don't work in that environment. Plant a forest that doesn't have a tone of underbrush blocking your vision. (I tend to like colorful turns of phrase, forgive me if I get heavy-handed with it...)

Just remember, Julius Caesar knew what he was doing with his "Divide and conquer" strategy. Break down your problem into smaller parts that you will re-join to each other later. Right now you are overwhelmed by this monolithic task.
Well noted
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
Hi Alan

What does tblRuns and tblFF record information about?
Forgot to answer tblFF, which are the personnel. Originally created for one dept (Petit Jean). Now, I see it should have been a tbl all depts could choose from. One FF to many Depts. Currently have to copy my own data for the two depts I am on.
 

mike60smart

Registered User.
Local time
Today, 07:31
Joined
Aug 6, 2017
Messages
579
Hi Alan

You say " tblFF which are the personnel"
Then you have another table named tblPer with a Primary Key of ContactID

Can you explain the relationship between these 2 tables?
 

Falcone203

Member
Local time
Today, 09:31
Joined
Dec 5, 2019
Messages
38
Hi Alan

You say " tblFF which are the personnel"
Then you have another table named tblPer with a Primary Key of ContactID

Can you explain the relationship between these 2 tables?
Contactid are the original contacts for all dept in the county. Firefighters were assigned, or added to that contactid
 

mike60smart

Registered User.
Local time
Today, 07:31
Joined
Aug 6, 2017
Messages
579
Hi Alan

OK So tblRuns is dealing with Details of a Callout to an Incident
You then have a field in tblRuns called Responded which you have set as a Multi Value lookup with tblFF (So tblFF is tblAvailableFirefighters)

This is not the recommended method of dealing with this scenario.

You should have your Main Form based on thlRuns with a Subform that deals with the many Firefighters who responded.

I will give you an example in a while
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Feb 19, 2002
Messages
29,179
I disagree with Issac regarding the naming of PK's. I recommend giving them meaningful names so that it is easier to match them to their corresponding FK's which should use the identical names whenever possible. The only time this isn't possible is when you have multiple references to the same table such as state or when you have a self referencing relationship.

I'm not sure what your actual question is though.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom