This is a classic problem coming from spreadsheets to databases. Think of your spreadsheet columns as fields, the records as rows. Spreadsheets are very flexible when it comes to adding more fields -- just type in the next column and you're away. Databases prefer to make you rethink your data structure. Fewer fields (because they need to be specifically created) holding more records -- you don't have a 65536 row limit like Excel.
In the example you gave, to average the pain readings, you need to build expressions that take the average across one row, in 64 different fields. Need another field? Wnat to fit them all on a report? Good luck. Instead, in Access you separate the different bits of information so that, for each patient visit, you have a date, the name of the patient, nurse and/or clinician, the pain score, and fields for however many other different pieces of information you need to gather (blood pressure, weight, ...) The advantage: fewer fields, with NO LIMIT on the number of readings you can make for each patient.
An example:
The Patient table needs a unique ID for each patient (use Autonumber) as well as First Name, Last Name, Gender, Date of Birth, the various address and contact details.
The Clinician table needs a unique ID for each doctor (Autonumber), as well as the doctor's name, address and contact details.
The Visits table has a Visits ID (Autonumber), a Patient ID (Number), a Clinician ID (Number), Visit Date, and fields for all of the relevant details of the visit.
To build the relationships between the tables, go to the Relationships view (on the main toolbar). Put all 3 tables on the surface, then click and drage to connect Patient ID on Patients to Patient ID in Visits. In the dialog, click that you want to enforce referential integrity. Do the same to connect Clinician ID in the Clinicians table to Clinician ID in Visits. Enforce referential integrity.
Close and save.
Now, for data entry, you need a form. Click the Forms tab, and New. Select the Forms wizard. Choose Patients as the table to use as the data source. This allows you to browse records by patient. Select all of the fields from Patients, and all of the fields from Visits. As you go through the screens you'll be prompted about how the data will be sorted, grouped and displayed. Don't worry about sorting or grouping. Make sure that you select to view records by Patient, with Visits as a subform.
Choose a name for the form, click OK and sit back. You'll end up with a (fairly ugly, but usable) form showing your patient details. Below, there will be a blank subform where you can put the visit information. Each new visit, add a line to the subform.
It seems a pain at first, but reporting and data maintenance become very simple if you do it right. There isn't tiem or space here to do the topic justive, but do a web search on data normalization. There are plenty of useful tutorials out there. Also, if you will be designing databases, DO SOME COURSES. At least Intro and Intermediate. Advanced too, if you want to build anything useful. And get a good book. Helen Feddema wrote Access 2002 Inside Out, which is pretty thorough. Available from this site is Grover Park George on Access -- also a goodie.
Denis
