Beginner Seeking Help: Building an Access Database for Volunteer Fire Department

[HerJoca]

Hello everyone,

I’m working on building a Microsoft Access database for a volunteer fire department, and as a beginner, I’m learning as I go with the help of ChatGPT. However, I’d love to get advice, guidance, and suggestions from experienced Access users to ensure I’m building this database effectively and efficiently.

Goals of the Database:​

1. Member Management:
   
   • Store detailed information about members, including personal details, training, medical records, and roles within the organization.
   • Provide a way to easily search for and retrieve member data.
2. Data Export:
   
   • Allow users to export selected member data to Excel or PDF.
   • Enable flexible selection of which fields to include in the export.
3. Additional Features (Future Development):
   
   • Equipment Management: Track inventory and assign items (e.g., helmets, uniforms) to specific members.
   • Financial Records: Manage membership fees or donations.
   • Meeting and Activity Tracking: Record attendance at meetings, training sessions, and interventions.
   • Customizable Reports: Generate detailed reports based on filters and selected data.
4. Ease of Use:
   
   • The database will be used in Access Runtime, so it needs to be intuitive and secure, with minimal chance for users to make accidental changes.

Progress So Far:​

1. Table for Members:
   
   • I’ve created a table that stores key member information, including name, contact details, medical data, training, and roles within the organization.
2. Basic Forms:
   
   • A data entry form for adding and updating member information.
   • A form that allows users to select fields for export.
3. Preliminary Export Functionality:
   
   • Currently, I’m working on exporting selected fields to Excel or PDF. I’m trying to do this with minimal or no VBA code.

Challenges and Questions:​

1. Scalability: How can I structure the database so it’s efficient and easy to expand when new features are added (e.g., equipment management, financial tracking)?
2. Export Functionality: I want users to select fields to export (e.g., Name, Birth Date) through a form. Is there a way to achieve this without heavy reliance on VBA?
3. Best Practices for Access Runtime: What should I consider to make the database foolproof and secure for users who will work only in Runtime?

Why I’m Asking Here:While ChatGPT has been helpful for learning and troubleshooting, I believe the collective experience of this community can offer insights and best practices that will take my project to the next level. Every bit of advice, from structuring tables to optimizing forms, will be greatly appreciated!
Thank you in advance for your help! I’m eager to hear your suggestions and learn from your expertise.
Best regards,
A motivated beginner on a mission

 

[theDBguy]

Hi. Welcome to AWF!

A good database needs a good data structure as a foundation. To have a good table structure, you'll have to follow the Normalization rules. If you can post a screenshot of your Relationship Window, we can comment on your table structure.

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=90561

 

[Pat Hartman]

As you start each new section of the app, post your proposed schema and we'll help.
1. Scalability is dependent on your foundation. If your schema is sound, you should be able to add new functionality and the tables to support it and connect them to existing tables.
2. I tend to not go crazy with stuff like this. I create one export with demographic data and others for "transactional" data. It is very difficult to create custom exports where users can pick data from multiple tables so I almost never do it. Once I get to creating custom reports, the exports are rarely used.
3. You can never depend on the user having only the runtime installed so you have to lock up the DB sufficiently to prevent accidental changes and then disribute as an .accde renamed to .accdr.

 

[The_Doc_Man]

@HerJoca - you are new here so don't know our ways. Therefore, you can be forgiven for your action. Gasman called you out for an action called cross-posting, which means that you posted the same question on more than one site.

We can't stop you, but you should know that it is considered rude to cross-post without notifying us that you did so. The reason that blind cross-posting is rude is that we are all volunteers here and don't get a penny for what we do. But if you give US a problem and someone solves it for you on another site, we would waste our time to solve it for you here on this site as well. By noting that you have cross-posted, we can first look up the other site to see if you have an answer and, if you do, we can abandon the question without wasting so much time.

Since you are new, all we will do is inform you that we frown on unannounced cross-posting. However, we DO understand why you do it. You are hoping for an answer. Just remember that a LOT of people come here and to other sites ALSO hoping for an answer. Help us better manage our donated time by helping us to know when you have cross-posted. Note also that if you post elsewhere and get no answers, it is OK to cross-post here as long as you explain (a) that you posted elsewhere first and (b) got no answers.

 

[Gasman]

TBF I only wanted to advise members, so they did not waste their time. Same on other site.

 

[MajP]

I would look at some of the available template databases to see how they organize some of the information. What you descibe might be bits and pieces form available templates. I would be cautious. Some of these do things most people on the forum will not do such as use table lookups and rely heavily on macros and not VBA. But you can get ideas. You can import and export into other databases forms, reports, tables, modules, queries, etc. So you can import some of this into yours.

Seriously do not worry about the functionality. All of that is doable. Most contributors on this site have plenty of examples, forms, and code to do those things and will be willing to help or share.
Export capabilities, Tailorable Reports, etc.,

You have to get the data structure correct. Let us help you do that. If the frame is correct adding bells and whistles is easy. If not its hard to impossible.

Immediately reading this I think there could be some problems. (But without knowing what you really track it is hard to determine if it is fine as is.)

I’ve created a table that stores key member information, including name, contact details, medical data, training, and roles within the organization

A single table to do that sounds like it could be "non normal".
Often I could expect multiple related tables.
TblMembers holding member information (Name, ID, ...)
TblContacts (related table holding 1 to N contacts for a person. I.e Multiple phones, multiple emails)
TblTraining (related table Holds multiple training records. Course Name, Date Taken, Certification .... )
TblRoles (related table if someone hold multiple roles. Primary Driver, Floor Supervisor)

This is why you should build your tables and then show us how the data relates. People here will help suggest ideas. We can then discuss with you if it is correct, tables to add, delete, modify, and relations to establish.

Example of that type of discussion and the back and forth is here:

Access product catalogue website style

Something's wrong. I can see the name of the file, but it does not let me download it and that error message shows up. I suggest you use another service, like Google Drive or even Dropbox.

www.access-programmers.co.uk

These discussion need to be collaborative because there are infinite designs depending on what the user needs to store and what the user needs to do with the data.

For the data export. I have a standard form that I use in in any database. All the functionality is there so no requirement to write any code. Just import the form.
The form will pull in all queries (or the ones you designate) into the combobox.
1.Then you can pick any query from the pull down.
2a. Choose what fields to include.
2b. Sort the fields using the up and down buttons.
3. You can filter the query even more using the down arrows on the columns (like any datasheet)
4. Export to Excel or PDF.

Point is people have this kind of functionality that you can get from this site.

 

[Mark_]

Specific to training and medical requirements, you will want to look into what a "Many to Many" relationship is.
You will have ONE table that lists requirements. You will have your table with volunteers.

You then will have a third table that links a requirement to a volunteer. This normally will also include the date the requirement was met, the value (if needed) for a medical test, and the date the requirement expires. This then drives reports that are based on future dates so you know who will have their requirements expire, prior to them expiring, so you can bug your volunteers for renewal or recertification documents.

If you are going to be saving copies of documents, I HIGHLY recommend staying to either PDF or PNG format. I also recommend saving the documents on disk with the path to the documents saved in your database instead of the PDF or image itself. This will reduce database bloat, speed your record access, and avoid a lot of issues of something changes. This path can be kept in your linking table so you have easy access to historical records for reporting.

 

[Gasman]

O/P has already been offered substantial code in the crosspost.

 

[arnelgp]

O/P has already been offered substantial code in the crosspost.

it is more than enough for a beginner, lots of stuff and fully loaded.