Hydrant Inspection Database Help

[Plan B]

I'm trying to figure out how to setup a fire hydrant inspection/maintenance database and was hoping I could get some help with the design. Basically what I have is a list of hydrants exported from our GIS database (less than 1500). Then I have an inspection form with about 30 fields that are done on every hydrant. Each hydrant is already given a unique asset-id number from the GIS database and is what we use for our inspection form to ID each hydrant.



I want to make sure I set it up as best I can from the start and I'm not a database guru by any means. My concerns are:
1) Inspections are done yearly, but periodically may have more than one inspection during that year for various reasons (damaged, service request by FD, etc). Each time work is done the field crews are required to update the inspection sheet.
2) We have several projects going on with some hydrants being deleted from our system while others will be added. I would need to update the master list maybe quarterly and add the new hydrants and make notes of those deleted from service.
3) We will also be collecting static pressure and flow rates at some point as well.


It seems easy enough, but I just want to cover all the bases. If you need more info let me know.

[rpeare]

First you need a table with your basic hydrant information

Your choice of a hydrant ID from the GIS system is a good one assuming those ID's will NEVER change, ever, ever, ever. If there's even a chance of them changing don't use it. If they're based on GPS code that's fine to as long as the code, once assigned can never change.

In this first table you want to store data that is not likely to change over time or can be updated once in a while, for instance street address is not likely to change.

You will also want an active/inactive flag on this table so that as hydrants are removed you can remove them from displaying on reports (for instance hydrants due for inspection)

Second you'll want a table that stores the inspection history
You would want a separate primary key (autonumber would work fine as a data type for this table) that will store the hydrant ID and all the inspection information for a particular maintenance review (your 30 pieces of data). If you do your 'additional inspections' and they also have the same pieces of data you can store them on this table as well, if the additional inspections are a different data set you will want a third table that stores the additional inspection information.

This way you can look at a history of inspections on a particular hydrant and as a new inspection comes due you can review the comments of the previous inspection for instance if rust was a problem on the previous visit in a particular part of the hydrant that would need extra inspection.

If the pressure and flow rates relate to the hydrant and is part of an inspection it can be on this table, if it's independent of the hydrants and you're actually measuring on the water line that's a different but similar setup.

[Plan B]

Thanks rpeare, sorry for the late response I go side tracked the last couple of days. What you described is basically the way I started to set it up, but it seemed too simple so I assumed I had missed something!

The Asset-ID will NEVER change, it can be removed from out GIS database but always stays in our service/work order system which assigns the asset-id.

I had not thought about an active/inactive flag. That should be just adding a new column with an active/inactive status? Then periodically compare my hydrant table with whats in the GIS table to match new hydrants/deleted hydrants.

The way it's looking now is static pressure will be apart of our current inspection so I will just add that field into the inspection table, while flow rates will be a seperate process and I think would go on a different table when/if that becomes our responsibility.

Thanks again for the help, I'm sure I'll have more questions as I get further along.

[rpeare]

I had not thought about an active/inactive flag. That should be just adding a new column with an active/inactive status? Then periodically compare my hydrant table with whats in the GIS table to match new hydrants/deleted hydrants.

Yes, having a text value of active/inactive will work as well, you really just need what amounts to a boolean value to tell you which items should appear on your reporting. Secondly, depending on the nature of your GIS table (data) you can probably update your list of assets without having to enter them by hand, if the data is available through a text file, data file, etc you should be able to import new unique records into your database, and likewise if you have an asset that no longer has a match in the GIS system you ccould mark it as inactive without the need for human intervention.

[rpsmsmith]

I work for a volunteer fire department in Arkansas and am trying to create a database for our hydrants, as you did in 2011. We don't have a GIS system and I'm a self-taught novice, for sure. We don't have funding to purchase something "off the shelf."

I was wondering if you were successful and if you would be willing to share any insights. I'm tweaking an Asset Maintenance template and it looks like I'll have to have a form for each hydrant and subforms for the maintenance.

Any tips would be appreciated.

[redbull]

Even without the GIS system, it is still possible. The things he is using for the GIS system is a unique id and an active/inactive flag. If you can reproduce this table, the rest should be similar to the existing users post.

[rpsmsmith]

Thanks. I have my table of Hydrants and set up tables for maintenance & testing. The Form Wizard has done a great job of creating my forms & subforms for entering the inspections & testing.

So far so good.

[rpsmsmith]

Now I'm wondering if I'll be able to import info from the Excel spreadsheet that contains photos of the hydrants..........I have a worksheet for each of almost 200 hydrants in the one Excel file....you can bet I'm going to try!

[redbull]

You should be able to, however embedding all 200 photos is going to make your DB HUGE!!. However, storing them locally on the machine and linking to them might keep things streamline. Depends on your use requirements.

[ssanfu]

I'm tweaking an Asset Maintenance template and it looks like I'll have to have a form for each hydrant and subforms for the maintenance.

This concerns me. You shouldn't have to a form for EACH hydrant - one form the enter the hydrant data and one (sub) form to enter inspection data.

Why do you think you need one form per hydrant?

If you would like post your dB, we can take a look at it and offer suggestions.

[rpsmsmith]

Thank you for taking your time to post . This forum is great (so are the members, too).

I went back to the original post from "rpeare" and read over it a few times. Then a lightbulb went off!

I abandoned the template and started from scratch. I have a table for my hydrants (imported from an Excel list), one for maintenance items, and one for testing items. I then used the Form Wizard and created ONE form with a subform for maintenance and a subform for testing....all on one form!

Teaching yourself Access can be quite interesting and challenging. I'm giving away my age, but I learned on db3. Access 2010 is MUCH better! Have a GREAT day!

[ssanfu]

I'm giving away my age, but I learned on db3

Same here.... Actually started with "File Cabinet" on Apple ][ ...... (flat file db but not programmable)

[redbull]

Awesome news.