Multiple field calculations

[Spencer Nils]

Hi I'm fairly new to access. I have 2 tables of different brands. One with 3
rows of information(450 mg of one brand) and one with 7 rows of information
(120 mg of the other brand). In the row headings are mg, name, batch no,
squalene %, and actual squalene, (then the same for two other ingredients.)
I need to multiply each one, by each other, for a total of 21 combination's.
(If i could I'd like to make it possible for future new entries in either
table to be automatically multiplied by the rest of the rows.) I would like
to have a formula that will do it automatically for all of them so i don't
have to type 21 of basically the same formula. My second problem is that each
item has a batch No. i need that to show in the answer to the calculation.
The calculation i need would look like this (450 mg * 21.1%) + (120 mg *
25.5%) that needs to be >= X. then we do the same calculation for the two
other ingredients and if they are all greater than X they are acceptable to
use and we add them together and if that total is >= X then we can use that
mixture. The percentages are different for all 21 combination's. I know
this is long and confusing but any help would be great I can even email you
the table or the spreadsheet I made in excel if that helps.

 

[dch3]

If the fields are identical in both tables (same name, same datatype) that's
a red flag. The tables should be combined adding a new field to capture the
brand name (Tylenol, Advil, etc,) indicating the brand that the data pertains
to. Good database design groups like data in the same table.

 

[Spencer Nils]

Thanks for the help I think I did as you said? Any help on a calculation that
will multiply each of the individual 7 batches by each of the individual 3
batches.. so i'd get 21 different solutions? Or do i have to write out an
individual calculation for each of the 21 combination's?

 

[Thomas Winterfeldt]

Thanks for the help I think I did as you said? Any help on a calculation
that
will multiply each of the individual 7 batches by each of the individual 3
batches.. so i'd get 21 different solutions? Or do i have to write out an
individual calculation for each of the 21 combination's?

Hi Nils,
I believe the solution is the following, now that everything is in one
table.
Create one select query q1 that shows all the values of one brand and a
query q2 for the 2nd brand.
Then create a 3rd query based on q1 and q2 with an expression expr: q1.value
* q2.value. including the batches
q1.batch and q2.batch will let you recognize your results.
I hope I got you right and this helps.

oh. you can avoid creating q1 and q2. simply create a select query that is
based on two instances of your table (the cartesian product of the table
with itself).
exclude rows with the same brand by a condition and display the expression
as before.
this version will allow you to extend the method to more than two brands.

Regards
Thomas