Creating a continuous subform with same information (1 Viewer)

lulucheung95

New member
Local time
Today, 19:03
Joined
Apr 19, 2020
Messages
4
I am creating an input form for medical staff to record the used medical consumables for each operation. For each operation, there will be a unique case no., patient name, operation date, operation name and surgeon, however, multiple items will be used for each operation.

I created a main form that contains text boxes for the staff to input the case no., patient name, operation date etc., and a continuous subform which I expected staff could quickly input the multiple lines of product information (e.g. item code, name, brand and qty). The data should be entered to the master table in which each data line represents a unique item used in operation. (So I expect multiple lines will have same case no., patient name, operation date etc., hope you guys understand how it looks like)

But each time when I try to enter a new line of data, a dialogue box popped out and said I couldn't do this because duplicate values are prohibited, unless I change the properties of the fields. Then I changed the "index" of the fields to "Yes (allow duplicates)" but the problem still exists. What should I do? Or is there another better form design that could do the same thing?

Anything/any comments would help, the nurses are recording the consumables with their hands.......and this could be a big problem given human errors can be very nasty in healthcare settings. Thank you.😭😭😭
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
Can you post up a sample of your tables and demo data? On a first pass, it sounds like your tables aren't set up correctly.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:03
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Did you link the subform to the main form using the Linked Master and Child Fields? If so, which field(s) did you use? Can you show us your table structure and indices? Thanks.
 

lulucheung95

New member
Local time
Today, 19:03
Joined
Apr 19, 2020
Messages
4
@theDBguy @Minty
Thanks! I am newbie to Access so I tried many times and didn't get much of the work done....However, here is what I need in the master table
Untitled.png

For example, a patient underwent a surgery today, and this patient used 14 items in that surgery, then the first 6 columns will be duplicated values with same operation information and the last 4 columns will be different for the 14 data lines. Then I drafted a very rough form like this:
1.png

At first I hope I can quickly enter the data in the continuous subform but I found out that it doesn't work. Is there any other way to do what I meant to do?
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
Okay - you have done what I thought you might have - "spreadsheet!ed" your design.
You want a minimum of two tables Operation and OperationConsumables

Operations holds the "headline data" (the stuff that repeats), and the consumables has a link back to that table and has multiple records that relate back to it.
 

Micron

AWF VIP
Local time
Today, 07:03
Joined
Oct 20, 2018
Messages
3,476
You MUST research and understand normalization if you want this to go well. You likely need a staff table as well as something akin to what was already pointed out. tblStaff might look like
StaffID
StaffType
LName
FName

unless you also have tables for staff so that you can use combos to lookup values rather than allowing free-form typing. Then there's patients...
 

zeroaccess

Active member
Local time
Today, 06:03
Joined
Jan 30, 2020
Messages
671
Look up "one-to-many relationships".

For each Operation, you can have many Consumables.

So

tblOperations
All the things that there is only one of: If there is only ever one Operation Date, Operation Name, Patient Name, then those items stay here. However if an Operation can ever span multiple dates, I would consider that.

If there is ever a possibility that an Operation has more than one Nurse, more than one Surgeon, or more than one Input Staff, those need to be in a different table. Maybe tblOperationsStaff. This will reference a tblStaff that lists all of your Staff members.

tblOperationsConsumables
Used Product Code, Used Product Name, Used Product Brand, and Quantity all go here. They will reference a tblProducts that contains all things you have in your inventory. Then you get into inventory tracking, which is more complex.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:03
Joined
Jan 23, 2006
Messages
15,364
Can you have more than 1 surgeon involved in the operation? Good approach is to get the business facts identified. Then base your tables and relationships on the facts. Get a data model of your setup - you can vet that model with test data and some test scenarios before getting too deep into physical Access. Easier to change things in the model than in the physical database.

To assist you with learning Normalization and Table Design and relationships, I recommend you work through 1 or 2 of the tutorials at RogersAccessLibrary listed below. By working through the tutorial(s) you will experience a process to identify tables and relationships and build a blueprint for your database. That process can be used with any database.
ZYX Labs
Class Info Systems

More info on Database Planning and Design etc in the link in my signature.
 

lulucheung95

New member
Local time
Today, 19:03
Joined
Apr 19, 2020
Messages
4
@Minty @Micron @zeroaccess @jdraw

Thank you for your information! I've re-designed the whole thing into 4 tables named tblOperation (contain the information of each operation with a unique code called "HN number"), tblConsumable (contain the consumed items with a linkage at "HN number" with tblOperation), tblProduct (holds all information of all products/consumables) and tblStaff (Staff type and staff name for combo boxes in tblOperation. Like the below pic:
reply.png


Then I presume the next step is to design the input form. Should I use a main form (ask about operation info) with an embeded continuous subform (a table format of input form to enter the consumables in that particular operation)? Is it possible to do it in this way in Access?

Or is there any better design to get an input form? As we, medical staff, do not use computer much, it's better to get the input form as stright-forward as possible. Thanks a lot!
 

zeroaccess

Active member
Local time
Today, 06:03
Joined
Jan 30, 2020
Messages
671
A good first step, but you need foreign keys in your tables (such as an OperationID in tblConsumable) that will hold any records from the many side of the relationship to tblOperation. Then create a relationship between the PK and FK fields that are equal.

I would also be clear about what tblConsumable is - if this is recording consumables for each operation, I might name it something more descriptive like tblOperationConsumables so it's clear that it is the consumables used for a specific operation, and not an inventory table. But that's just me.

I think you also need a tblOperationStaff to record the staff involved in each operation. If that is what tblStaff is, again a name that is more descriptive would help avoid future confusion as the current name makes it sound like the table that holds your employees. Again make sure you have the OperationID as a foreign key for any staff records that are related. Ideally, staff members would be chosen with a drop down or similar functionality on your form(s), and those names would come from an employees table.

Each of these foreign keys will be automatically added for each record once you set up your subform links properly.
 

lulucheung95

New member
Local time
Today, 19:03
Joined
Apr 19, 2020
Messages
4
@jdraw
Actually multiple surgeons could be in one surgery but we record only 1 surgeon as representative and we only record 1 staff as the "input staff" for each operation. So they can merely be a drop down list to choose surgeon/input staff for each operation, and staff members will be chosen from an employees table, which is "tblStaff" in my case.

Every operation will have unique HN number, even if the patient enter our theatre twice, there will be two HN number assigned to each operation. Therefore, HN is a unique, non-null and non-duplicable number. I used it as the foreign key in tblConsumable to link the used items with the operation.

@zeroaccess YES! your advice is brilliant and I changed the names of the tables to avoid confusion. It is not necessary to record all surgeons/nurses involved in the operation as we only need 1 person as the responsible staff for that particular operation for tracking and tracing.

reply1.png


Your advice and guidance are truly helpful. I've gone through each comment and the notes provided, they are very informative. Please kindly advice some keywords/concepts I will need to know for the next step/subform design as the instruction provided by Microsoft is minimal and scattered. It will be great if a systematic approach for learning database management and access application is available. Thank you!
 

Micron

AWF VIP
Local time
Today, 07:03
Joined
Oct 20, 2018
Messages
3,476
Post what the relationship window looks like after your corrections?
 

Users who are viewing this thread

Top Bottom