Crystal,
I must say, you are absolutely, positively, without a doubt, THE BEST
EVER!!!
Thank you so much, I now get one card per recipe (unless of course it needs
to go to 2 cards). One last problem with it though. Now when I view the
recipe card report, the subreport shows the recipe id number. When I look at
the sub report by itself it is not visible. If I go into the main report's
design view I do not actually see the subform, but a white box which only
says Table.RecipeIngredients. Though having the recipe id visible (It is
marked to not be visible in the properties of the subreport itself) is not a
big deal for myself, I plan to share this database with several people in my
family and would prefer it wasn't there so there is no confusion between it
and the quantity field. Any ideas?
Thank you again!!!!!!
Teri.
:
Teri,
After some more thought -- since you have a certain size
card you are printing on -- is that the subreport solution
would be the way to go. That way, you can layout the exact
size for the card in the main report -- much as you have it
now -- except:
1. the main report will just be based on Recipes, so there
would be only one card per recipe
2. Where you have the ingredients, you would have a
subreport control with the size you want.
The subreport is created just as a report is -- it is called
a subreport because of the way it is used.
In the subreport, you would have one line per ingredient.
Make sure you put the RecipeID on the subreport so that you
can link it to the main report. On the subreport, RecipeID
would have
Visible --> No
When I have controls that are not visible, I make the
backcolor black and the text white so they stand out more
when you are in the design view
Don't you also have cooking instructions?
In the future, you should avaoid using spaces in fieldnames
and tablenames -- they complicate things
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
strive4peace wrote:
either:
1. GROUP the report by recipeID or RecipeName
A. put RecipeID and RecipeName in the group header section
B. put ONE line in the detail section for the ingredient
Click Sorting & Grouping from the report design (from the menu: View,
Sorting & Grouping)
Choose RecipeName or RecipeID as a grouping field and GroupHeader --> yes
OR
2. Make a main report based on Recipes and a subreport based on Recipe
Ingredients
for the subreport:
LinkMasterFields --> RecipeID
LinkChildFields --> RecipeID
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Teri wrote:
Crystal,
Hope you are having a fabulous day!!
It is a report with a subreport which contains the ingredients,
measurements and comments regarding the ingredients (for example -
chopped, luke warm, diced, etc). My SQL for the query reads as follows:
SELECT DISTINCT [Recipe Ingredients].Quantity, Recipes.Instructions,
Recipes.Notes, [Recipe Ingredients].Comments, Recipes.RecipeName,
Recipes.RecipeID, [Recipe Ingredients].IngredientID
FROM Recipes INNER JOIN [Recipe Ingredients] ON Recipes.RecipeID =
[Recipe Ingredients].RecipeID
WHERE (((Recipes.[Add to shopping List?])=Yes))
ORDER BY Recipes.RecipeName;
I noticed that it indicates SELECT DISTINCT and then lists the fields
as you mentioned, but it still returns the multiple cards. I finally
realized that the number of cards it returns directly coincides with
the number of ingredients listed in the recipe.
I am baffled! I am finding that I don't much care for Access, only
because I just can't get a grip on these queries or relationships.
Maybe it will all fall into place for me someday, but right now it is
turning my hair gray faster then my kids are!
Thank you again!
Teri.
:
Hi Teri,
Thank you -- same to you
Do you have a main report/subreport? Or are you using grouping?
When you select UniqueValues*, the SQL will change from
SELECT field1, field2, etc
to
SELECT DISTINCT field1, field2, etc
IF you select UniqueRecords*, the SQL will change to
SELECT DISTINCTROW field1, field2, etc
UniqueValues and UniqueRecords are mutually exclusive -- you can only
choose one of them. They are part of the Query properties
1. turn on the properties window
from the menu --> View, Properties
2. click in a light gray area of the query that is not a column or a
fieldlist
The titlebar of the properties window will say "Query Properties" --
there, you will see these properties
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Teri wrote:
Hello Crystal, it is a pleasure to "meet" you!
Thank you so much for your response, I truly appreciate it. In your
reply you first mention going to the record set of my FORM, I have
not created a form. I am working with a REPORT. Would it be the
same? I did go into my report's properties, found the recordsource
property and went to my query through it. I right clicked on the
upper gray area of the query (I believe you meant where the table(s)
used are shown) and found the uniquevalues selection and changed it
from No to Yes. I saved it and ran my query, there was no change, I
am still getting a return of multiple cards for the same recipe.
When I checked the SQL view of my query, it makes no mention of
unique values. Did I do something wrong?
Thank you again for trying to help me.
Teri.
:
Hi Teri,
try this:
in the recordset for your form, set UniqueValues = true
1. go to the query builder for the recordset
turn on the properties window
click the ... in the RecordSource property for the main form
2. right-click in a gray area of the query
3. UniqueValues --> true
4. close and update the SQL
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Teri wrote:
I have downloaded an Access template from Microsoft.com for
recipes. I am creating a report so that I can print out recipes
(with ingredients and instructions) in the form of a 5x8 index
card. I have gotten the form to work properly as far as having
all the necessary information included and fitting on the card.
My issue is that I now get anywhere from 5 to 10 of the same card
as opposed to 1 card for each recipe. Any ideas on how I can get
Access to return just one card for each recipe?
Thank you so much in advance!!
Teri.
