Calculated field??

[Guest]

I have a query in a recipe database which pulls the ingredients I need for
the recipes I select along with the quantity (fields used are Ingredient,
quantity and comments[which indicates measurement]). What I need to do is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For example I
have chosen two different recipes which both require Parmesan Cheese, one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to add the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Ken Snell \(MVP\)]

Give us the SQL statement of the query that pulls the ingredients that are
in the recipes you select. Then we can show you how to write that calculated
field.

 

[Guest]

Here is my SQL. Thank you so much for taking the time to help me with this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.

Give us the SQL statement of the query that pulls the ingredients that are
in the recipes you select. Then we can show you how to write that calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I need for
the recipes I select along with the quantity (fields used are Ingredient,
quantity and comments[which indicates measurement]). What I need to do is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For example I
have chosen two different recipes which both require Parmesan Cheese, one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to add the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Ken Snell \(MVP\)]

If you want a list of the ingredients and the total amount you need, this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>

Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.

Give us the SQL statement of the query that pulls the ingredients that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need to do
is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For example
I
have chosen two different recipes which both require Parmesan Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Guest]

Ken,

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

If you want a list of the ingredients and the total amount you need, this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>

Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.

Give us the SQL statement of the query that pulls the ingredients that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need to do
is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For example
I
have chosen two different recipes which both require Parmesan Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Ken Snell \(MVP\)]

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be able
to do what you want. Getting a decimal representation of a fraction is as
simple as dividing out the fraction. If you leave it as the text with
fractions, it'll take a fairly sophisticated function to parse and then
calculate the quantity sums.
--

Ken Snell
<MS ACCESS MVP>

Ken,

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I
don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

If you want a list of the ingredients and the total amount you need, this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>

Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.
:

Give us the SQL statement of the query that pulls the ingredients that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need to
do
is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Guest]

Okay, keeping in mind that I decided to do this database to begin with to
make my life easier, will add a label or two to my recipe entry form that
will be a constant reminder of what decimal amount equals what fraction. If
I get stuck figuring it out, I will ask my kids for help, they are good at
math. I still have alot of things I would like to do with this database, and
it only makes sense to do it right the first time, before I get too many
recipes input into it. I will make the changes and try your SQL and will
come back if I have any problems. I have already printed it off so that I
can find it easily.

Thanks again, Ken, I really do appreicate your help with this!

Teri.

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be able
to do what you want. Getting a decimal representation of a fraction is as
simple as dividing out the fraction. If you leave it as the text with
fractions, it'll take a fairly sophisticated function to parse and then
calculate the quantity sums.
--

Ken Snell
<MS ACCESS MVP>

Ken,

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I
don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

If you want a list of the ingredients and the total amount you need, this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>


Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.
:

Give us the SQL statement of the query that pulls the ingredients that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need to
do
is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Ken Snell \(MVP\)]

< fingers can be such a problem.... >
1/8 = 0.125 (1 divided by 8 !!!!)

Good luck!

--

Ken Snell
<MS ACCESS MVP>

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be
able to do what you want. Getting a decimal representation of a fraction
is as simple as dividing out the fraction. If you leave it as the text
with fractions, it'll take a fairly sophisticated function to parse and
then calculate the quantity sums.
--

Ken Snell
<MS ACCESS MVP>

Ken,

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I
don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

If you want a list of the ingredients and the total amount you need,
this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>


Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients]
ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.
:

Give us the SQL statement of the query that pulls the ingredients
that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need
to do
is
create a calculated field which will return total amount of a
single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Jamie Collins]

Further considerations:

· In addition to the quantity you need to (separately) persist the
measure e.g. 1 teaspoon of sugar, 1 cup of sugar, 1 sugar lump, 1 pound
of sugar, etc. You may need a conversion tables to be able to achieve
your totals.

· Recipes are famous for being BOMs (bill of materials, parts
explosion). Modelling such hierarchies in SQL is a book in itself:

http://www.dbazine.com/ofinterest/oi-articles/celko24
Graphs, Trees, and Hierarchies by Joe Celko

"imagine a restaurant with a menu. The menu disassembles into dishes,
and each dish disassembles into ingredients, and each ingredient is
either simple (e.g., salt, pepper, flour), or it is a recipe, itself,
such as béarnaise sauce or hollandaise sauce. These recipes might
include further recipes."

Jamie.

--

 

[Guest]

Ken! Sorry for the delay in letting you know how things worked out, started
a new job and have had family in town. Got to let you know that you MVPs are
AWESOME, PHENOMENAL, STUPENDOUS, ETC, ETC!!

Thanks for your help with this, I REALLY appreciate it! The ONLY downfall
is the fact that I can't have my comments field in there, its the field that
lists my measurements (each, cup, tsp, tbsp, etc). I will be posting another
question soon, I hope you are the one that finds it, you are GREAT!

Can you tell I am thankful and thrilled?

Teri.

< fingers can be such a problem.... >
1/8 = 0.125 (1 divided by 8 !!!!)

Good luck!

--

Ken Snell
<MS ACCESS MVP>

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be
able to do what you want. Getting a decimal representation of a fraction
is as simple as dividing out the fraction. If you leave it as the text
with fractions, it'll take a fairly sophisticated function to parse and
then calculate the quantity sums.
--

Ken Snell
<MS ACCESS MVP>

Ken,

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I
don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

:

If you want a list of the ingredients and the total amount you need,
this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>


Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients]
ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.
:

Give us the SQL statement of the query that pulls the ingredients
that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need
to do
is
create a calculated field which will return total amount of a
single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Ken Snell \(MVP\)]

< blush > You're very welcome.

Because your comments likely are specific to the recipes, including the
comments field for a "summation" query would not be appropriate.

Give some thought to adding a field that would be a fixed "unit of measure"
for an ingredient (e.g., sugar is always going to be represented in
teaspoons), and then you can "have fun" with doing calculations to convert
one cup of sugar in one recipe to teaspoons and store that value, which then
will make the summation easier and more meaningful. (Of course, having a
variety of measurements in different recipes will make this "not so easy"!)

Keep persevering... you'll get your desired result yet. Good luck on the new
job.
--

Ken Snell
<MS ACCESS MVP>

Ken! Sorry for the delay in letting you know how things worked out,
started
a new job and have had family in town. Got to let you know that you MVPs
are
AWESOME, PHENOMENAL, STUPENDOUS, ETC, ETC!!

Thanks for your help with this, I REALLY appreciate it! The ONLY downfall
is the fact that I can't have my comments field in there, its the field
that
lists my measurements (each, cup, tsp, tbsp, etc). I will be posting
another
question soon, I hope you are the one that finds it, you are GREAT!

Can you tell I am thankful and thrilled?

Teri.

< fingers can be such a problem.... >
1/8 = 0.125 (1 divided by 8 !!!!)

Good luck!

--

Ken Snell
<MS ACCESS MVP>

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be
able to do what you want. Getting a decimal representation of a
fraction
is as simple as dividing out the fraction. If you leave it as the text
with fractions, it'll take a fairly sophisticated function to parse and
then calculate the quantity sums.
--

Ken Snell
<MS ACCESS MVP>


Ken,

This stinks! I can't use your query. I have the quantity field set
as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because
I
don't
know how to figure out the decimal form of measurements like 1/8.

Thank you any way, I really appreciate your trying to help me.

Teri.

:

If you want a list of the ingredients and the total amount you need,
this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients]
ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.

--

Ken Snell
<MS ACCESS MVP>


Here is my SQL. Thank you so much for taking the time to help me
with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity,
[Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe
Ingredients]
ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

Thank you again!

Teri.
:

Give us the SQL statement of the query that pulls the ingredients
that
are
in the recipes you select. Then we can show you how to write that
calculated
field.

--

Ken Snell
<MS ACCESS MVP>

I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I
need
to do
is
create a calculated field which will return total amount of a
single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want
to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.

Any help in figuring this out would be GREATLY appreciated!!

Thanks,

Teri.

 

[Marshall Barton]

Teri, You should do what Ken says and save the decimal
numbers in the table for any other calculations you may want
to do in the future (e.g. doubly the recipe).

If you use an unbound text box (name it txtQuant), then you
can get Access to do the math for you and save it by placing
the decimal value in your bound text box (named Quantity?).
The only syntax rule is that you write your fractions with a
space between any whole number and its fractional part (e.g.
1 1/4). This makes it really easy to convert to the
decimal value (e.g. 1.25)

For example, 1 1/4 can be made into a legal expression just
by replacing the space with a + sign, (1+1/4). Ken's entire
sophisticated function <grin> can be done in your txtQuant
text box's AfterUpdate event:

Me.Quantity = Eval(Replace(Me.txtQuant, " ", "+"))

This expression will not change a decimal number if you
should choose to enter it that way. Neither will it change
a whole number so it should be safe in all cases.

If/when you get around to doing calculations like doubling
the recipe, you will want a function to convert the decimal
numbers back to fractions. Actually you will probably want
the function immediately for use in reports and probably to
display the table value as a fraction on your form (in the
txtQuant text box). There are several of these available in
the Google Newsgroup archives or we can post our favorites
here.
--
Marsh
MVP [MS Access]

Okay, keeping in mind that I decided to do this database to begin with to
make my life easier, will add a label or two to my recipe entry form that
will be a constant reminder of what decimal amount equals what fraction. If
I get stuck figuring it out, I will ask my kids for help, they are good at
math. I still have alot of things I would like to do with this database, and
it only makes sense to do it right the first time, before I get too many
recipes input into it. I will make the changes and try your SQL and will
come back if I have any problems. I have already printed it off so that I
can find it easily.

1/8 = 0.125 (1 divided by 9)

I strongly suggest that you change the field's design so that you'll be able
to do what you want. Getting a decimal representation of a fraction is as
simple as dividing out the fraction. If you leave it as the text with
fractions, it'll take a fairly sophisticated function to parse and then
calculate the quantity sums.

This stinks! I can't use your query. I have the quantity field set as a
text field so that I can do 1 1/2 as opposed to 1.5. This is because I
don't
know how to figure out the decimal form of measurements like 1/8.


:
If you want a list of the ingredients and the total amount you need, this
query should provide that for you:

SELECT Ingredients.Ingredient, Sum([Recipe Ingredients].Quantity) AS
HowMuch
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
GROUP BY Ingredients.Ingredient
ORDER BY Ingredients.Ingredient;

I deleted the Comments field from your query because having it in the
above
query will not let the Sum value be for the single ingredient.



Here is my SQL. Thank you so much for taking the time to help me with
this,
I really appreciate it!!

SELECT Ingredients.Ingredient, [Recipe Ingredients].Quantity, [Recipe
Ingredients].Comments
FROM Recipes INNER JOIN (Ingredients INNER JOIN [Recipe Ingredients] ON
Ingredients.IngredientID = [Recipe Ingredients].IngredientID) ON
Recipes.RecipeID = [Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Ingredients.Ingredient;

:

Give us the SQL statement of the query that pulls the ingredients that
are
in the recipes you select. Then we can show you how to write that
calculated
field.


I have a query in a recipe database which pulls the ingredients I
need
for
the recipes I select along with the quantity (fields used are
Ingredient,
quantity and comments[which indicates measurement]). What I need to
do
is
create a calculated field which will return total amount of a single
ingredient if that ingredient is used in multiple recipes. For
example
I
have chosen two different recipes which both require Parmesan
Cheese,
one
recipe calls for 1/2 cup and the other calls for 1 cup. I want to
add
the
1/2 cup to the 1 cup and have Parmesan Cheese listed once on my
list.