Multiple field calculations

  • Thread starter Thread starter Spencer Nils
  • Start date Start date
S

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.
 
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.
 
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?
 
Spencer Nils said:
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?
Click to expand...

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
 
Back
Top