I am trying to create a query to use in a report. I am needing to self join
one table. The table consist of Family Number, Child Number, Wish Item. I
am needing the query to concatenate the wish item to one field.

So I need a field to look like this:

basketball baseball
soccer football

The results I am getting with my current SQL (listed below) is this:

basketball baseball
basketball soccer
basketball football

What am I doing wrong?

SQL:

SELECT DISTINCT [Child Wish List1].[Family Number], [Child Wish List1].[Child
Number], [Child Wish List1].[Child Wish] + " " + [Child Wish List2].
[Child Wish]
FROM [Child Wish List] as [Child Wish List1] LEFT JOIN [Child Wish List] as
[Child Wish List2] on [Child Wish List1].[Family Number] = [Child Wish List2].
[Family Number] and [Child Wish List1].[Child Number] = [Child Wish List2].
[Child Number] and [Child Wish List1].[Child Wish] <> [Child Wish List2].
[Child Wish];

I don't think you want the join on the wishes, just the key field(s). Rather than a join, would something like this work for you?

http://www.mvps.org/access/modules/mdl0004.htm

I receive the same results when I eliminate the wish item. Also the concatenate is used for more than one table. This is one table that I need to join to itself.

What does the data in the table look like? A child with 2 wishes would have 2 records? Can you post a sample we can play with?

yes, thank you

the table likes like this

familyID ChildID WishItem
1 A basketball
1 A baseball
1 A football
1 A soccer
1 B Barbie
1 B make-up
1 B doll house

my query needs to show the data like this

Family 1 Child A
basketball baseball football
soccer

Family 1 Child B
Barbie make-up dollhouse

I would seriously consider the above function. It's the simplest way to get the look you want, and more dynamic. If you go with the self join and build it for your sample data which has a maximum of 4 records, you'll have to fix it when somebody has 5. The function would flow automatically with that. More info on self joins here:

http://allenbrowne.com/ser-06.html