Results 1 to 9 of 9
  1. #1
    newmexicat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    6

    Exclamation Inherited Access Database - DB Cleanup Question & Help Request re: "Collect Data" e-mails in Access

    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!!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you ever spent time putting together a Relational Data Base Management System (RDBMS)? Have you looked at any of the Contact Templates available for MS Access? There are some pretty good ones.

  3. #3
    newmexicat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    6
    I've looked at some of the contact templates for MS Access and think that there might be something there. I realize that would mean starting a new database to make sure things are set up right.

    I am exceptionally curious, though, to whether or not I would be able to send an e-mail to just the primary contact associated with a given agency to have them validate/add/or updated all 3 contacts assigned to a given agency. If there is a way to do it; then it's worth the work to create the database anew. If not, then I'm going to have to manually enter everything and copy and paste the info in the DB to create e-mails and re-enter data when people reply.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All of the templates just create a usable/modifiable database. You can then tailor it to your specific needs. I'm sure what you have expressed so far is doable and if you get stuck, these forums can bail you out. I should warn you that most of the helpers prefer VBA code to macros.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just curious...

    Is the dB in mdb format or accdb?
    Is the dB split?
    How many users are in the dB at the same time?
    Does the dB (mostly) meet your needs? Might be able to take what you have and normalize the current structure so you don't have to start from scratch.


    If you want to provide the dB to be analyzed, change the sensitive data (change PH# to (999) 555-1212), use names like Donald Duck, Mighty Mouse, Sam Spade, etc. Change dept names to Dept1, State Police2, etc.

    Don't need 800 entries, maybe 5 - 10 to see how the dB works....
    Do a "compact and Repair", then Zip it......


    Good luck with your project............

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Some of the more complex databases I have built are contacts DB's. Each time I think I have something that will fit most any environment, a new Business Rule presents itself and a revision to the ERD is necessary.

    After revisiting so many iterations and types of contact DB's, I always try to start with the Contact entities, first, when automating a business process.

    In other words, 'contacts' sounds easy, but it aint. And, contacts are super duper important to most any Business Process.

  7. #7
    newmexicat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    6
    The database is in accdb format.
    I'm typically the only user in it - I doubt we'd ever actually need simultaneous access.

    There are literally NO relationships in this database. Really, it's just a spreadsheet.

    I guess, thinking of it that way, there's no reason why I can't start building off that info instead of re-entering it. I think part of the problem is that I'm afraid to touch the old stuff (a ton of old, unused tables/queries/reports). But, no reason I can't try to work with that first.

    I was working on a whole new database set up to enter the information into... I guess I could just try to replicate those table relationships in the exiting one and see what happens.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was working on a whole new database set up
    You could continue with the new database, using the old dB as a reference. I would be using a copy of the current dB, not the production (current working) dB.

    Post pictures of the relationship window if you want comments/reviews. The most important thing (IMHO) is to get the table structure/relationships correct first before creating queries and forms/reports.


  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    newmexicat,

    You might get some useful tips on database generally by watching these free videos by Dr.Daniel Soper
    Intro to Database
    The Relational Model
    Data Modelling and the ER Model


    Good luck with your project.
    Last edited by orange; 10-08-2016 at 06:35 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-03-2014, 10:27 AM
  2. Replies: 12
    Last Post: 05-23-2013, 10:56 AM
  3. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  4. Replies: 2
    Last Post: 03-29-2012, 08:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums