Re: Duplicates returned in report -- DISTINCT, DISTINCTROW

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
    ... the SQL will change from ... UniqueValues and UniqueRecords are mutually exclusive -- you can only choose one of them. ... They are part of the Query properties ... I have gotten the form to work properly as far as having all the necessary information included and fitting on the card. ...
    (microsoft.public.access.reports)
  • Re: Duplicates returned in report -- Group or Report/Subreport
    ... Click Sorting & Grouping from the report design ... Make a main report based on Recipes and a subreport based on Recipe Ingredients ... My SQL for the query reads as follows: ... UniqueValues and UniqueRecords are mutually exclusive -- you can only choose one of them. ...
    (microsoft.public.access.reports)
  • Re: Clearing all fields with code problem
    ... In a saved query you could use the following as criteria ... The cards are assigned to staff in our building, so the card is either blank, I.e no one has that card or someone has the card and the record is filled in. ... There is only one table apart from several small tables that populate the drop down boxes. ... What code should I enter that will check if there is any thing to delete in my OLE picture field ...
    (microsoft.public.access.gettingstarted)
  • Re: Problem with VBA Code behind form
    ... >> through which you can see the report. ... graphics card. ... the query calls from the table details of all ... >> selection is limited further by only choosing the records checked on ...
    (microsoft.public.access.forms)
  • Re: Problem with VBA Code behind form
    ... > with Radeon 64Mb graphics card. ... >> show up on the report, ... I'd execute an update query ...
    (microsoft.public.access.forms)