multiple criteria

  • Thread starter Thread starter Shanin
  • Start date Start date
S

Shanin

We are wanting to make a database that will store Event Report information on
individuals. I need some advice on the best way to set this up. I've
already figured the individuals supported will have their own table with
their basic contact information. Event reports should have it's own table
also. My question is on that the event reports table, there can be several
incident types (about 20 and these forms come from the State so there is no
changing the number of them) that can be associated with one event and 1-20
can occur on the same event, example: Joe choked on some food, resulting in
him falling to the floor, and hitting his head. In that scenerio, three
things would need to be checked or recorded according to the State form,
choking, falling, and striking head. How is it best to incorporate these
since I know the checkboxes aren't the best. Do I need to make a separate
field for each one of these options or make those options be in a separate
table? I want to set this up in the best way so data can be queried easily
later on.

Thanks
 
Shanin

It sounds like you are trying to create one field for each possible event.
That sounds like a spreadsheet, not a relational database.

It all starts with the data. Before you move ahead to your reports,
describe the underlying data structure you are considering using to store
the data.

Reports are based (preferably on queries, which are based) on the data
stored in tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
As to the Best Way, there never is just one.

Yes, you need the Person data. If you're lucky you can link to that
information in your organization. In that case you'd use their table
design. Also, there may be a separate address table. If you're not
able to link to corporate data then you may be able to import the data
from their database.

No,, you don't need a Reports table unless you are going to forward
raw data. In that case, post back with more details about what you're
doing.

You need a table for Incident Types. It will list each of the State's
incident types with a brief description with each one.

The next table is tblEvent - the reason we're here. It will have:
EventID Autonumber (Primary Key)
PersonID Long Integer (Foreign Key) which is the
Primary Key of tblPerson - relates
this record to that Person
EventDate Date/time field for date alone
EventTime Date/time field for time along.
EventNotes Memo field - allows several thousand
characters to tell about the event.

The next table will be tblEventIncidents

EventIncidentsID Autonumber (Primary Key)
EventID Long Integer (Foreign Key) - relates this record
to the parent record in tblEvent
IncidentID or Incident number from tblIncidentType
EvenInciNotes text 255 amplifying notes about this
incident

That might get you started or at least started asking more questions.

Once your tables are designed and you want to start adding data you'll
need Forms. You'll find that the form/subform paradigm will be very
helpful.

My expectation is that you'd open a form based on tblEvent and then
pick the person, fill in the Date (defaults to Today() and time
defaults to the time portion of Now(). Then you'll decide whether to
enter all of the incidents that occurred or to go after the prose in
the Event Notes.

When it comes to creating Reports, your report will be based on a
query which will marshal most of the information for your report. In
addition, the Report designer has lots of features that help you to
really tweak your reports. You will probably want more than one.
Design a separate Report for each purpose rather than making a single,
very busy report.

HTH
 
Here is an idea of what I'm trying to do. The State has a paper form that is
printed out called an "event report". It has several sections on it that can
be filled out, but it all is based on one incident on one person. The
sections it has are Incident Type, Injury Type, Injury Severity, Injury
Description, Injured Body Parts, Medication Error, People Notified, etc.
That is the majority of what is on it. We are wanting a way to store the
data in a database so it can be easily viewed and trends seen later on
instead of having to pull numerous sheets of paper and searching for them.
Basically someone will take the written copy that comes from the field and
enter it into a form in a database.

So from what you're saying I think I have a better grasp on this now.
Basically for each section that has multiple fields that more than one can be
chosen, make a separate table which lists those to be used as a list and a
primary key basically calling it tblIncidentList or tblInjuryList. Then make
a separate table for those sections again where it will store each incident
or injury and link it by the eventID, so if there were 5 incidents listed on
the table tblIncident you'd have the same eventID listed five times with a
separate Incident ID in each one correct?

I can see that form for entering this may be fun. Is there a limit on
sub-forms you can put on a form?
 
I think you're getting it. In general, those little tables that
hadn't even been in your mind at the beginning will end up being the
ones with the most records. What tends to cause many people to fear
the concept is that those tables will hold records of the same type
from everyone. Those Foreign Key entries point each record back to
its "parent" record in the table above it.

Those tables that hold lists are just normal tables that are referred
to in use as "Lookup tables". They are very useful and are good
things. Don't confuse the term "lookup table" with the dreaded term
"Lookup Field". Lookup fields are evil and to be avoided. You'll
also find a write up about them on www.mvps.org/access

A form can have any number of subform controls. The limitation is on
nesting. I believe that nesting is limited to 3 levels. Once you get
into referencing controls on forms and subforms you'll see that
references to things in that third level can be cumbersome and
awkward. As you get into it at just the level of form/subform you'll
start to appreciate that. On www.mvps.org/access there is a good
presentation by, I believe, Keri Hardwicke.

My suggestion would be to consider using pages in your form and to
present each subform on a dedicated page of the form. A much more
complex to achieve presentation might be to use a Tab control to
present the subforms.

Have fun!

HTH
 
Back
Top