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
> Normalizea State Form, Access 2016    
 
   
brastedhouse
post Sep 1 2019, 01:17 PM
Post#1



Posts: 110
Joined: 16-March 15
From: Chautauqua, NY


I just read Roger's Access Blog from Wednesday, October 18, 2017 "Really Bad Design Decisions: A Case Study" about a hospital form to database transfer gone terribly wrong. And lo and behold I have just been given one to "automate". I am at a loss as to how to proceed. I have 30 data points I have to collect and report to the state of NY about Narcan administration. Every time we use it we have to send this report to the state.

So, I have a table that records the basic information about EMS calls (tblCall). And I need to create a table or tables to record the Narcan administration and it needs to be linked to the ems calls table. I could easily just create a big table of 30 fields and have at it but I doubt that is a good idea. If I made one table I would have:
narcanID autonumber
callTableFK number (link back to ems call table
fields 1 - 30.

A lot of those 30 fields have required answers. So I have to create tables to use for combo boxes or use values in the combo boxes depending on how many response there are or if I already have a table that I use for combo boxes of the same info. jeesh.

So here are the fields if I had one big table:
narcacnAdminID AUTONUMBER
callTableFK NUMBER
narcanResponderArrive DATE
narcanPtGender SHORT TEXT VALUE LIST
narcanPtAge NUMBER TEXT BOX
narcanCallZipCode SHORT TEXT TEXT BOX
narcanCallCounty SHORT TEXT VALUE LIST
narcanPtResponsivenes SHORT TEXT VALUE LIST
narcanPtBreathing SHORT TEXT VALUE LIST
narcanPtPulse SHORT TEXT VALUE LIST
narcanDrugs NUMBER Select SQL
narcanVialsUsed SHORT TEXT VALUE LIST
narcanTimeToReaction NUMBER Select SQL
narcanPtResponse NUMBER Select SQL
narcan2ndDoseRoute SHORT TEXT VALUE LIST
narcanTimeBetweenDoses NUMBER Select SQL
narcan2ndTimeToReaction NUMBER Select SQL
narcan2ndDoseResponse NUMBER Select SQL
narcanPostNarcanSymptomsOther SHORT TEXT VALUE LIST
narcanOtherInterventionsDone SHORT TEXT VALUE LIST
narcanAdministeredByOtherSHORT TEXT VALUE LIST
narcanDisposition SHORT TEXT VALUE LIST
narcanDidPtLive SHORT TEXT VALUE LIST
narcanHospital NUMBER Select SQL
narcanTransportingAmbulance NUMBER Select SQL
narcanComments Long Text
narcanAgency NUMBER Select SQL
narcanAgencyType SHORT TEXT VALUE LIST
narcanAgencyTypePolicBadge SHORT TEXT TEXT BOX
narcanAgencyTypeFirstName SHORT TEXT TEXT BOX
narcanAgencyTypeLastName SHORT TEXT TEXT BOX

So, should I break this up? Is there a more logical way than others? If I don't break it up, is there a way to have a series of subtables in tabs on a form to ease the eye strain?

Best, scott
Go to the top of the page
 
orange999
post Sep 1 2019, 01:33 PM
Post#2



Posts: 1,973
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Yes, normalize.
You might try working through a few good examples also on Roger's site
See the tutorials ER Diagram
Class info
Widgets

They all start with a clear description of the business --always a great place to start.

Here's info right here on UA
and
Part 2
This post has been edited by orange999: Sep 1 2019, 01:41 PM

--------------------
Good luck with your project!
Go to the top of the page
 
tina t
post Sep 2 2019, 12:50 AM
Post#3



Posts: 6,123
Joined: 11-November 10
From: SoCal, USA


i agree with orange - definitely normalize. and do read the info at the links he posted, every bit will help.

QUOTE
So I have to create tables to use for combo boxes or use values in the combo boxes depending on how many response there are or if I already have a table that I use for combo boxes of the same info. jeesh.

and i agree with you. :) but keep this in mind: the only thing worse than normalizing, and using "supporting tables" to provide combobox RowSources - is not doing it. go through the pain now, once, or go through worse pain over and over during the building and later the maintaining of a non-normalized database. always the developer's choice.

also, specifically re using value lists vs tables as combobox (and listbox) RowSource, i'd recommend that you always use a table as RowSource. the only exception might be if there are only two possible choices - and the only ones i can think of are True/False (sames as Yes/No, or On/Off) and gender Male/Female. in the True/False case, that might be increased by one - N/A. in all other cases, you limit yourself dangerously when you use a value list, because things change, business rules change, and what may seem to be only two or three possible answers can also change. with a table, all you need do is add another record. with a value list, you need to change the design of one or more forms, and possibly one or more reports. i'll go with a data change over a structure change any day of the year. and i'm willing to bet that no developer has ever said "gee, i wish i'd used a value list instead of a table for this RowSource".

other than that, i just have a few comments/questions re the fields you posted. not for you to answer to me, but things to ask yourself as you consider how to design the tables and relationships.

QUOTE
So here are the fields if I had one big table:
narcacnAdminID AUTONUMBER
callTableFK NUMBER
narcanResponderArrive DATE is this the same as the date of the call? and is that date in the call table? if so, is it necessary to duplicate it here?
narcanPtGender SHORT TEXT VALUE LIST
narcanPtAge NUMBER TEXT BOX
narcanCallZipCode SHORT TEXT TEXT BOX
narcanCallCounty SHORT TEXT VALUE LIST counties do change, as do responding areas. better as a table that can be updated?
narcanPtResponsivenes SHORT TEXT VALUE LIST
narcanPtBreathing SHORT TEXT VALUE LIST
narcanPtPulse SHORT TEXT VALUE LIST the options, and the two fields directly above, are subject to change. i'd opt for tables, to be safe.
narcanDrugs NUMBER Select SQL by "Select SQL" i assume you mean the choices are from a "supporting" table. the big question here is: might more than one answer be appropriate- two or three or ten drugs, rather than just one? if so (and i'm guessing the meaning of this field, anyway), i'd recommend moving this data into a separate child table, rather than using a multi-value field.)
narcanVialsUsed SHORT TEXT VALUE LIST
narcanTimeToReaction NUMBER Select SQL
narcanPtResponse NUMBER Select SQL
narcan2ndDoseRoute SHORT TEXT VALUE LIST "2nd" dose is a warning flag - multiples of the same thing almost always should be in a separate child table. so one table describing dosing, with as many records as needed to describe each instance of dosing. time between doses may take a bit more thought.
narcanTimeBetweenDoses NUMBER Select SQL
narcan2ndTimeToReaction NUMBER Select SQL
narcan2ndDoseResponse NUMBER Select SQL
narcanPostNarcanSymptomsOther SHORT TEXT VALUE LIST again, and ditto the next two or three fields: can there be multiple answers? see above re child tables.
narcanOtherInterventionsDone SHORT TEXT VALUE LIST
narcanAdministeredByOtherSHORT TEXT VALUE LIST
narcanDisposition SHORT TEXT VALUE LIST
narcanDidPtLive SHORT TEXT VALUE LIST
narcanHospital NUMBER Select SQL
narcanTransportingAmbulance NUMBER Select SQL
narcanComments Long Text
narcanAgency NUMBER Select SQL these five fields describe an agency, not an instance of narcan administration. definitely a separate table (with agency type list as a separate supporting table to the agency table), with an "agency" foreign key in this table. since again you cite "Select SQL", if that means you already have a tblAgencies, then the following fields should already exist in that table. this field would be your foreign key to tblAgencies.
narcanAgencyType SHORT TEXT VALUE LIST
narcanAgencyTypePolicBadge SHORT TEXT TEXT BOX
narcanAgencyTypeFirstName SHORT TEXT TEXT BOX
narcanAgencyTypeLastName SHORT TEXT TEXT BOX

my only other suggestion is to not worry about having multiple tables instead of a single table. remember that in Access data storage and data display are completely separate. you can store the data in a normalized structure, and yet display it in a report as a long list like your field list above, if that's what the state agency wants. there are also many options for data entry form setup that can make it easier for your end user. and if it takes extra work to set up, remember you only have to do it once! ;)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
brastedhouse
post Sep 2 2019, 09:45 PM
Post#4



Posts: 110
Joined: 16-March 15
From: Chautauqua, NY


Many thanks for the all the input. I am beginning my reading tonight. I appreciate the all the time everyone out into this. It has been a real eye opener. Best, Scott
Go to the top of the page
 
tina t
post Sep 2 2019, 11:56 PM
Post#5



Posts: 6,123
Joined: 11-November 10
From: SoCal, USA


you're welcome, orange and i are glad to help. good luck with your project! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 06:52 AM