Access 2003: Multiple Data Items

  • Thread starter Thread starter Trevor Aiston
  • Start date Start date
T

Trevor Aiston

I have been asked to help design a clinical audit database which will record
data from a paper data collection sheet.

However, quite a few of the feilds in the database will have to contain more
than one peice of data
E.g the paper form asks the user to tick all the services to be included in
the audit
Mental Health
Pysiotherapy
Psychology, etc
and the user can tick more than one service. What is the best way to record
this data in an access.

Trevor
 
I would use a column for each type in your table, and make it a yes/no field.
Then on your forms each different selection would have a checkbox. This
sounds most appropriate for the "tick" you were talking about.
 
Thanks Ryan, Hmm I can see that working if a little messy. Is there no way
in 2003 to have multivalue feilds as you can in 2007?

Thanks
Trevor
 
You could use a listbox, or have unbound checkboxes for each value and have
each on click event of the checkbox concantinate into a textbox. I would
still suggest the checkboxes howevever, because future queries to count how
many of a single value will be easier. Just my suggestion though.
 
Whoops, that got sent by accident.

What you need to do is what Access 2007 is doing in the background (totally
hidden from everyone) and that is to have additional tables for storing the
information.

Probably, an available services table which would be a listing of all the
possible services
and a Services used table which would link to the available services table and
to your primary table (Audits?).

The Services used table would have two fields (or more if needed)
Field: Service (would store the value of a primary key in the available
services table)
Field: ServiceUser (would store the value of a primary key in your primary table)

The Audit? table would be joined to ServicesUsed table and the Services table
would be joined to the ServicesUsed table.

You would need a form with a subform for data entry that would allow you to
add one record tothe ServicesUsed table for each service used by each Audit?
table record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks to all who have replied, trying to pursuade our IT dept (NHS) to
upgrade us to 2007 which would also answer other queries.

But many thanks for all of your efforts
 
Trevor,

I have to agree with most everyone that has recommended the Check Box. I
have had simular occurances and have found that check boxes are the answer
and it is easily accessable for your needs.

Having multiple tables means having to maintain each one, especially if
there are changes. here you only need to update one.

and the most impotant reason (no offense to an IT People, but its true)
trying to get your IT department to upgrade, could delay the project further.

These are my opinions, based on my experiences.
 
That's not an uncommon solution adopted by inexperienced users, but I'm
afraid that it is nevertheless a seriously flawed design. It's committing
the fundamental design fault of 'encoding data as column headings'. This
contravenes Codd's Rule 1 (The Information Rule) , which is expressed in a
slightly expanded form as Date's Information principle, viz:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

John Spencer has, as would be expected from a developer of his standing and
experience, provided a rock-solid solution which should be adopted.

Ken Sheridan
Stafford, England
 
Ken and Bob,

I appreciate the lashing and I truly mean when I say it is humbling. As I
said before these are only my opinions and based on my experiences using
Access in the 10+ years I have taught myself rudimentary ways of avoiding IT
(and if there are no answers to be found). Yet I still getting the project
completed.

Again, gentlemen thank you for contributing to the site and as always I look
to members like you for answers.
 
Thank you for your courteous response, which does you great credit. I hope
my comments didn't come over too much as a lashing. If so it was
unintentional, and I apologise. I have to plead guilty to preaching with the
zealotry of the converted sinner. I can recall many years ago designing a
dBASE table for a colleague using a bunch of Boolean columns in exactly the
way I am now saying is verboten! But that was before I'd read any of Codd or
Date's words of wisdom.

Ken Sheridan
Stafford, England
 
Back
Top