Automatic form with calculations based on two tables

[DanielleCO]

Hi, This the issue i'm facing.
I have different products that relate to each other.
Example: when i have an order for 6m of a profile, i know i also need to add 6m of a certain seal.

i've made a print screen of the how it looks in excel and what i want to achieve.

so basicly, when i create a subform with all my profiles, i want my subform with seals to automaticly update with the right quantity based on the information filled in the subform with profiles.

 

[mike60smart]

Hi
Can you upload a copy of your database?

 

[DanielleCO]

sure here it is. but careful, i don't have my product in them yet. i can just try it with 'test products'.

 

[DanielleCO]

i have different productgroups which i want to add in different subforms. just to give you a bit of background info

 

[mike60smart]

Hi
So for your Product ProfileT you can have 1 or More Seals

 

[DanielleCO]

Hi
So for your Product ProfileT you can have 1 or More Seals

indeed. some profiles need 2 seals which are different. some have only 1 seal. i have this specification in my products details.

 

[mike60smart]

Remember that the Product is the one side , in other words a Many to Many layout

You need a table of Products
tblProducts
-ProductID - PK - Autonumber
-Productname
-(other fields describing the product)

A Table of Seals
tblSeals
-SealID - PK - Autonumber
-Seal

Then a junction table to link the Products to Seals
tblProductSeals
-tblProductSeals
-ProductSealID - PK - Autonumber
-ProductID - FK - Linked to PK from tblProducts
-SealID- FK - Linked to PK from tblSeals

 

[DanielleCO]

Ok. i'll give it a try. i'll come back to you with an update. thanks for now!

 

[DanielleCO]

Ok i managed to make the junction table? Do i have to make a new query now? what's the next step to get my subform for my seals to be automaticly updated when i fill in the subform for my profiles?

 

[mike60smart]

Can you upload your current database?

 

[DanielleCO]

i've added products in two tables. one for profiles and one for accessories. i haven't linked them yet though

 

[mike60smart]

Hi Danielle

Look at the Products Form and also study the Relationship Diagram

Also you should NOT have spaces in field Names

 

[DanielleCO]

Ok. i'll have a look. i'm just starting a training course at work but i'm impatient to look at your work. i'll keep you posted. again Mike, thank you so much so far!

 

[DanielleCO]

Hi Mike, sorry to bother you again but i'm really struggling with the file you send me. I'm actually completely lost. i have no idea how this junction table works or how it helps me. I don't know if you have the time but do you think it's possible to have a video chat about it? i think it might be more useful to have a face to face chat about it to explain my needs.
Again, i'm very grateful for your help so far. it's just that all these things are new to me.
thanks!

 

[mike60smart]

Hi Mike, sorry to bother you again but i'm really struggling with the file you send me. I'm actually completely lost. i have no idea how this junction table works or how it helps me. I don't know if you have the time but do you think it's possible to have a video chat about it? i think it might be more useful to have a face to face chat about it to explain my needs.
Again, i'm very grateful for your help so far. it's just that all these things are new to me.
thanks!

Hi Danielle
I will send you a personal message now

 

[arnelgp]

if you can provide and English database, i am sure Many can help you.