Having trouble creating a medical testing tracking system

[nakamichie]

Hello,

I have been asked to help create a access data base (using 2007) on tracking patients medical tests that then alerts you when it is overdue set against a scale:

Physical Test - age 20-39 - every 3 years - Male and Female
Physical Test - age 40-49 - every 2 years - Male and Female
Physical Test - age 50+ - every 1 years - Male and Female
PAP Test - 20+ - every 3 years - Female

There is about 10 of these tests like this and then tests specifically for Cancer type patients.

I figured out one way by creating a table with each column being a test but this didn't really work as I need to keep a record of each time they come in and the test is done and then it spits out the next reoccurring test from that date.

I then will need to make a report created from this that alerts on everybody who is overdue to not gotten any of the tests by category.

I then thought okay I will create a table that has the fields as ready something that this is better and to append the records to another form:

Illness - Pulling from tables of Illness
Test - Pulling from table of Tests
AgeGrp - multi-field, referenced from Table of Patients
Gender - Multi-field, pulling from the Table of Patients
Frequency - pulling from table of frequencies with amount of days to calculate

I did this and now I can't figure out anything past this point and how to link all this data up so the right information pops out.

Or is there a better way of going about this.

I am very new at this so any help would be great as have been scratching my head all day over it.

Thanks!

 

[burrina]

Welcome to the forum. You will need to provide details of your current tables/relationships for us to assist further.

 

[plog]

This process is called data normalization (http://en.wikipedia.org/wiki/Data_normalization). Check out that link and also google for a few tutorials.

My advice to first timers is to open up Excel and put all the data you have or are going to have in there. Use only 1 tab and as many columns as you need. Create a few fake records and make sure you account for every case. Using that, you can then start the process of creating the correct table structure in Access.

 

[nakamichie]

Thanks for your answer. I will check this out. I also figured out 3/4th of what I was trying to do by following someon elses examples. But what you say makes sense.

Thanks.