SQL Help!!!

[doodle]

Windows xp, access 97
why diesn't crap work on saturdays?

I have a query that pulls from 4 tables. two of the tables have a field
named the same. "OnHand". I have stated in my wuery which one I want to
pull from. The query runs fine on it;s own, but when I run the report
that is based on this query, I get the following message:

The specified field 'qryAS400_UsageNew.OnHand' could refer to more than
one table listed in the FROM clause of your SQL statement.


thanks in advance,

-doodle

Here is my query:

SELECT DISTINCT qryAS400_UsageNew.PartNum,
qryAS400_UsageNew.PartDescrip, qryAS400_UsageNew.OnHand,
qryAS400_UsageNew.USAGE, IIf([sumofonhand] Is Null,0,[sumofonhand]) AS
ROH, IIf([sumofusage] Is Null,0,[sumofusage]) AS RU,
[qryAS400_UsageNew].[onhand]+[roh] AS TotalOnHand,
[qryAS400_UsageNew].[USAGE]+[ru] AS TotalUsage,
[totalonhand]-[totalusage] AS Var, qryAS400_UsageCores.CorePN,
qryAS400_UsageCores.OnHand, IIf([countofspindle type1] Is
Null,0,[countofspindle type1]) AS CAC
FROM ((qryAS400_UsageNew LEFT JOIN qryAS400_UsageRepairs ON
qryAS400_UsageNew.PartNum =
qryAS400_UsageRepairs.tblAS400_UsageRepairNum.PartNum) LEFT JOIN
qryAS400_UsageCores ON qryAS400_UsageNew.CorePN =
qryAS400_UsageCores.CorePN) LEFT JOIN qryAS400_UsageCoresOut ON
qryAS400_UsageNew.CorePN = qryAS400_UsageCoresOut.PartNum
WHERE (((qryAS400_UsageCores.OnHand)<>0));

 

[Granny Spitz via AccessMonster.com]

The specified field 'qryAS400_UsageNew.OnHand' could refer to more than
one table listed in the FROM clause of your SQL statement.

Try changing the qryAS400_UsageNew.OnHand column to an alias like
qryAS400_UsageNew.OnHand AS UNOH and use UNOH in your report.

 

[doodle]

works beautiful. thanks.

-doodle

 

[Granny Spitz via AccessMonster.com]

works beautiful. thanks.

You're welcome, doodle. Interesting name. <g>