query runs - report has error

[Novice User]

Hi there,

Thank you in advance for anyone that can point me in the right direction on
this. I have a query that combines multiple tables and queries. Some of the
underlying queries have the same tables and use the same field. In the query
that combines these, it runs fine. However, when I create a report from this
query I get, the specified field "fieldname" could refer to more than one
table in the from clause of the SQL statement. I'm not understanding why I
would get this message when the query runs fine independently. In fact I
copied this query from one that works fine with a report. I copied the query,
changed the criteria and then copied the report that works and changed what I
needed there (which was only the field that it sorted and grouped on). That
was all I changed, the field that the report grouped by and the criteria the
query filted by. Everything else is exactly the same but one report runs
fine and the other gives me the error message. Any ideas on how to fix this?

 

[Duane Hookom]

Queries are much more accepting of duplicate field names. They will simply
create an alias on the fly for presentation purposes like "Expr1" and
"Expr2". Just look through your query and remove the duplicate field names.
You may need to get rid of the "*" in the design view.

 

[Novice User]

Thank you for your quick reply. I have looked at the sql on this but I'm not
seeing the problem. Here is what it says, it is the JCJob.ID that it says it
has a problem with.

SELECT tbl1.ID, JCJob.ID, JCManager.ManagerName, JCJob.Description,
JCJob.Price, qrytotals.SumOfChargeTotal, Sum(qrytotlabor.ChargeQty) AS
SumOfChargeQty1, JCJob.UserDefined1, qrylabcalc.rrate, qrytotals.rmatrate,
JCJob.EstJobEndDate, JCJob.UserDefined2, ARCustomer.Name
FROM (((((ARCustomer RIGHT JOIN JCJob ON ARCustomer.SKARCustomer =
JCJob.FKARCustomer) LEFT JOIN JCManager ON JCJob.ManagerNumber =
JCManager.SKJCManager) LEFT JOIN qrytotals ON JCJob.ID = qrytotals.ID) LEFT
JOIN qrylabcalc ON JCJob.ID = qrylabcalc.ID) LEFT JOIN qrytotlabor ON
JCJob.ID = qrytotlabor.JCJob.ID) LEFT JOIN JCPhase AS tbl1 ON JCJob.SKJCJob =
tbl1.FKJCJob
GROUP BY tbl1.ID, JCJob.ID, JCManager.ManagerName, JCJob.Description,
JCJob.Price, qrytotals.SumOfChargeTotal, JCJob.UserDefined1,
qrylabcalc.rrate, qrytotals.rmatrate, JCJob.EstJobEndDate,
JCJob.UserDefined2, ARCustomer.Name
HAVING (((tbl1.ID)<>"OTHER") AND ((JCJob.ID)>"S1000" And (JCJob.ID)<>"SHOP")
AND ((JCJob.Price)>0) AND ((JCJob.UserDefined1)="TM")) OR
(((tbl1.ID)<>"OTHER") AND ((JCJob.ID)>"S1000" And (JCJob.ID)<>"SHOP") AND
((JCJob.UserDefined2)="CC")) OR (((JCJob.ID)>"S1000" And (JCJob.ID)<>"SHOP")
AND ((JCJob.UserDefined1)="NB"))
ORDER BY tbl1.ID, JCJob.ID;

 

[Duane Hookom]

You are creating two columns in your query with the name "ID".
SELECT tbl1.ID, JCJob.ID, JCManager.ManagerName, JCJob.Description,
I would change this to something like:
SELECT tbl1.ID as tbl1ID, JCJob.ID as JCJobID, JCManager.ManagerName,
JCJob.Description,

 

[Novice User]

That did the trick - you're awesome, thanks

You are creating two columns in your query with the name "ID".
SELECT tbl1.ID, JCJob.ID, JCManager.ManagerName, JCJob.Description,
I would change this to something like:
SELECT tbl1.ID as tbl1ID, JCJob.ID as JCJobID, JCManager.ManagerName,
JCJob.Description,