Query works; Report gives SQL Error

[Hillary]

I have an inherited database from a friend that I am doing some work on. The naming convention is giving me some problems. I don't want to put a lot of time into re-creating the database or changing table/query names so I hoping someone can help me with a quick solution.

The query works great. I used the wizzard to create a report and I get the following error when I try to open the report, The specified field '[Full Name].[Full Name]' could refer to more than 1 table listed in the From clause of your SQL statement.

The SQL statement is SELECT [Full Name].[Full Name], [Count Class Attendees.CountOfCustomer Name]+[Count Independant Purchases.CountOfCustomer Name]+[Count Free Gift.CountOfCustomer Name] AS [Count]
FROM (([Full Name] LEFT JOIN [Count Class Attendees] ON [Full Name].[Full Name] = [Count Class Attendees].[Full Name]) LEFT JOIN [Count Independant Purchases] ON [Count Class Attendees].[Full Name] = [Count Independant Purchases].[Full Name]) LEFT JOIN [Count Free Gift] ON [Count Independant Purchases].[Full Name] = [Count Free Gift].[Full Name]
ORDER BY [Full Name].[Full Name];

Thanks for your help!


Hillary

 

[dhookom]

Try alias Full Name and I think your bracketing is off. I would also not alias and expression with [Count] since it is a property of reports and lots of other objects. I changed it to NameCount.

SELECT [Full Name].[Full Name] as FullName, [Count Class Attendees].[CountOfCustomer Name]+[Count Independant Purchases].[CountOfCustomer Name]+[Count Free Gift].[CountOfCustomer Name] AS NameCount
FROM (([Full Name] LEFT JOIN [Count Class Attendees] ON [Full Name].[Full Name] = [Count Class Attendees].[Full Name]) LEFT JOIN [Count Independant Purchases] ON [Count Class Attendees].[Full Name] = [Count Independant Purchases].[Full Name]) LEFT JOIN [Count Free Gift] ON [Count Independant Purchases].[Full Name] = [Count Free Gift].[Full Name]
ORDER BY [Full Name].[Full Name];

Duane
MS Access MVP