Are my goals realistic

[Leggatto]

Hi there. I'm building a patient database and I just started learning Access yesterday. I've been through 3 different tutorials and I have a tentative grasp on Access basics. I was hoping I could explain some of my goals with the database and someone could tell me if my goals are realistic within the software.

Like I said, the database if of surgical patients. There's multiple sections of information to gather. The way I have it currently setup is tables for each group. For instance, there is a table for patient demographic information, a table for patient past medical history, etc. My end goal, ideally, would be to have forms to input data into each field for each patient. Rich Holowczak has a basic tutorial and he talks about Switchboards which sound like giant forms that direct the user between different tables. Is that a correct interpretation?

One of my questions is whether or not there are ways to "point" to different tables. I have a Preop&Surgery table that goes through a lot of information. There is a TypeOfSurgery Field which has a relationship to a SurgeryList table. The SurgeryList table is pictured below and is just a list of surgeries.



The PreopSurg Table looks like this



Then I set up a relationship like this



That being done, the TypeOfSurgery field now acts as a Lookup Field (I think that's the correct name) allowing me to put multiple types of surgery in the field per patient. This is important because people commonly get multiple procedures done at the same type. For instance, it's very common for someone to get a Diagnostic Laparscopy, IOUS, and peritoneal washing in the same OR session.

So that's all well and good. But I would like some more functionality. Many of the surgeries on the SurgeryList have other parameters that need to be filled out. To choose a simple one, a diagnostic laparoscopy needs to have a place the findings can be input. I made a DiagnosticLaparoscopy table for this purpose.





Is there someway I can have the program "point" to this table when Diagnostic Laparotomy is chosen in the Type of Surgery field?

If you've made it this far, thank you so much. You're a saint. I apologize for my ignorance and am very appreciative of your time.

[ranman256]

You don't have to build a relationship of Patient&Surgery and Surgery.
TSurgeryList is a lookup table, it only feeds the options for the field.
You DO need relationship for ownership....parent-child tables.
tPatients and tPatient&Surgery (1 to many)
1 patient has many surgeries,and maybe even further, THAT 1 surgery can have may conditions it it too.
tPatient--tPatient&Surgery--tSurgeryDetails

[orange]

Leggatto,

Your goals may or may not be realistic. Your issue at the moment is primarily database design, normalization, relational database theory, business analysis and any standards related to your line of business. You need to have a clear set of requirements that indicate the bounds of your proposed system and the related rules. Do not jump into physical database too quickly. Get your requirements and a plan organized so you know what you're doing.
It's very possible that HIPPA is relevant to your proposed project.

Here are some related links that may help
see this link for more info
another

I have seen many posts/articles re HIPPA and that Access is not compliant. I am not in health care field and will defer you to other who are.

Most Access developers, including M$oft, will suggest you have no spaces or special characters in your field and object names.

Here is a free, generic data model that may show some relationships you may need to consider.

Good luck.

[Micron]

Lookup fields are to be avoided as well. Here's why
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Ditto on the special characters
https://access-programmers.co.uk/for...d.php?t=225837

[NTC]

If by asking "are goals realistic" - do you mean 'is Access able' - - the answer is definitely yes... assuming the record set volume & simultaneous user count can be supported by today's PCs (which are pretty darn powerful). Access is as functionally capable as any other database brand in terms of building a Windows application.

[Leggatto]

Thanks to everyone who replied. My big takeaway is to study about this quite a bit more. I was likely getting a little ahead of myself. Thanks for your time in reading.