?
Solved

Access 2007 Methods:  Structure of tables/forms for entering data

Posted on 2011-02-18
1
Medium Priority
?
273 Views
Last Modified: 2012-05-11
Good morning, I am going to be building a new database and I would like to get some feedback from more knowledable Access experts before I begin.  The database will likely consist of many forms/tables and will serve as a template for projects where students or other users will abstract information from academic literature (to create a database of information for writing up reports).  The goal will be to capture all relevant information contained in the article, but because this is a template it can not be specifically tailored to a single project.
Some of the forms will gather routine information that is conserved across projects (for example Authors, Title, Journal, number of participants, number lost to follow up) but because of the varying study designs one article might have 3 study groups whereas another could have 1 (ie. an observational study with 3 group vs. a randomized trial with a control and two experimental groups) or include multiple diseases etc...  My first question is this, how do you suggest implementing form/table structure to deal with this disparate 'length' of data in each study?  Do I make a single table for, for example study characteristics and have Group1, Group2, Group3 .... Group7 fields and so on for each of these items that could contain multiple entries?  And how should this be presented on the form so that data entry is elegant and not an exercise in navigating through many fields which could be irrelevant to a specific study?  One method I thought of was to hide many of the form controls and have them appear on some selection (i.e. a radio button or combo box for number of groups) but this might still be a bit messy.  Does anyone have any ideas about how to make the form controls dynamic to what the user needs to transcribe?  Another issue to keep in mind will be that the data for these entires will be cross checked with another student/user's entry so I will likely be adding multiple list boxes for users to make selections where 'open ended' text entry isn't necessary (for example in selecting study type or disease which are fairly consistent).  
Anyone's help will be greatly appreciated and I will gladly answer any additional questions you might have.  
Thanks in advance for any help,
Bevo
0
Comment
Question by:Bevos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 34931170
Do I make a single table for, for example study characteristics and have Group1, Group2, Group3 .... Group7 fields and so on for each of these items that could contain multiple entries?

Yes you want  a single table.   Each study group will be a separate record not separate fields. You will use a single record to define one group. If the attributes that design a group vary a lot the I use a child table (1-to-many)  where each record defines just one attribute.

One method I thought of was to hide many of the form controls and have them appear on some selection (i.e. a radio button or combo box for number of groups) but this might still be a bit messy.
You instincts are correct that it would be messy. It should  be avoid.   I would use a table where you have a record for each of the varying attributes.

Example table to hold attributes:

Generic Fields:
-Parent_Table_RecID -foreign  key field that links to the parent table's primary key
-Attribute_Type_RecID - a foreign key to the lookup table of the attribute names
-Attribute_Data -  This is the actual data for this attribute type


0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question