September 6, 2007 at 3:24 am
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 & Position | | | | | | |
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 & Position | 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 & Position | 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!
September 6, 2007 at 7:42 am
When you describe this "Excel-like" setup - is that absolutely required as the data entry mechanism, or are you describing what the "report" coming out of this data should look like? Because, as a table structure - that's a normalization disaster in the making.
How about something more normalized for the data entry? Something like:
EpisodeID Instrument Type Attempt# model successful?
12345 catheter 1 CathsRUS No
12345
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 10, 2007 at 11:42 am
I think you better stay at the drawing board for a little bit.
Separate tables for
cath
stent
wire
incidence (with a many to one relationship to procedure)
procedure
Form to create a new procedure
Form to create each incidence with data constraints based on records in cath/stent/wire tables.
Report to pull data by procedure
This will keep your data uniform and also allow you to run additional reports in the future, ie. "How many incidences were there for Stent ABC?"
I'm an old hand at Access and an SQL Server nooblet so I would be happy to help.
September 11, 2007 at 2:20 am
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/Type 4/Type 5/Type6 | ||||||
| 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/Type 4/Type 5/Type6 | ||||||
| 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/Type 4/Type 5/Type6 | ||||||
| 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/Type 4/Type 5/Type6 |
Thanks
Kelly
September 11, 2007 at 11:14 am
It's not the data entry - it's answering the "questions" that derive from this kind of data model that will continue to haunt you.
The purpose for data of this sort if for some type of analytical work. "how many times was this type of instrument effect? in conjunction with what?" The minute they expect those answers - this data model will have you crashing your machine and/or that very same user screaming that performance SUCKS, because it will. And it will unfortunately be because the data model is wrong.
I don't think you need separate tables for separate types of tools, but in order to get any kind of reliable data you really want the users typing this stuff in to have a list to pull from. Thankfully - Access has some abilities to give you those opens (Something like - keep a table of all of the previous choices typed in - if someone types something new in, ask if they want it added to the list or not).
What you do need to think about is how the instruments relate to the # of tries (the # you're running across the top). you REALLY don't want to end up in a circumstances where you have numbers running across the top ad nauseam. That's not a database - that's EXCEL. Databases don't work that way, and they do "big analysis" on datasets bigger than Excel can handle for a reason - the data is set up for that to work that way. And that way is "long, but skinny tables" not "short wide tables". In this case skinny and wide refer to the NUMBER of fields, and not so much how wide each field is.
So - right now you have at least 3 tables: the instrument list, the tries (and what is so special about them), and the instruments USED during each try. You probably have more, but I don't have enough to know what.
Quite honestly - if you don't have any flexibility on the data structure and they're not going to listen to you on how the data entry should work or how to improve data reliability (office buzzword for that one is "data integrity"), then give them an excel solution and we can talk about how to get that Excel data into something an DATABASE can actually use. I mean - if the data is no good, then the reports based on that data will be no good, in which case - what's the purpose of tracking the data to begin with?
Not putting your foot down now is a major downpayment into a pain mortgage, and those have a really high interest rates. In case it hasn't become clear yet - I've walked in your shoes, and let's just say the sting hasn't died down these long years later. I've learned to tell the client not to "meddle" in the data structure, especially if they're in healthcare. You don't tell them how to treat a patient, so they don't tell you how to organize their data. It's for their own good (REALLY - it's for their own good).
by the way - we're now talking about data modeling, and what a relational database gives you, NOT MSAccess per se. Any database should force you to these questions. I'd encourage you to do some reading up on relational databases and/or normalization: it should help you immensely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2007 at 7:09 am
If you need an excellent place to start on normalization, please look at the list here.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 13, 2007 at 5:18 pm
Quote:
"Not putting your foot down now is a major downpayment into a pain mortgage, and those have a really high interest rates. In case it hasn't become clear yet - I've walked in your shoes, and let's just say the sting hasn't died down these long years later. I've learned to tell the client not to "meddle" in the data structure, especially if they're in healthcare. You don't tell them how to treat a patient, so they don't tell you how to organize their data. It's for their own good (REALLY - it's for their own good)."
Well said. If I could have a dollar for every developer I have seen hurting (myself included), because the work wasn't done up front to inform the client where the boundaries were, I would be able to give up this database lark! Sure the client knows the business, but you know the technology and should know how that can satisfy the business requirements by listening to and learning from the client. It's a team thing.
The second comment of importance is that of studying database modelling and normalisation. I used to teach Access and used to tell my classes that before they touch any computer or application, they get hold of the biggest sheet of paper they can, start at one end, and hope that by the other end they have the kernel of a design.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply