DB Design Concept (1 Viewer)

[diversoln]

I have a Table that will record chemical (antibody) use in fields as follows:

Antibody_Name
Lot_Number
Order_Date
Received_Date
Open_Date
Exhaust_Date

There are several chemicals, each from a specific manufacturer. So I have another Table as follows:

Antibody_Name
Manufacturer

I want a form that will allow me to choose the antibody from a list created using the manufacturer table (a combo box). Once the antibody is selected, I want to call up a continuous form that will display the antibody name and manufacturer in the header and allow me to enter the lot number and dates in the continuous form.

I'm having a couple problems.....

1.) when I join the two tables linking the Antibody_Name field, I lose my ability to add records to the query and the form.

2.) the value in my combo box used to select the chemical name is not being passed to my query - when it runs, no records are selected and the resulting form is blank.

I think I'm missing something in the design but I'm not sure what it is....

The query SQL is below ....

SELECT Antibody_Filter.Antibody_Name, Antibody_Filter.Lot_Number, Antibody_Filter.Order_Date, Antibody_Filter.Received_Date, Antibody_Filter.Open_Date, Antibody_Filter.Exhaust_Date, Antibody_Filter.Manufacturer
FROM Antibody_Filter
WHERE (((Antibody_Filter.Antibody_Name)=Forms![Antibody_Pop-up]!Antibody_Select));

Thanks for any help you can provide.

 

[meboz]

The antibody usage details need to be in a subform on the form your trying to open.

The recordsource of the main form should just be the Antibody list table, that houses the manufacturer names.

Have a command button on the popup form that opens the main form and find a record ie applys a filter based on your combo box selection

 

[Pat Hartman]

1. I don't think that your table design is correct. Is it true that antibodies can come from only ONE manufacturer? Are you certain that you will NEVER have a situation where the same antibody can be supplied by more than one manufacturer?
2. Add primary keys to both tables.
3. You'll need to change the antibody table so that it has the new key field from the other table and remove the antibody_name field since it is no longer needed.
4. Create a relationship using the relationship window and enforce referential integrity.

 

[Crilen007]

AntibodyID (Primary Key, click the little key in table design mode)
AntibodyName
AntibodyLot
AntibodyOrderDate
AntibodyReceivedDate
AntibodyOpenDate
AntibodyExhaustDate
ManufacturerID



ManufacturerID (Primary Key, click the little key in table design mode)
ManufacturerName


Now in the relationships editor, link the two "ManufacturerID" together.

 

[Crilen007]

Here is an example.

 

[diversoln]

Pat,

Your question about alternative sources for the antibodies is a valid one. I do anticipate a future where suppliers change. That's why I want to have a table for antibodies with their corresponding manufacturers. With this in mind, do the steps you've recommended still apply ?

If I don't set up a table with just antibodies and their corresponding manufacturers, it seems the user will end up having to type in (or select) the manufacturer for each entry into the table showing the lot ordering, receipt, open, and exhaust dates. If this is the case, why would I even need the second table with the manufacturer and manufacturer key?

Thanks.

 

[Crilen007]

Wait, I dont get what you mean.

you have the separate tables so they DONT have to type it each time.


Can you tell us what this is for?

Giving a table layout and asking to know the best way doesnt work.

We need to know how data is entered, IE:


The user selects a manufacturer, and adds the stuff to a list, linked to the manufac

or

The user selects the chemical, and selects the manuf.


Like, how are things entered?

And does an antibody only get opened once? Don't you need multiple tables for when its opened and stuff on different dates?

Please explain what this does.

 

[diversoln]

Dear Crilen007,


The concept is to keep track, by lot number, when antibodies are ordered, recieved, opened, and exhausted. There are several types of antibodies so when the user is working with one in particular, it's nice to filter the data so they can see the history for that one antibody type.

My thought is to use a form showing a combo box as a picklist to filter the data in the main table to be displayed on the lot history data entry form. This picklist is not working. Its as if the query is not seeing the value in the combo box and the result is a null set of data.... If I hardcode the same value into the query's criteria - it filters properly! Do you know why this is happening? What should I look for in my combo box parameters? (See SQL posted in first message)

Anyways, over time, there will be several records for Antibody A.... a lot will be ordered, received, opened, and eventually used up over the course of a few weeks or months. And at some point prior to the lot being used up, another lot of Antibody A is ordered so its on hand and ready to use when the time comes. The users will interact with the system a few times a week updating the status as needed for approximately 50 different antibody types.

It would be nice to show the manufacturer on the filtered form. While the rule is to buy each antibody from its best source, that source could (and probably will) change over time. Aside from it just being "nice" to show the manufacturer on the data entry form, the manufacturer information will also be very helpful to see the order response of the manufacturers. (How long does it take to receive the antibody once its been ordered from a given source). This would be handled in a seperate query/report function of the database.

My initial thought of tying the Antibody to another Table that shows the Manufacturers for the Antibodies would allow the user to enter the Antibody-Manufacturer pairing one time and update it when the Manufacturer changes. If the manufacturer is part of the Lot History Table, the user will need to record the manufacturer with every record of the lot history ...... This seems inefficient to me, since the manufacturer of a given antibody will not typically be changing lot to lot.

Please share your thoughts ..... Thanks.

 

[Pat Hartman]

Your structure is actually many-to-many and you have lots of additional data fields for the relation table (tblAntibodyShipment). Don't forget to set up relationships using the relationship window and check the enforce referential integrity box.

tblAntibody
AntibodyID (autonumber primary key)
AntibodyName
etc.

tblManufacturer
ManufacturerID (autonumber primary key)
Manufacturer
etc.

tblAntibodyShipment
AntibodyShipmentID (autonumber primary key)
AntibodyID (foreign key to tblAntibody)
ManufacturerID (foreign key to tblManufacturer)
LotNumber
OrderDate
ReceivedDate
OpenDate
ExhaustDate

The above shipment table will not be sufficient if you want to track usage. You should really use a transaction table where each event is logged separately - order, receive, use. These transactions are summed to show stock on hand, etc.