2 fields from separate tables needed to create calculated field (1 Viewer)

jack_attridge

Registered User.
Local time
Today, 01:08
Joined
Aug 14, 2013
Messages
28
Hi guys,

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

Thanks for any help
 

DavidAtWork

Registered User.
Local time
Today, 01:08
Joined
Oct 25, 2011
Messages
699
as long as you can link the 2 records required to provide the 'Max Rated Power' and 'Cylinder Capacity' values, then it can be calculated.
I assume you have PK/FK's in the required tables to link on

David
 

Mihail

Registered User.
Local time
Today, 03:08
Joined
Jan 22, 2011
Messages
2,373
Is it possible to keep them in separate tables/subforms and still calculate the field?
Not only possible but is the recommended practice.
You must (I repeat: must, necessary) to remove the max power density field from your DB.
Design a query that use Max Rated Power and Cylinder Capacity fields in order to calculate the max power density.
It is no reason to store the max power density if you can calculate "on demand".
 

jack_attridge

Registered User.
Local time
Today, 01:08
Joined
Aug 14, 2013
Messages
28
DavidAtWork,The method I was trying to take was to have a 'cylinder capacity' field in both table/subform 1 and table/subform 2 (not visible on subform2) and then link them by making them PK and FK so that they will equal each other. From here, I can then just produce a simple calculated field in my form for 'max power density'. However, it doesn't seem to be working.

Mihail, it needs to be on the form and visible for the user so does this mean your query idea won't do what I want?
 

Mihail

Registered User.
Local time
Today, 03:08
Joined
Jan 22, 2011
Messages
2,373
DavidAtWork,The method I was trying to take was to have a 'cylinder capacity' field in both table/subform 1 and table/subform 2 (not visible on subform2) and then link them by making them PK and FK so that they will equal each other. From here, I can then just produce a simple calculated field in my form for 'max power density'. However, it doesn't seem to be working.

Mihail, it needs to be on the form and visible for the user so does this mean your query idea won't do what I want?

Always a solution is possible.
But DO NOT STORE CALCULATED DATA in you DB.
 

jack_attridge

Registered User.
Local time
Today, 01:08
Joined
Aug 14, 2013
Messages
28
OK thank you, I got rid of it from my database and created the calculated query which works fine.

I have other equal fields across the various tables that I would want to make equal but can not obviously make them all primary and foreign keys, is it possible to relate them any other way? For example, engine type is in both project overview main form and engine definition sub form however its data entries are not definitely unique. There are no calculations needed, they are just the same fields in different tables that I need to relate.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:08
Joined
Feb 19, 2013
Messages
16,553
Defining relationships is just a way of documenting how the db works and how the different parts (tables and queries) relate to each other - it does not affect the way it works (i.e. you don't need to define a relationship in order to link two tables) or force you to 'follow a particular path'.

If you want to look up a value in tableB where some field matches a field in tableA, just link the two tables on the common field in your query - you may need to use a left join to ensure all records in tableA are returned if there is not a matching record in tableB
 

jack_attridge

Registered User.
Local time
Today, 01:08
Joined
Aug 14, 2013
Messages
28
Ok, well thats good news, but are you able to explain in a bit more detail please? Would this enable me to make the field in subform2 autofill to the equal value of a field in subform 1?
 

Users who are viewing this thread

Top Bottom