Hi all, sorry to bother you all, especially if this has already been answered, but I can't seem to find the solution to it. Assuming that what I've been doing has been correct that is.
I have three (3) tables with the following fields (where PK is primary key);
[tbl Ingred]: IngredID (PK), ProdID_fk, CompCode, IngredName, Weight, Symbol, RP01, RP02, RP03, RP04, RP05, RP06, RP07, RP08, RP09, RP10
[tbl Prod]: ProdID (PK), ProdName, FP, BP, SBU, ProdNotes
[tbl RPCalc]: RPs, RPs_long (PK), Sym, Limit, Sym_1, RP_1, Comp_1, Add, RP_01, RPNotes
[tbl Prod] has a one-to-many relationship with [tbl Ingred]; that is, for one ProdID, there can be many IngredIDs. The tables are designed so that the final user-displayed form is 'user-friendly'.
I've made a qry called [qry Qcalc] used to calculate the the Quotation (Q) of each of the 10 RPs (RP01 - RP10). Which simply for the first one is; QRP01: [Weight]/[tbl RPCalc_1].Limit, where the [tbl Ingred].[RP01] is linked to a corresponding [tbl RPCalc], by the [RPs] field. As for one [RPs] there may be multiple [RPs_long]. The way it is set up is so that the [RPs] and [RPs_long] are mapped correctly, to be used for calculations.
However, I am now stuck with a problem. I need to sum up these QRP values ([QRP01] - [QRP10]), based on (at least?) two conditions. The user can enter in their RPs; e.g. R10, R20, R21 in any of the [RP01] to [RP10]'s, and in any order.
What I need is to be able to obtain a sum of the Q values for each [RPs_long] (e.g. R10, R20, R21), rather than being the sum of the fields [RP01] to [RP10]. Also, each [RPs_long] may or may not be able to be summed up, depending on another field [Add] which has a "Yes" or "No". "Yes" is for it being able to be summed up, and "No" for it not being additive.
So the end result would be; for each [ProdID], there will be multiple [IngredID] where each will have up to ten (10) "RPs" (that being the [RP01] to [RP10] fields). These inputted "RPs" are then related to [tbl RPCalc] to be able to obtain the Q-values. If the Q-Value is not greater or equal to 1 (>= 1), then the sum of the Q-values for each [RPs_long], provided that the [Add] field is "Yes", will then be able to determine what are the final output RPs to be used, i.e. for the sum of the Q-values are greater or equal to 1 (>= 1), that [RPs_long] will be used.
I've been stuck on this for quite some time, and I know that if I change the whole table structure (like having a field for each RPs in the [tbl Ingred]), I might be able to make it much easier for myself. However, by doing so, it will not be able to offer as much flexibility in the calculations, and also, may not make it as 'user-friendly'. Another thought was maybe to create another table with just the [CompCodeID] (based on [tbl Ingred].[CompCode]), [Weight], [RP], but I'm not too sure how this will work either, but I might give it a shot if this current method doesn't work.
I hope that this made sense, as it is all quite complex (and confusing) to me. Any help on this matter will be much appreciated. Thanks in advance.
I have three (3) tables with the following fields (where PK is primary key);
[tbl Ingred]: IngredID (PK), ProdID_fk, CompCode, IngredName, Weight, Symbol, RP01, RP02, RP03, RP04, RP05, RP06, RP07, RP08, RP09, RP10
[tbl Prod]: ProdID (PK), ProdName, FP, BP, SBU, ProdNotes
[tbl RPCalc]: RPs, RPs_long (PK), Sym, Limit, Sym_1, RP_1, Comp_1, Add, RP_01, RPNotes
[tbl Prod] has a one-to-many relationship with [tbl Ingred]; that is, for one ProdID, there can be many IngredIDs. The tables are designed so that the final user-displayed form is 'user-friendly'.
I've made a qry called [qry Qcalc] used to calculate the the Quotation (Q) of each of the 10 RPs (RP01 - RP10). Which simply for the first one is; QRP01: [Weight]/[tbl RPCalc_1].Limit, where the [tbl Ingred].[RP01] is linked to a corresponding [tbl RPCalc], by the [RPs] field. As for one [RPs] there may be multiple [RPs_long]. The way it is set up is so that the [RPs] and [RPs_long] are mapped correctly, to be used for calculations.
However, I am now stuck with a problem. I need to sum up these QRP values ([QRP01] - [QRP10]), based on (at least?) two conditions. The user can enter in their RPs; e.g. R10, R20, R21 in any of the [RP01] to [RP10]'s, and in any order.
What I need is to be able to obtain a sum of the Q values for each [RPs_long] (e.g. R10, R20, R21), rather than being the sum of the fields [RP01] to [RP10]. Also, each [RPs_long] may or may not be able to be summed up, depending on another field [Add] which has a "Yes" or "No". "Yes" is for it being able to be summed up, and "No" for it not being additive.
So the end result would be; for each [ProdID], there will be multiple [IngredID] where each will have up to ten (10) "RPs" (that being the [RP01] to [RP10] fields). These inputted "RPs" are then related to [tbl RPCalc] to be able to obtain the Q-values. If the Q-Value is not greater or equal to 1 (>= 1), then the sum of the Q-values for each [RPs_long], provided that the [Add] field is "Yes", will then be able to determine what are the final output RPs to be used, i.e. for the sum of the Q-values are greater or equal to 1 (>= 1), that [RPs_long] will be used.
I've been stuck on this for quite some time, and I know that if I change the whole table structure (like having a field for each RPs in the [tbl Ingred]), I might be able to make it much easier for myself. However, by doing so, it will not be able to offer as much flexibility in the calculations, and also, may not make it as 'user-friendly'. Another thought was maybe to create another table with just the [CompCodeID] (based on [tbl Ingred].[CompCode]), [Weight], [RP], but I'm not too sure how this will work either, but I might give it a shot if this current method doesn't work.
I hope that this made sense, as it is all quite complex (and confusing) to me. Any help on this matter will be much appreciated. Thanks in advance.
