query runs - report has error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Novice User - 21 Apr 2008 00:33 GMT

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?

Reply to this Message

Duane Hookom - 21 Apr 2008 00:57 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> Hi there,
>
[quoted text clipped - 11 lines]
> 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?

Reply to this Message

Novice User - 21 Apr 2008 01:04 GMT

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;

> Queries are much more accepting of duplicate field names. They will simply
> create an alias on the fly for presentation purposes like "Expr1" and
[quoted text clipped - 16 lines]
> > 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?

Reply to this Message

Duane Hookom - 21 Apr 2008 02:38 GMT

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,

Signature

Duane Hookom
Microsoft Access MVP

> 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
[quoted text clipped - 41 lines]
> > > 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?

Reply to this Message

Novice User - 21 Apr 2008 02:56 GMT

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,
[quoted text clipped - 47 lines]
> > > > 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?

Reply to this Message