UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Database Design Concept, Access 2016    
 
   
tvining
post Jan 2 2019, 02:19 PM
Post#1



Posts: 58
Joined: 2-March 04
From: Texas


Here is my application:
I work in emergency services, and have a number (about 80) checklists for different incidents (ie Fire Response, Train Accident, Motor Vehicle Accident).
Each checklist has different steps (Call these people, file this report, etc)
All checklists are completed within an hour or so.
When a checklist is opened, I'd like for it to create a record that, in case it is closed, would allow us to pull that checklist back up and continue it. (user error)
Some checklist items are the same (Confirm correct checklist, Consider preparation of Reports, Confirm checklist complete and print, consider other QRCs as applicable) and some are unique to the particular checklist (ie notify railroad for train accident, notify ambulance for injury, etc)
Some steps require only a mark to indicate it's completion (choice of 5 options) and some steps require inputting of text (details).

I have a few ideas floating in my mind for design:
1. Table of Checklist steps, table of QRC's, with links to each item for each step, final table with marks of completion/initials/details.
2. Table with checklist items listed as flat file, entry line for each checklist (Line for 101, line for 201, line for 301), table for relating completion marks

Other ideas?
Based on the application, what is the best way to proceed?

I can do the work, but need the kick in the pants to get me going to right direction.

TV

--------------------
TV
Go to the top of the page
 
theDBguy
post Jan 2 2019, 02:29 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,559
Joined: 19-June 07
From: SunnySandyEggo


Hi TV,

I have done a checklist type database for an insurance company once and if I remember correctly, I created a table to list each checklist type and then made a child table to indicate the steps for each checklist. In my case, there were only few similar steps, so I just duplicated them using a different checklist ID. In your case, if there are a lot of duplicate steps, you might consider adding another table to show which checklist a particular step is applicable. When the user selects a checklist, I generate the steps by going through the child table and append the records to the actual inspection table where the auditor/inspector can mark the scores.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Jeff B.
post Jan 2 2019, 02:44 PM
Post#3


UtterAccess VIP
Posts: 10,254
Joined: 30-April 10
From: Pacific NorthWet


Sound like you've already given it some good thought. When I have a db to design, I usually start out with paper and pencil, and jot down the things about which I'll want to have/keep/retrieve data (i.e., the "entities"). When I have those, I start drawing lines between them to show the "relationships" (1-to-1 -- each item in entity #1 has one and only one corresponding/related item in entity #2; 1-to-many -- each item in entity #1 can 'belong' to multiple items in entity #2; many-to-many -- each item in entity #1 can belong to multiple items in entity #2 ... and vice versa).

That last one can get a little tricky, but the way to handle it in Access is to create an entity between the 'manys', that hold any/all valid combinations. That sounds a little like your checklists, where one checklist would have multiple "checklist items", and one "checklist item" could 'belong' to multiple checklists.

My next step is to start jotting down, under each entity, the data that belongs to that entity. For example, if you have a Person/People entity, people have names, dates of birth, ...

At this point, I generally turn on my computer and start building Access tables. Each table is an entity, each entity has data elements (i.e., "fields"), and tables are related to each other.

I also like to spend the time thinking about what folks ask about (i.e, want to know), and about what folks may, this afternoon, next week, next month, ..., want to ask about. If I design something more than what folks need right now, but with the ability to easily grow in the future, I just made my (or someone else's) work easier.

An example of this is what it appears you've already decided ... you could use a list (i.e., table) of "checklist items" as a way to easily add new items in the future. And if your situation might call for the elimination of a checklist item, rather than removing it (and leaving a checklist with an "orphan"), consider adding UseFrom and UseTo date-type fields.

Similarly, if you have a checklist today that might no longer be needed, consider adding From/To dates in the "Checklist x CheckList Item" table (one of those many-to-many mentioned above). What the heck, how 'bout the same in your Checklist table?

Good luck!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
tvining
post Jan 10 2019, 08:02 AM
Post#4



Posts: 58
Joined: 2-March 04
From: Texas


Thank you for the input! It is very helpful. I like the idea of the child to consider what step matches to what list.

TV

--------------------
TV
Go to the top of the page
 
tvining
post Jan 10 2019, 08:03 AM
Post#5



Posts: 58
Joined: 2-March 04
From: Texas


Thank you!
In the end, the output is just ensuring that the steps get completed, so there will not likely be a 'report' to print, but yes, I need to design the looks of the form to ensure that it is what the controllers actually are used to seeing.

Great input!
Thanks.

--------------------
TV
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 06:21 PM