Basic help needed

[Vincew]

Hi, I am trying to build a BOM (i think it called) database for my Cider production. I have got the basic record keeping bit working for each apple pressing, however now i want to "Blend" various ciders together and am not sure how to combine multiple records in to a new record in another table. I will do further calculations with the numbers "ABV and So2" that are brought over later but trying to sort 1 thing at a time.

I realise this is probably a very basic thing but I just can't seem to work it out from videos that all seem to talk about invoices and profits etc.

I have attached a simplified example of what I am trying to do.

In the table "Pressing" I have the "Cider name" the "ABV" the "So2" and the date pressed.

In the "Blending" table I have the Blend name, then 3 sections to select which pressing make up the blend, I have dropdowns for "cider1","cider2","cider3" where I can select which ciders from the pressing table will go in to the blend, then the date of the blending.

When i select a cider from the dropdown how do i get it to bring over the "ABV" and "So2" that is recorded for that pressing?

In the example I want the record in the "Blend" table to read as follows after selecting Browns, gold, dab in the appropriate drop downs.

Vintage, Browns, 4.7, 52, gold, 5.6, 85. dab, 7.2, 122, 20/09/2024

Cheers
Vince

 

[Gasman]

When i select a cider from the dropdown how do i get it to bring over the "ABV" and "So2" that is recorded for that pressing?

Include them as extra columns in the combo, then just refer to the columns. Combos start at 0.

 

[plog]

You don't move data over and your table structure is incorrect.

1. You shouldn't store data in multiple places. You shouldn't move data from Pressing to Blend, you simply relate them (via primary key) and then when you need all the data in both tables together you JOIN them in a query.

2. You shouldn't have numerated fields nor groups of fields. In Blend you've got 3 groupings of fields--1 group per every pressing. That is incorrect. Data shouldn't be accomodated horizontally (with more fields) but vertically (with more rows).

What happens if more than 3 pressings? What about less than 3? You need a junction table to sit between Blend and Pressing tables to sort out which pressings go to which blends and vice versa. This should be how your tables should be set up:

Blends
blend_ID, autonumber, primary key
blend_Name, text, same as in BlendName currently
blend_Date, date, same as Blenddate currently

BlendPressings
bp_ID, autonumber, primary key
ID_Blend, number, foreign key to Blends.blend_ID
ID_Pressing, number, foreign key to Pressing.ID

Pressing remains the same. From a form perspective you would have a main form based on Blends and subform based on BlendPressings where you would be able to assign pressings to blends.

 

[Pat Hartman]

The blends table needs to be 1 row for each component so it needs three rows rather than three sets of columns

 

[Vincew]

You don't move data over and your table structure is incorrect.

1. You shouldn't store data in multiple places. You shouldn't move data from Pressing to Blend, you simply relate them (via primary key) and then when you need all the data in both tables together you JOIN them in a query.

2. You shouldn't have numerated fields nor groups of fields. In Blend you've got 3 groupings of fields--1 group per every pressing. That is incorrect. Data shouldn't be accomodated horizontally (with more fields) but vertically (with more rows).

What happens if more than 3 pressings? What about less than 3? You need a junction table to sit between Blend and Pressing tables to sort out which pressings go to which blends and vice versa. This should be how your tables should be set up:

Blends
blend_ID, autonumber, primary key
blend_Name, text, same as in BlendName currently
blend_Date, date, same as Blenddate currently

BlendPressings
bp_ID, autonumber, primary key
ID_Blend, number, foreign key to Blends.blend_ID
ID_Pressing, number, foreign key to Pressing.ID

Pressing remains the same. From a form perspective you would have a main form based on Blends and subform based on BlendPressings where you would be able to assign pressings to blends.

Hi,

Thanks for your input, most of which has gone over my head, so i will just deal with 1 item in this reply. I have changed the blend table a bit so there is no transferred data just the totals that i need to see. The aim is to have the ability to have up to 6 components to the blend. if less than that i would expect the cells to be blank for text and default to 0 for number fields.

The blends table needs to be 1 row for each component so it needs three rows rather than three sets of columns

I do not understand this, I have 1 blend named "vintage" which has in this example have 3 components. how can 1 record be 3 rows?

updated file

 

[MarkK]

This is a many-to-many relationship. As others have noted, it should be modelled using three tables, like...

 

[plog]

You have a many-many relationship between Blends and Pressings (1 blend can have many pressings : 1 pressing can go to many blends). Therefore you need a junction table (aka associative entity) to sit between both those tables to sort out which pressings go to which blends and vice versa:

Associative entity - Wikipedia

en.wikipedia.org

Reread my initial post where I explain how to implement that.

edit--or better yet check out Mark's example database where you gives you the correct structure for your database.