Automatic form with calculations based on two tables (1 Viewer)

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
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.

1708422217617.png
 

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
sure here it is. but careful, i don't have my product in them yet. i can just try it with 'test products'.
 

Attachments

  • Database 12.accdb
    3.3 MB · Views: 33

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
i have different productgroups which i want to add in different subforms. just to give you a bit of background info
 

mike60smart

Registered User.
Local time
Today, 21:36
Joined
Aug 6, 2017
Messages
1,917
Hi
So for your Product ProfileT you can have 1 or More Seals
 

mike60smart

Registered User.
Local time
Today, 21:36
Joined
Aug 6, 2017
Messages
1,917
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

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
Ok. i'll give it a try. i'll come back to you with an update. thanks for now!
 

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
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?
 

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
i've added products in two tables. one for profiles and one for accessories. i haven't linked them yet though
 

Attachments

  • Database 12.accdb
    3.5 MB · Views: 32

mike60smart

Registered User.
Local time
Today, 21:36
Joined
Aug 6, 2017
Messages
1,917
Hi Danielle

Look at the Products Form and also study the Relationship Diagram

Also you should NOT have spaces in field Names
 

Attachments

  • Database 12.zip
    871.2 KB · Views: 38

DanielleCO

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
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

Member
Local time
Today, 22:36
Joined
Feb 18, 2024
Messages
31
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

Registered User.
Local time
Today, 21:36
Joined
Aug 6, 2017
Messages
1,917
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,246
if you can provide and English database, i am sure Many can help you.
 

Users who are viewing this thread

Top Bottom