Query works, report doesn't

[laxster]

I've built a query that executes flawlessly and displays all the information I need it to. However, when I want to display information from that query on a report, I get an error stating that "The specified field '[Ship To #].[Street Address Line 2]' could refer to more than one table listed in the FROM clause of your SQL statement.

I've searched around to see if I can find solutions, but nothing helpful.

I'm not sure if this helps, but here's the query's SQL:

SELECT [Generation table].[Ship to], [Ship To #].Name, [Ship To #].[Street Address Line 2], [Generation table].[Pallet Quantity], [Generation table].[Shipment #], Numbers.Field1, [Generation table].[Ship From], [Ship From].Name, [Ship From].[Street Address], [Ship From].[Street Address Line 2]
FROM Numbers, ([Ship To #] INNER JOIN [Generation table] ON [Ship To #].[Buyer #] = [Generation table].[Ship to]) INNER JOIN [Ship From] ON [Generation table].[Ship From] = [Ship From].[Buyer #]
WHERE (((Numbers.Field1)<=[Pallet Quantity]))
ORDER BY Numbers.Field1;

Thanks for looking into this!

 

[dkinley]

Try putting the source name (table/query) in front of the [Ship To #].[Street Address Line 2] reference for qualification.

For instance [tblTableName].[Ship To #].[Street Address Line 2]

-dK

 

[PNGBill]

The query sees the full table.field name and the report may only see the result ie Street Address Line 2 twice.
Can be better to have fields as ShipperAdd2 RecAdd2 but sql can also use [Ship From].[Street Address 2] as ShipperAdd2 and then the report should see ShipperAdd2.

 

[boblarson]

[Ship to #] IS the table name, I believe.

Also, there should not be any tables with # in the name. Bad naming can cause bad results.

You also have another Access Reserved Word used as a column name (NAME) which is REALLY bad to use as a field/object name. You need to put square brackets around that one as well.

 

[laxster]

I tried to make appropriate changes where I could (some information comes from other databases in the company which I can't change), but I still get the same error.

Here is the modified code:

SELECT [Generation table].[Ship to], TblShipTo.[Name], TblShipTo.[Street Address], TblShipTo.[Street Address Line 2], [Ship From].[Street Address Line 2], [Generation table].[Pallet Quantity], [Generation table].[Shipment #], Numbers.Field1, [Generation table].[Ship From], [Ship From].[Name], [Ship From].[Street Address], [Ship From].[Street Address Line 2]
FROM Numbers, (TblShipTo INNER JOIN [Generation table] ON TblShipTo.[Buyer #] = [Generation table].[Ship to]) INNER JOIN [Ship From] ON [Generation table].[Ship From] = [Ship From].[Buyer #]
WHERE (((Numbers.Field1)<=[Pallet Quantity]))
ORDER BY Numbers.Field1;

 

[boblarson]

Still missing the square brackets around NAME (see red).

SELECT [Generation table].[Ship to], TblShipTo.[Name], TblShipTo.[Street Address], TblShipTo.[Street Address Line 2], [Ship From].[Street Address Line 2], [Generation table].[Pallet Quantity], [Generation table].[Shipment #], Numbers.Field1, [Generation table].[Ship From], [Ship From].[Name], [Ship From].[Street Address], [Ship From].[Street Address Line 2]
FROM Numbers, (TblShipTo INNER JOIN [Generation table] ON TblShipTo.[Buyer #] = [Generation table].[Ship to]) INNER JOIN [Ship From] ON [Generation table].[Ship From] = [Ship From].[Buyer #]
WHERE (((Numbers.Field1)<=[Pallet Quantity]))
ORDER BY Numbers.Field1;

 

[laxster]

I modified that too (accidentally posted the wrong thing), and it just tells me that "The specified field 'TblShipTo.[Street Address Line 2]' could refer to more than one table listed in the FROM clause of your SQL statement.

 

[boblarson]

Well, you do have it twice (I think you meant to make it ShipTo):

SELECT [Generation table].[Ship to], TblShipTo.[Name], TblShipTo.[Street Address], TblShipTo.[Street Address Line 2], [Ship From].[Street Address Line 2], [Generation table].[Pallet Quantity], [Generation table].[Shipment #], Numbers.Field1, [Generation table].[Ship From], [Ship From].[Name], [Ship From].[Street Address], [Ship From].[Street Address Line 2]
FROM Numbers, (TblShipTo INNER JOIN [Generation table] ON TblShipTo.[Buyer #] = [Generation table].[Ship to]) INNER JOIN [Ship From] ON [Generation table].[Ship From] = [Ship From].[Buyer #]
WHERE (((Numbers.Field1)<=[Pallet Quantity]))
ORDER BY Numbers.Field1;

 

[laxster]

I still get the error. I am utterly lost as why the query runs perfectly but the report using the query does not.

 

[boblarson]

I still get the error. I am utterly lost as why the query runs perfectly but the report using the query does not.

Perhaps it is time to post a copy of the database here so we can take a look.

 

[binoj]

I know this is an old question but when I had a similar problem and searched I found this post. After scratching my head for some time I figured out the solution. In future if anyone is looking, my reply may help them.

In the sql the field name he has used [Name] is a reserved word in access. All he has to do is assign an alias TblShipTo.[Name] as CustName or something. Then the report should work.