So, I inherited an Access Database that's been handed down by secretaries before me. I genuinely don't know if I can salvage this thing or if I need to start over and enter all 800 or so entries new in a better formatted database.
I know the database was established by people only familiar with working with spreadsheets. There are no relationships set up and everything runs from pretty much one table (originally, someone did have some codes established pulling from separate tables; but, that's been used less and less over the years as people started manually entering codes for each entry).
The database itself is supposed to be a contact database that keeps record of ALL the contacts for an Office of Emergency Management. The database is meant to maintain several types of contact information; all of which need to be reported on separately: Contacts who need to be called into the Emergency Operations Center if we are activated (Emergency Support Function contacts) - and those contacts for each agency have to have "3-deep" redundancy, so we have a primary, secondary, and tertiary contact for each agency; Elected/Appointed Officials (again, contacts are 3-deep - meaning there's a line of succession order from 1 - 3, with the first being the primary and secondary and tertiary contacts as backup); Key Partners (groups we may need to call on for help); Resource Contacts (agencies/businesses that could offer supplies/equipment during a disaster); HAZMAT/Tier II facilities (facilities/companies that store hazardous materials on site and we may need to contact to notify of inspections and/or if we hear of a fire/emergency near their site); Critical Infrastructure (facilities that we may need to warn if there's a potential for electrical blackout or a train derailment, etc)... The main idea is that a lot of different information is meant to be kept and maintained in this DB.
When I first started here, I realized that the previous secretary's method of giving reports to Emergency Managers wasn't sufficient. The past few secretaries were entering information in the database as if it were a flat spreadsheet, exporting the info to excel, filtering, and printing. Clearly, this isn't good enough. But, because I'm having a hard time figuring out how best to "fix" all this and I was under the gun time-wise, the best thing I could figure out was to create reports based on what was needed. (Example: I used a query to figure out "Is this individual contact an ESF Contact? If yes, include in report - then sort based on agency and order based on line of succession number, listing 1st contact first down to the 3rd contact.") It was a slap-dash way of getting the info my supervisor needed; but it worked... kinda.
One of the main reasons I want to find a better way to organize the data goes beyond my reporting needs. I need to be able to maintain this information better. My goal, in a perfect world, would be to be able to Collect Data via e-mail using Access. Ideally, I would be able to e-mail only then contacts identified as a primary contact for a given agency and ask them to verify or edit the contact/resource information we have in our database for their agency (meaning at least 3 individual contacts, and those contacts' corresponding contact information, for a given agency) via an e-mail sent through Access and then receive replies to update the database. Additionally, I'd like to be able to send these requests for update based on the type of contact an agency is; so I'd need to make sure that the e-mail Data Collection could be based sets of agencies (to be determined by the agency contact type). Theoretically, some agencies may be more than one type of contact and I may need to update information for them for multiple purposes. (Example: State Police would be a law enforcement contact that has an Emergency Support Function in our Emergency Operations Center, so I would need to include them in an ESF Line of Succession Update; but, State Police are also a Resource Contact - as they help us when we need crowd control and may need to close roads due to hazardous weather/road conditions - so I need to update the resource information on file for the agency.) Right now, the database is only about 800 entries... but if we were appropriately getting information, I think that could swell and I'd like to make sure I have an efficient way of updating information annually and for getting new information when we hear of a new agency/business that needs to be added before I attempt to start a campaign to add more to the database.
I'm by no means a "power user" but I could really use some guidance as to whether or not what I want to do is even possible. My IT department has been no help whatsoever and I want to make our office more capable of managing contacts - as it is important in the emergency management field!
I can't share too many screenshots, as much of the information contained within the database is sensitive (elected official personal cell phone #s, etc); but I'd be happy to clarify anything to get some help!! I am so incredibly hopeful that someone can help me!!