Table structure / planning - help please

[KellyGroom]

Hello

I wonder if anyone can help me?

I am currently creating my first Access database and in the planning stage but have come across one problem area. I need to create a database that holds and reports on information recorded on a clinical form. The section of the form I am stuck on contains a table as below.

1 2 3 4 5 6
Catheter Type
Wire Type
Balloon Type
Type of Stent
Size of Stent
Target Vessel
Successful? Y / N Y / N Y / N Y / N Y / N Y / N

The user works their way down this form starting in column one but free typing what make of Catheter they will use and then type of Wire and so on. The problem arises when a particular item doesn't work, e.g. a type of Balloon. They then try a different type of balloon and would enter this information in Column 2 and then if this works go back to column 1 to fill in the next field 'Type of Stent'. As below..

1 2 3 4 5 6
Catheter Type CathsRUS
Wire Type Wire 1000
Balloon Type BAL001 BAL002
Type of Stent ST555
Size of Stent 10
Target Vessel Left side
Successful? Y / N Y / N Y / N Y / N Y / N Y / N

Not only does the user need to record all this information without duplicating anything else from column 1 into column 2 but there are also instances that within the same procedure number, that a second bleed occurs and they need to fill in the next available column, which in the example above wiuld be column 3. They would again work their way down, but this time they may not need to use a wire as they can reuse the last one.

1 2 3 4 5 6
Catheter Type CathsRUS OpCa34
Wire Type Wire 1000
Balloon Type BAL001 BAL002 BA3
Type of Stent ST555 ST333
Size of Stent 10 5
Target Vessel Left side Right side
Successful? Y / N Y / N Y / N Y / N Y / N Y / N

The access database needs to be able to hold all this information and show that 2 separate episodes in affect have occurred. The user wants to be able to report on various things such as, what type of Stent/Wire/Balloon/Stent size is the most/least used / successful/unsuccessful? Etc

If anyone can help shed some light on the best way to structure this information in Access I would be very grateful!

 

[Dennisk]

you have a set of repeating groups which require normalising. i.e. create a seperate table to hold the Successful Y/N fields use the Primary key from the main table as the Foreign Key in the related table. Do this before you start to create your forms otherwise you will run into problems leter on especially with reports and queries.

When designing patient based database (I've done this for 30 years now) its alway

a Patient can have non, one or more referrals. Each referral may have none, one or more episodes. In your case the Incontinent data is the episode.

 

[Guus2005]

Something like this:

Every Catheter probably has more than one type. Put these in the Catheter table:
tblCatheter
-CatheterID:Autonumber
-Description:Text

Every Wire probably has more than one type. Put these in the Wire table:
tblWire
-WireID:Autonumber
-Description:Text

etc.

There must be a Patient table
tblPatient
-PatientID:Autonumber
-Name:Text
-SSN:Text

There must be a Case table (or something similar)
tblCase
-CaseID:Autonumber
-PatientID:Number
-StartDateate
-Enddateate
-Room:number

Now your Clinical form tables
CaseCatheter
-CaseID:Number
-CatheterID:Number
-Timestampate/Time

CaseWire
-CaseID:Number
-WireID:Number
-Timestampate/Time

etc.

This way you can support more than one Wire/Balloon/whatever for each case.

HTH. Guus

 

[KellyGroom]

A possible solution???

Thank you both for your help.

Unfortunatley, the User is not prepared to provide the list of types for each category (stents, balloons, wires etc) to allow me to set up individual tables for them to select from, as there are hundreds of each. A possible data input nightmare I know, but the decision is not mine to make.

I have been considering creating a wide table, as below...any thoughts or advice is much appreciated!

Field Names Data Type
Auto No. Number 1 2 3 4 5 etc..
Target Vessel & Position Text
No. of Caths Number
Cath Type 1 Text
Cath Type 2 Text
Cath Type 3 Text
Cath Type 4 Text
Cath Type 5 Text
Cath Type 6 Text
Cath Type Successful Type 1/Type2/Type 3...etc
No. of Wires Number
Wire Type 1 Text
Wire Type 2 Text
Wire Type 3 Text
Wire Type 4 Text
Wire Type 5 Text
Wire Type 6 Text
Wire Type Successful Type 1/Type2/Type 3...etc
No. of Balloons Number
Balloon Type 1 Text
Balloon Type 2 Text
Balloon Type 3 Text
Balloon Type 4 Text
Balloon Type 5 Text
Balloon Type 6 Text
Balloon Type Successful Type 1/Type2/Type 3...etc
No. of Stents Number
Stent Type 1 Text
Stent Type 2 Text
Stent Type 3 Text
Stent Type 4 Text
Stent Type 5 Text
Stent Type 6 Text
Stent Size 1 Text
Stent Size 2 Text
Stent Size 3 Text
Stent Size 4 Text
Stent Size 5 Text
Stent Size 6 Text
Stent Type Successful Type 1/Type2/Type 3...etc

Thanks

Kelly

 

[Guus2005]

The solution to your problem must be sought in the normalization of your database. It depends on a large number of variables why you should choose for this solution or that solution. Since i do not know all variables i have to guess.

You can create a table Category which the fields ID and Category. containing: Cath, Wire, Balloon,...
A table TypeSize to store an ID and all possible types and sizes.

Using a combobox you can show each category. When they enter a value which is not in the list, you can choose to add it to your table. Same goes for the types and sizes table.

In the Patients table you can store the ID's of the Category and TypesSizes tables.

Hope you're getting closer to a solution.