Re: Duplicates returned in report -- Group or Report/Subreport

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"strive4peace" <"strive4peace2006 at yaho" wrote:


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.

"strive4peace" <"strive4peace2006 at yaho" wrote:



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.

.

Relevant Pages

  • Re: Duplicates returned in report -- DISTINCT, DISTINCTROW
    ... SELECT DISTINCT [Recipe Ingredients].Quantity, Recipes.Instructions, ... UniqueValues and UniqueRecords are mutually exclusive -- you ... They are part of the Query ... instructions) in the form of a 5x8 index card. ...
    (microsoft.public.access.reports)
  • Re: Duplicates returned in report -- put ID n header of subreport
    ... I put the RecipeID in the report header and did Visible=No ... on the subreport, put recipeID in the report header and make ... I now get one card per recipe (unless of course it needs ...
    (microsoft.public.access.reports)
  • Re: C reactive protein -0-; SED rate -0-
    ... recipe wasn't being followed quite correctly. ... I report the news when it comes into me. ... "Since starting the pepper bread I have never felt better, ... Instead he was cancer free in 6 months. ...
    (misc.health.alternative)
  • Re: Duplicates returned in report -- Report/Subreport
    ... 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. ... you can layout the exact size for the card in the main report -- much as you have it now -- except: ... Make a main report based on Recipes and a subreport based on Recipe Ingredients ...
    (microsoft.public.access.reports)
  • Re: C reactive protein -0-; SED rate -0-
    ... recipe wasn't being followed quite correctly. ... So this is the second report. ... "Since starting the pepper bread I have never felt better, ... Instead he was cancer free in 6 months. ...
    (misc.health.alternative)