The record source for my report pulls the proper records and sorts them the way I want it to. When I run the report, I get an error message, "The field Name.Name could refor to more than one table in the From clause of your SQL." Why does the query work but not the report? Here is my SQL:
SELECT Lots.NameID, [Preliminary Inventory Query].LatestDate, [Propagation and Potting Merge].PropQuantity, [Propagation and Potting Merge].ContainerID, Lots.[Lot Number], Name.Name
FROM Name INNER JOIN (Lots INNER JOIN ([Propagation and Potting Merge] INNER JOIN [Preliminary Inventory Query] ON ([Preliminary Inventory Query].[Lot Number] = [Propagation and Potting Merge].[Lot Number]) AND ([Propagation and Potting Merge].PropDate = [Preliminary Inventory Query].LatestDate)) ON Lots.[Lot Number] = [Preliminary Inventory Query].[Lot Number]) ON Name.NameID = Lots.NameID
ORDER BY Name.Name;
If I remove Name.Name from my report then it works fine. The only problem is it sorts by nameID I need it to sort by name. The query will sort by name.name with no problem.
Thank you.
Full Version: Record Source Query Functions But Report Does Not
Welcome to Utteraccess.
Name is a reserved word. You should not have any table named name nor any field named name.
Robert
Name is a reserved word. You should not have any table named name nor any field named name.
Robert
QUOTE (gwe4 @ Mar 7 2010, 10:08 PM) 
The record source for my report pulls the proper records and sorts them the way I want it to. When I run the report, I get an error message, "The field Name.Name could refor to more than one table in the From clause of your SQL." Why does the query work but not the report? Here is my SQL:
SELECT Lots.NameID, [Preliminary Inventory Query].LatestDate, [Propagation and Potting Merge].PropQuantity, [Propagation and Potting Merge].ContainerID, Lots.[Lot Number], Name.Name
FROM Name INNER JOIN (Lots INNER JOIN ([Propagation and Potting Merge] INNER JOIN [Preliminary Inventory Query] ON ([Preliminary Inventory Query].[Lot Number] = [Propagation and Potting Merge].[Lot Number]) AND ([Propagation and Potting Merge].PropDate = [Preliminary Inventory Query].LatestDate)) ON Lots.[Lot Number] = [Preliminary Inventory Query].[Lot Number]) ON Name.NameID = Lots.NameID
ORDER BY Name.Name;
If I remove Name.Name from my report then it works fine. The only problem is it sorts by nameID I need it to sort by name. The query will sort by name.name with no problem.
Thank you.
SELECT Lots.NameID, [Preliminary Inventory Query].LatestDate, [Propagation and Potting Merge].PropQuantity, [Propagation and Potting Merge].ContainerID, Lots.[Lot Number], Name.Name
FROM Name INNER JOIN (Lots INNER JOIN ([Propagation and Potting Merge] INNER JOIN [Preliminary Inventory Query] ON ([Preliminary Inventory Query].[Lot Number] = [Propagation and Potting Merge].[Lot Number]) AND ([Propagation and Potting Merge].PropDate = [Preliminary Inventory Query].LatestDate)) ON Lots.[Lot Number] = [Preliminary Inventory Query].[Lot Number]) ON Name.NameID = Lots.NameID
ORDER BY Name.Name;
If I remove Name.Name from my report then it works fine. The only problem is it sorts by nameID I need it to sort by name. The query will sort by name.name with no problem.
Thank you.
For more info, follow: Things To Avoid...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.