Form and Subform structure help

[Chillendw]

Hey all,

So I'm having some trouble figuring out how to set up my form/subform. Heck, I'm not even 100% sure that it's what I need.

tblExipients
- ExipientID (PK), ExipientLotNumber, ExipientAmount
- Each ExipientLotNumber has a different ExipientAmount

tblSolution
- SolutionID (PK), SolutionName, ExipientID (FK), Manufacturer
- So 1 SolutionName can have several ExipientLotNumbers, but they each have a different ExipientAmount

I was making a form that had tblSolution as the main form, then tblExipients as the subform. My logic was that for each SolutionName, each different ExipientLotNumber would show up. (I'm using this form to create new records).
However, when I tried it, the main form's records consisted of several SolutionNames. I'm unsure what I'm doing wrong, and if this is even the best strategy and/or best form/table structure.

Thanks in advance for any help.

 

[MarkK]

What is the real-world problem? For one row in tblSolution, are there many Excipient rows, or is the other way around? Your table design, with the foreign key in the Solution table, seems to suggest that one Excipient row should have many related Solution rows, but that's not what I would expect from what 'solution' and 'excipient' mean.

But we can't advise you without knowing the purpose of your system, and how the real-world problem is structured.

 

[Chillendw]

What is the real-world problem? For one row in tblSolution, are there many Excipient rows, or is the other way around? Your table design, with the foreign key in the Solution table, seems to suggest that one Excipient row should have many related Solution rows, but that's not what I would expect from what 'solution' and 'excipient' mean.

But we can't advise you without knowing the purpose of your system, and how the real-world problem is structured.

Thanks for the reply Mark. I'm basically trying to keep track of how many excipients we receive and use. It may sound weird but where I'm at, the solution is already mixed with the excipient, but on the label it includes how much excipient there is. That's what I'm keeping track of.

A certain solution can contain several different ExcipientLotNumbers AS ELN, and each one correlates to a different amount of excipient. However, it is not easily coded to where ELN is R2000 meaning 2000 mL. While each ELN has a different volume, there can be several ELNs to one solution. And an ELN not specific to just one vial, it can be on several.

Hope this explains it.
Thanks.

 

[MarkK]

This sounds like a many-to-many relationship, in which you have a Solution, and a series of unrelated volumes of Excipient. Then, there should be a third table that joins the excipient to the solution...

tblSolution
SolutionID (Primary Key)
SolutionName
MixDate

tblExcipient
ExcipientID (PK)
ExcipientName
LotNumber

tblSolutionExcipient
SolutionExcipientID (PK)
SolutionID (Foreign Key)
ExcipientID (FK)
Volume

See if that makes sense. In this case the SolutionExcipient table can record the volume of each excipient, drawn from larger lots, which comprise a single solution. Maybe this fits the structure of your real word problem?

 

[Chillendw]

See if that makes sense. In this case the SolutionExcipient table can record the volume of each excipient, drawn from larger lots, which comprise a single solution. Maybe this fits the structure of your real word problem?

I'll give this a look-see. Thanks.

In regards to a form, would I then need 2 sub-forms within the main form to update all 3 of the tables?

 

[MarkK]

I would not do it all in one form. The workflow should be totally different, like, probably your shipper/receiver is the person who needs to update the excipient table, right? Those volumes are lot numbers, presumably larger quantities that are available for the creation of solutions, right? So that's a problem for the shipping/receiving department. Give them their own user interface to work with their data.

You, by contrast, are concerned with making solutions and blending excipients as a totally different workflow. In that case I would expect to see a mainform on the solution table, and a subform on the SolutionExcipient table. And I would expect that subform to be linked to the mainform on SolutionID, and have a combo on the ExcipientID field to show available Excipients from the Excipient table.