Duplicates returned in report

[Guest]

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.

 

[strive4peace]

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

*

 

[Guest]

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]

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

*

 

[Guest]

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]

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

*

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

*

 

[strive4peace]

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

*

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

*

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.

 

[Guest]

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

*

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

*

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.

 

[strive4peace]

Hi Teri,

thank you for your kind words

If the visible property is no, perhaps you have RecipeID on
your subreport twice...unless you are making it visible in
code, I do not see how it could be showing...when you render
the card, look at the location of the recipedID and see if
it matches up to the control you have visible = no for.

Another thing you can do is this:

on the subreport, put recipeID in the report header and make
the report header Visible = no


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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

*

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.

 

[Guest]

Again, I must reiterate, YOU ARE THE VERY BEST EVER!! Thank you so much for
all your help! I put the RecipeID in the report header and did Visible=No
and it worked! I am almost done with everything I need to do with database,
I am starting to feel really good about it, thanks to you!

Teri.

Hi Teri,

thank you for your kind words

If the visible property is no, perhaps you have RecipeID on
your subreport twice...unless you are making it visible in
code, I do not see how it could be showing...when you render
the card, look at the location of the recipedID and see if
it matches up to the control you have visible = no for.

Another thing you can do is this:

on the subreport, put recipeID in the report header and make
the report header Visible = no


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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.

 

[strive4peace]

Hi Teri,

flattery gets you everywhere... thanks ... you are welcome
I am glad you got it!

Now you can put the kid's favorite recipes in ... hey,
another thought ... make the following tables...

*Notes*
NoteID, autonumber
RecipeID, long integer
PID, long integer
Rating, integer
(0 = doesn't like, 10=love)
Comments, memo

*People*
PID, autonumber
Lastname, text
Firstname, text
Middlename, text
DOB, date
Comments, memo

Now, you can make another subform on your recipe form -- use
the tab control to easily switch between subforms when you
are looking at recipes. Then, when someone likes (or
doesn't like) a recipe, you can add comments!

make a continuous subform based on Notes

LinklChildFields --> RecipeID
LinkMasterFields --> RecipeID

PID will be a combobox

Name --> PID
ControlSource --> PID
RowSource --> SELECT PID,
Firstname & " " & Lastname as Who
FROM People
ORDER BY Firstname, Lastname
Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
(this adds up to the Column Widths)

Rating will be a combobox

Name --> Rating
ControlSource --> Rating
RowSourceType --> ValueList
RowSource --> 0;"hate";1;"";2;"";3;"";4;"no
opinion";5;"";6;"";7;"";8;"";9;"";10;"love"
(you can fill in words where I didn't on the other numbers
or change the numbering system)
ListRows --> 10
(so they all show)
Columncount --> 2
ColumnWidths --> 0;1
ListWidth --> 1

Comments will be a textbox

OnDouble-click -->
[Event Procedure]
DoCmd.RunCommand acCmdZoomBox

(so it will zoom up when you double-click on it and show you
more information)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Again, I must reiterate, YOU ARE THE VERY BEST EVER!! Thank you so much for
all your help! I put the RecipeID in the report header and did Visible=No
and it worked! I am almost done with everything I need to do with database,
I am starting to feel really good about it, thanks to you!

Teri.

:

Hi Teri,

thank you for your kind words

If the visible property is no, perhaps you have RecipeID on
your subreport twice...unless you are making it visible in
code, I do not see how it could be showing...when you render
the card, look at the location of the recipedID and see if
it matches up to the control you have visible = no for.

Another thing you can do is this:

on the subreport, put recipeID in the report header and make
the report header Visible = no


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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.

 

[Guest]

Crystal,

You know everything, don't you! You are truly a "God Send" to me on this.
You listed a couple of things I have actually taken care of already in a way
that works for me. But I do have another issue if you are up to it. Let me
know!

Teri.

Hi Teri,

flattery gets you everywhere... thanks ... you are welcome
I am glad you got it!

Now you can put the kid's favorite recipes in ... hey,
another thought ... make the following tables...

*Notes*
NoteID, autonumber
RecipeID, long integer
PID, long integer
Rating, integer
(0 = doesn't like, 10=love)
Comments, memo

*People*
PID, autonumber
Lastname, text
Firstname, text
Middlename, text
DOB, date
Comments, memo

Now, you can make another subform on your recipe form -- use
the tab control to easily switch between subforms when you
are looking at recipes. Then, when someone likes (or
doesn't like) a recipe, you can add comments!

make a continuous subform based on Notes

LinklChildFields --> RecipeID
LinkMasterFields --> RecipeID

PID will be a combobox

Name --> PID
ControlSource --> PID
RowSource --> SELECT PID,
Firstname & " " & Lastname as Who
FROM People
ORDER BY Firstname, Lastname
Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
(this adds up to the Column Widths)

Rating will be a combobox

Name --> Rating
ControlSource --> Rating
RowSourceType --> ValueList
RowSource --> 0;"hate";1;"";2;"";3;"";4;"no
opinion";5;"";6;"";7;"";8;"";9;"";10;"love"
(you can fill in words where I didn't on the other numbers
or change the numbering system)
ListRows --> 10
(so they all show)
Columncount --> 2
ColumnWidths --> 0;1
ListWidth --> 1

Comments will be a textbox

OnDouble-click -->
[Event Procedure]
DoCmd.RunCommand acCmdZoomBox

(so it will zoom up when you double-click on it and show you
more information)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Again, I must reiterate, YOU ARE THE VERY BEST EVER!! Thank you so much for
all your help! I put the RecipeID in the report header and did Visible=No
and it worked! I am almost done with everything I need to do with database,
I am starting to feel really good about it, thanks to you!

Teri.

:

Hi Teri,

thank you for your kind words

If the visible property is no, perhaps you have RecipeID on
your subreport twice...unless you are making it visible in
code, I do not see how it could be showing...when you render
the card, look at the location of the recipedID and see if
it matches up to the control you have visible = no for.

Another thing you can do is this:

on the subreport, put recipeID in the report header and make
the report header Visible = no


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Teri wrote:

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

 

[strive4peace]

Hi Teri,

thank you, happy to help!
(I don't know NEARLY everything ... just more than average )

post your question in a new thread -- I will look for you

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal,

You know everything, don't you! You are truly a "God Send" to me on this.
You listed a couple of things I have actually taken care of already in a way
that works for me. But I do have another issue if you are up to it. Let me
know!

Teri.

:

Hi Teri,

flattery gets you everywhere... thanks ... you are welcome
I am glad you got it!

Now you can put the kid's favorite recipes in ... hey,
another thought ... make the following tables...

*Notes*
NoteID, autonumber
RecipeID, long integer
PID, long integer
Rating, integer
(0 = doesn't like, 10=love)
Comments, memo

*People*
PID, autonumber
Lastname, text
Firstname, text
Middlename, text
DOB, date
Comments, memo

Now, you can make another subform on your recipe form -- use
the tab control to easily switch between subforms when you
are looking at recipes. Then, when someone likes (or
doesn't like) a recipe, you can add comments!

make a continuous subform based on Notes

LinklChildFields --> RecipeID
LinkMasterFields --> RecipeID

PID will be a combobox

Name --> PID
ControlSource --> PID
RowSource --> SELECT PID,
Firstname & " " & Lastname as Who
FROM People
ORDER BY Firstname, Lastname
Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
(this adds up to the Column Widths)

Rating will be a combobox

Name --> Rating
ControlSource --> Rating
RowSourceType --> ValueList
RowSource --> 0;"hate";1;"";2;"";3;"";4;"no
opinion";5;"";6;"";7;"";8;"";9;"";10;"love"
(you can fill in words where I didn't on the other numbers
or change the numbering system)
ListRows --> 10
(so they all show)
Columncount --> 2
ColumnWidths --> 0;1
ListWidth --> 1

Comments will be a textbox

OnDouble-click -->
[Event Procedure]
DoCmd.RunCommand acCmdZoomBox

(so it will zoom up when you double-click on it and show you
more information)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Again, I must reiterate, YOU ARE THE VERY BEST EVER!! Thank you so much for
all your help! I put the RecipeID in the report header and did Visible=No
and it worked! I am almost done with everything I need to do with database,
I am starting to feel really good about it, thanks to you!

Teri.

:



Hi Teri,

thank you for your kind words

If the visible property is no, perhaps you have RecipeID on
your subreport twice...unless you are making it visible in
code, I do not see how it could be showing...when you render
the card, look at the location of the recipedID and see if
it matches up to the control you have visible = no for.

Another thing you can do is this:

on the subreport, put recipeID in the report header and make
the report header Visible = no


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Teri wrote:


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