Error: Specified field could refer to more than one table

G

Guest

When opening my Query, (which used to display information correctly) I now
get "Specified field could refer to more than one table in the FROM clause of
your SQL statement. The Query then opens in the SQL View. I'm trying to
view the Query in Design View and it will not allow me. How can I change
from SQL View to Design View?
 
A

Allen Browne

The query contains several clauses, such as:
SELECT ...
FROM ...
WHERE ...
ORDER BY ...

Examine the FROM clause.
It contains multiple tables, JOINed together.
Each JOIN describes which field from one table matches which field in the
other, e.g.:
ON Orders.OrderID = OrderDetail.OrderID

In one of those ON expressions, the table name is missing, e.g. it just
says:
ON Orders.OrderID = OrderID

Typing the table name and dot in front of the field name will solve the
problem.
 
G

Guest

How can I switch this view from the SQL View to the Design View. This is
typically the way I work with the Query?
 
A

Allen Browne

Once you fix the SQL statement so that it makes sense, you will be able to
switch back to design view.

If the SQL statement is incomplete or invalid or ambiguous, Access
(understandably) can't show design view, because it can't make sense of it.
 
G

Guest

When I try to switch the views from SQL View to Design View - it does not
allow me to change the view - it displays only the SQL View.
 
J

John Spencer

The way I would try would be to look at all the fields that aren't
completely designated and add the table name to them.

If you can't figure it out, you might post the SQL text of your query.
Perhaps someone can suggest what you need to do. It would help if you
posted the table and associated field names.

Are you using any calculated fields? That may be where the problem lies.
 
G

Guest

The error message is "The specified field "[2005 Max
Mileage],MaxOfWeeklyMileage' could refer to more than one table listed in the
FROM clause of your SQL statement.

SELECT DISTINCTROW [98 Vehicle Info Combined For Form].ID, [98 Vehicle Info
Combined For Form].[Driver & Location], VEHICLES.SELLDATE, [98 Vehicle Info
Combined For Form].[Veh Description], IIf([98 Vehicle Info Combined For
Form]![Sum Of Cost]>0,[98 Vehicle Info Combined For Form]![Sum Of Cost],0) AS
98Gas, IIf([98 Vehicle Info Combined For Form]![98FuelPerMile]>0,[98 Vehicle
Info Combined For Form]![98FuelPerMile],0) AS 98FuelPerMile, IIf([98 Vehicle
Info Combined For Form]![Sum Of CostofService]>0,[98 Vehicle Info Combined
For Form]![Sum Of CostofService],0) AS 98Ser, IIf([98 Vehicle Info Combined
For Form]![98SerPerMile]>0,[98 Vehicle Info Combined For
Form]![98SerPerMile],0) AS 98SerPerMile, IIf([98 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[98 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 98MaxMiles, IIf([98 Vehicle Info Combined For
Form]![1998Miles]>0,[98 Vehicle Info Combined For Form]![1998Miles],0) AS
1998Miles, [98 Vehicle Info Combined For Form]![DateSold] AS 98DateSold,
IIf([99 Vehicle Info Combined For Form]![99Fuel Costs]>0,[99 Vehicle Info
Combined For Form]![99Fuel Costs],0) AS 99Gas, IIf([99 Vehicle Info Combined
For Form]![99FuelPerMile]>0,[99 Vehicle Info Combined For
Form]![99FuelPerMile],0) AS 99FuelPerMile, IIf([99 Vehicle Info Combined For
Form]![Sum Of CostofService]>0,[99 Vehicle Info Combined For Form]![Sum Of
CostofService],0) AS [Sum Of CostofService], IIf([99 Vehicle Info Combined
For Form]![99SerPerMile]>0,[99 Vehicle Info Combined For
Form]![99SerPerMile],0) AS 99SerPerMile, IIf([99 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[99 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 99Max, IIf([99 Vehicle Info Combined For
Form]![99Miles]>0,[99 Vehicle Info Combined For Form]![99Miles],0) AS
99Miles, [99 Vehicle Info Combined For Form]![DateSold] AS 99DateSold,
IIf([2000 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2000 Vehicle Info
Combined For Form]![Sum Of Cost],0) AS 2000Gas, IIf([2000 Vehicle Info
Combined For Form]![2000FuelPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000FuelPerMile],0) AS 2000GasPerMile, IIf([2000 Vehicle Info Combined
For Form]![Sum Of CostofService]>0,[2000 Vehicle Info Combined For Form]![Sum
Of CostofService],0) AS 2000Ser, IIf([2000 Vehicle Info Combined For
Form]![2000SerPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000SerPerMile],0) AS 2000SerPerMile, [2000 Vehicle Info Combined For
Form].[Max Of WeeklyMileage], [2000 Vehicle Info Combined For
Form].[2000MilesDrvn], [2000 Vehicle Info Combined For Form].DateSold,
IIf([2001 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2001 Vehicle Info
Combined For Form]![Sum Of Cost],0) AS 2001Gas, IIf([2001 Vehicle Info
Combined For Form]![2001FuelPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001FuelPerMile],0) AS 2001GasPerMile, IIf([2001 Vehicle Info Combined
For Form]![Sum Of CostofService]>0,[2001 Vehicle Info Combined For Form]![Sum
of CostofService],0) AS 2001Ser, IIf([2001 Vehicle Info Combined For
Form]![2001SerPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001SerPerMile],0) AS 2001SerPerMile, [2001 Vehicle Info Combined For
Form].[2001MilesDrvn], [2001 Vehicle Info Combined For Form]![2001Max] AS
2001MaxMiles, [2001 Vehicle Info Combined For Form].[2001Sold], IIf([2002
Vehicle Info Combined For Form]![Sum Of Cost]>0,[2002 Vehicle Info Combined
For Form]![Sum Of Cost],0) AS 2002Gas, IIf([2002 Vehicle Info Combined For
Form]![2002FuelPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002FuelPerMile],0) AS 2002GasPerMile, IIf([2002 Vehicle Info Combined
For Form]![Sum Of CostofService]>0,[2002 Vehicle Info Combined For Form]![Sum
of CostofService],0) AS 2002Ser, IIf([2002 Vehicle Info Combined For
Form]![2002SerPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002SerPerMile],0) AS 2002SerPerMile, [2002 Vehicle Info Combined For
Form].[2002MilesDrvn], [2002 Vehicle Info Combined For Form]![2002Max] AS
2002MaxMiles, [2002 Vehicle Info Combined For Form].[2002Sold], IIf([2003
Vehicle Info Combined For Form]![2003Gas]>0,[2003 Vehicle Info Combined For
Form]![2003Gas],0) AS 2003Gas, [2003 Vehicle Info Combined For
Form].[2003FuelPerMile], IIf([2003 Vehicle Info Combined For
Form]![2003SerCosts]>0,[2003 Vehicle Info Combined For
Form]![2003SerCosts],0) AS 2003Ser, [2003 Vehicle Info Combined For
Form].[2003SerPerMile], [2003 Vehicle Info Combined For Form].[2003Max],
[2003 Vehicle Info Combined For Form].[2003MilesDrvn], [2003 Vehicle Info
Combined For Form].[2003Sold], IIf([2004 Vehicle Info Combined For
Form]![2004Gas]>0,[2004 Vehicle Info Combined For Form]![2004Gas],0) AS
2004Gas, [2004 Vehicle Info Combined For Form].[2004FuelPerMile], IIf([2004
Vehicle Info Combined For Form]![2004SerCosts]>0,[2004 Vehicle Info Combined
For Form]![2004SerCosts],0) AS 2004Ser, [2004 Vehicle Info Combined For
Form].[2004SerPerMile], [2004 Vehicle Info Combined For Form].[2004Max],
[2004 Vehicle Info Combined For Form].[2004MilesDrvn], [2004 Vehicle Info
Combined For Form].[2004Sold], IIf([2005 Vehicle Info Combined For
Form]![2005Gas]>0,[2005 Vehicle Info Combined For Form]![2005Gas],0) AS
2005Gas, [2005 Vehicle Info Combined For Form].[2005FuelPerMile], IIf([2005
Vehicle Info Combined For Form]![2005Ser]>0,[2005 Vehicle Info Combined For
Form]![2005Ser],0) AS 2005Ser, [2005 Vehicle Info Combined For
Form].[2005SerPerMile], [2005 Vehicle Info Combined For Form].[2005Max],
[2005 Vehicle Info Combined For Form].[2005MilesDrvn], [2005 Vehicle Info
Combined For Form].[2005Sold], IIf([2006 Vehicle Info Combined For
Form]![2006Gas]>0,[2006 Vehicle Info Combined For Form]![2006Gas],0) AS
2006Gas, [2006 Vehicle Info Combined For Form].[2006FuelPerMile], IIf([2006
Vehicle Info Combined For Form]![2006Ser]>0,[2006 Vehicle Info Combined For
Form]![2006Ser],0) AS 2006Ser, [2006 Vehicle Info Combined For
Form].[2006SerPerMile], [2006 Vehicle Info Combined For Form].[=2006Max],
[2006 Vehicle Info Combined For Form].[2006MilesDrvn], [2006 Vehicle Info
Combined For Form].[2006Sold], [VEHICLES Total Miles]![Vehicle Total Miles]
AS TtlMiles,
IIf([98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas]>0,[98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas],0)
AS TltFuel, IIf([TtlMiles]>0,[TltFuel]/[TtlMiles],0) AS TtlFuelPerMile,
IIf([98Ser]+[Sum Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser]>0,[98Ser]+[Sum
Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser],0)
AS TtlSer, IIf([TtlMiles]>0,[TtlSer]/[TtlMiles],0) AS TtlSerMile,
IIf([TltFuel]+[TtlSer]>0,[TltFuel]+[TtlSer],0) AS [TtlGas&Ser],
IIf([TtlMiles]>0,[TtlGas&Ser]/[TtlMiles],0) AS TtlCostPerMile
FROM ([2005 Vehicle Info Combined For Form] RIGHT JOIN ([2004 Vehicle Info
Combined For Form] RIGHT JOIN ((([2001 Vehicle Info Combined For Form] RIGHT
JOIN ((([99 Vehicle Info Combined For Form] RIGHT JOIN ([98 Vehicle Info
Combined For Form] RIGHT JOIN VEHICLES ON [98 Vehicle Info Combined For
Form].ID = VEHICLES.ID) ON [99 Vehicle Info Combined For Form].ID =
VEHICLES.ID) LEFT JOIN [2000 Vehicle Info Combined For Form] ON VEHICLES.ID =
[2000 Vehicle Info Combined For Form].ID) LEFT JOIN [VEHICLES Total Miles] ON
VEHICLES.ID = [VEHICLES Total Miles].ID) ON [2001 Vehicle Info Combined For
Form].ID = VEHICLES.ID) LEFT JOIN [2002 Vehicle Info Combined For Form] ON
VEHICLES.ID = [2002 Vehicle Info Combined For Form].ID) LEFT JOIN [2003
Vehicle Info Combined For Form] ON VEHICLES.ID = [2003 Vehicle Info Combined
For Form].ID) ON [2004 Vehicle Info Combined For Form].ID = VEHICLES.ID) ON
[2005 Vehicle Info Combined For Form].ID = VEHICLES.ID) LEFT JOIN [2006
Vehicle Info Combined For Form] ON VEHICLES.ID = [2006 Vehicle Info Combined
For Form].ID;
 
J

John Spencer

Since I don't see that field MaxofWeeklyMileage ( I so see Max of
WeeklyMileage) in SELECT Clause my guess is that the reference exists
somewhere else. Perhaps in queries that you are using to build this query
there is a conflict.

Things to try:
Run the query and select Remove Filter/sort from the menu and then save the
query.

Copy the SQL text into a blank query and try switching into design view

Replace ! with . in your calculated expressions and see if that works.

Perhaps you can try removing parts of the query until you find what is
causing the problem.
For instance, try removing all the IIF clauses - one at a time. Perhaps
that will point you the problem area.

Try replacing the entire Select clause with just SELECT *

SELECT *
FROM ...


RH said:
The error message is "The specified field "[2005 Max
Mileage],MaxOfWeeklyMileage' could refer to more than one table listed in
the
FROM clause of your SQL statement.

SELECT DISTINCTROW [98 Vehicle Info Combined For Form].ID, [98 Vehicle
Info
Combined For Form].[Driver & Location], VEHICLES.SELLDATE, [98 Vehicle
Info
Combined For Form].[Veh Description], IIf([98 Vehicle Info Combined For
Form]![Sum Of Cost]>0,[98 Vehicle Info Combined For Form]![Sum Of Cost],0)
AS
98Gas, IIf([98 Vehicle Info Combined For Form]![98FuelPerMile]>0,[98
Vehicle
Info Combined For Form]![98FuelPerMile],0) AS 98FuelPerMile, IIf([98
Vehicle
Info Combined For Form]![Sum Of CostofService]>0,[98 Vehicle Info Combined
For Form]![Sum Of CostofService],0) AS 98Ser, IIf([98 Vehicle Info
Combined
For Form]![98SerPerMile]>0,[98 Vehicle Info Combined For
Form]![98SerPerMile],0) AS 98SerPerMile, IIf([98 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[98 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 98MaxMiles, IIf([98 Vehicle Info Combined For
Form]![1998Miles]>0,[98 Vehicle Info Combined For Form]![1998Miles],0) AS
1998Miles, [98 Vehicle Info Combined For Form]![DateSold] AS 98DateSold,
IIf([99 Vehicle Info Combined For Form]![99Fuel Costs]>0,[99 Vehicle Info
Combined For Form]![99Fuel Costs],0) AS 99Gas, IIf([99 Vehicle Info
Combined
For Form]![99FuelPerMile]>0,[99 Vehicle Info Combined For
Form]![99FuelPerMile],0) AS 99FuelPerMile, IIf([99 Vehicle Info Combined
For
Form]![Sum Of CostofService]>0,[99 Vehicle Info Combined For Form]![Sum Of
CostofService],0) AS [Sum Of CostofService], IIf([99 Vehicle Info Combined
For Form]![99SerPerMile]>0,[99 Vehicle Info Combined For
Form]![99SerPerMile],0) AS 99SerPerMile, IIf([99 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[99 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 99Max, IIf([99 Vehicle Info Combined For
Form]![99Miles]>0,[99 Vehicle Info Combined For Form]![99Miles],0) AS
99Miles, [99 Vehicle Info Combined For Form]![DateSold] AS 99DateSold,
IIf([2000 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2000 Vehicle
Info
Combined For Form]![Sum Of Cost],0) AS 2000Gas, IIf([2000 Vehicle Info
Combined For Form]![2000FuelPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000FuelPerMile],0) AS 2000GasPerMile, IIf([2000 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2000 Vehicle Info Combined For
Form]![Sum
Of CostofService],0) AS 2000Ser, IIf([2000 Vehicle Info Combined For
Form]![2000SerPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000SerPerMile],0) AS 2000SerPerMile, [2000 Vehicle Info Combined
For
Form].[Max Of WeeklyMileage], [2000 Vehicle Info Combined For
Form].[2000MilesDrvn], [2000 Vehicle Info Combined For Form].DateSold,
IIf([2001 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2001 Vehicle
Info
Combined For Form]![Sum Of Cost],0) AS 2001Gas, IIf([2001 Vehicle Info
Combined For Form]![2001FuelPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001FuelPerMile],0) AS 2001GasPerMile, IIf([2001 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2001 Vehicle Info Combined For
Form]![Sum
of CostofService],0) AS 2001Ser, IIf([2001 Vehicle Info Combined For
Form]![2001SerPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001SerPerMile],0) AS 2001SerPerMile, [2001 Vehicle Info Combined
For
Form].[2001MilesDrvn], [2001 Vehicle Info Combined For Form]![2001Max] AS
2001MaxMiles, [2001 Vehicle Info Combined For Form].[2001Sold], IIf([2002
Vehicle Info Combined For Form]![Sum Of Cost]>0,[2002 Vehicle Info
Combined
For Form]![Sum Of Cost],0) AS 2002Gas, IIf([2002 Vehicle Info Combined For
Form]![2002FuelPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002FuelPerMile],0) AS 2002GasPerMile, IIf([2002 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2002 Vehicle Info Combined For
Form]![Sum
of CostofService],0) AS 2002Ser, IIf([2002 Vehicle Info Combined For
Form]![2002SerPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002SerPerMile],0) AS 2002SerPerMile, [2002 Vehicle Info Combined
For
Form].[2002MilesDrvn], [2002 Vehicle Info Combined For Form]![2002Max] AS
2002MaxMiles, [2002 Vehicle Info Combined For Form].[2002Sold], IIf([2003
Vehicle Info Combined For Form]![2003Gas]>0,[2003 Vehicle Info Combined
For
Form]![2003Gas],0) AS 2003Gas, [2003 Vehicle Info Combined For
Form].[2003FuelPerMile], IIf([2003 Vehicle Info Combined For
Form]![2003SerCosts]>0,[2003 Vehicle Info Combined For
Form]![2003SerCosts],0) AS 2003Ser, [2003 Vehicle Info Combined For
Form].[2003SerPerMile], [2003 Vehicle Info Combined For Form].[2003Max],
[2003 Vehicle Info Combined For Form].[2003MilesDrvn], [2003 Vehicle Info
Combined For Form].[2003Sold], IIf([2004 Vehicle Info Combined For
Form]![2004Gas]>0,[2004 Vehicle Info Combined For Form]![2004Gas],0) AS
2004Gas, [2004 Vehicle Info Combined For Form].[2004FuelPerMile],
IIf([2004
Vehicle Info Combined For Form]![2004SerCosts]>0,[2004 Vehicle Info
Combined
For Form]![2004SerCosts],0) AS 2004Ser, [2004 Vehicle Info Combined For
Form].[2004SerPerMile], [2004 Vehicle Info Combined For Form].[2004Max],
[2004 Vehicle Info Combined For Form].[2004MilesDrvn], [2004 Vehicle Info
Combined For Form].[2004Sold], IIf([2005 Vehicle Info Combined For
Form]![2005Gas]>0,[2005 Vehicle Info Combined For Form]![2005Gas],0) AS
2005Gas, [2005 Vehicle Info Combined For Form].[2005FuelPerMile],
IIf([2005
Vehicle Info Combined For Form]![2005Ser]>0,[2005 Vehicle Info Combined
For
Form]![2005Ser],0) AS 2005Ser, [2005 Vehicle Info Combined For
Form].[2005SerPerMile], [2005 Vehicle Info Combined For Form].[2005Max],
[2005 Vehicle Info Combined For Form].[2005MilesDrvn], [2005 Vehicle Info
Combined For Form].[2005Sold], IIf([2006 Vehicle Info Combined For
Form]![2006Gas]>0,[2006 Vehicle Info Combined For Form]![2006Gas],0) AS
2006Gas, [2006 Vehicle Info Combined For Form].[2006FuelPerMile],
IIf([2006
Vehicle Info Combined For Form]![2006Ser]>0,[2006 Vehicle Info Combined
For
Form]![2006Ser],0) AS 2006Ser, [2006 Vehicle Info Combined For
Form].[2006SerPerMile], [2006 Vehicle Info Combined For Form].[=2006Max],
[2006 Vehicle Info Combined For Form].[2006MilesDrvn], [2006 Vehicle Info
Combined For Form].[2006Sold], [VEHICLES Total Miles]![Vehicle Total
Miles]
AS TtlMiles,
IIf([98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas]>0,[98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas],0)
AS TltFuel, IIf([TtlMiles]>0,[TltFuel]/[TtlMiles],0) AS TtlFuelPerMile,
IIf([98Ser]+[Sum Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser]>0,[98Ser]+[Sum
Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser],0)
AS TtlSer, IIf([TtlMiles]>0,[TtlSer]/[TtlMiles],0) AS TtlSerMile,
IIf([TltFuel]+[TtlSer]>0,[TltFuel]+[TtlSer],0) AS [TtlGas&Ser],
IIf([TtlMiles]>0,[TtlGas&Ser]/[TtlMiles],0) AS TtlCostPerMile
FROM ([2005 Vehicle Info Combined For Form] RIGHT JOIN ([2004 Vehicle Info
Combined For Form] RIGHT JOIN ((([2001 Vehicle Info Combined For Form]
RIGHT
JOIN ((([99 Vehicle Info Combined For Form] RIGHT JOIN ([98 Vehicle Info
Combined For Form] RIGHT JOIN VEHICLES ON [98 Vehicle Info Combined For
Form].ID = VEHICLES.ID) ON [99 Vehicle Info Combined For Form].ID =
VEHICLES.ID) LEFT JOIN [2000 Vehicle Info Combined For Form] ON
VEHICLES.ID =
[2000 Vehicle Info Combined For Form].ID) LEFT JOIN [VEHICLES Total Miles]
ON
VEHICLES.ID = [VEHICLES Total Miles].ID) ON [2001 Vehicle Info Combined
For
Form].ID = VEHICLES.ID) LEFT JOIN [2002 Vehicle Info Combined For Form] ON
VEHICLES.ID = [2002 Vehicle Info Combined For Form].ID) LEFT JOIN [2003
Vehicle Info Combined For Form] ON VEHICLES.ID = [2003 Vehicle Info
Combined
For Form].ID) ON [2004 Vehicle Info Combined For Form].ID = VEHICLES.ID)
ON
[2005 Vehicle Info Combined For Form].ID = VEHICLES.ID) LEFT JOIN [2006
Vehicle Info Combined For Form] ON VEHICLES.ID = [2006 Vehicle Info
Combined
For Form].ID;


John Spencer said:
The way I would try would be to look at all the fields that aren't
completely designated and add the table name to them.

If you can't figure it out, you might post the SQL text of your query.
Perhaps someone can suggest what you need to do. It would help if you
posted the table and associated field names.

Are you using any calculated fields? That may be where the problem lies.
Click to expand...
Click to expand...
 
G

Guest

Thanks to all who helped with this. I've found the error.

John Spencer said:
Since I don't see that field MaxofWeeklyMileage ( I so see Max of
WeeklyMileage) in SELECT Clause my guess is that the reference exists
somewhere else. Perhaps in queries that you are using to build this query
there is a conflict.

Things to try:
Run the query and select Remove Filter/sort from the menu and then save the
query.

Copy the SQL text into a blank query and try switching into design view

Replace ! with . in your calculated expressions and see if that works.

Perhaps you can try removing parts of the query until you find what is
causing the problem.
For instance, try removing all the IIF clauses - one at a time. Perhaps
that will point you the problem area.

Try replacing the entire Select clause with just SELECT *

SELECT *
FROM ...


RH said:
The error message is "The specified field "[2005 Max
Mileage],MaxOfWeeklyMileage' could refer to more than one table listed in
the
FROM clause of your SQL statement.

SELECT DISTINCTROW [98 Vehicle Info Combined For Form].ID, [98 Vehicle
Info
Combined For Form].[Driver & Location], VEHICLES.SELLDATE, [98 Vehicle
Info
Combined For Form].[Veh Description], IIf([98 Vehicle Info Combined For
Form]![Sum Of Cost]>0,[98 Vehicle Info Combined For Form]![Sum Of Cost],0)
AS
98Gas, IIf([98 Vehicle Info Combined For Form]![98FuelPerMile]>0,[98
Vehicle
Info Combined For Form]![98FuelPerMile],0) AS 98FuelPerMile, IIf([98
Vehicle
Info Combined For Form]![Sum Of CostofService]>0,[98 Vehicle Info Combined
For Form]![Sum Of CostofService],0) AS 98Ser, IIf([98 Vehicle Info
Combined
For Form]![98SerPerMile]>0,[98 Vehicle Info Combined For
Form]![98SerPerMile],0) AS 98SerPerMile, IIf([98 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[98 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 98MaxMiles, IIf([98 Vehicle Info Combined For
Form]![1998Miles]>0,[98 Vehicle Info Combined For Form]![1998Miles],0) AS
1998Miles, [98 Vehicle Info Combined For Form]![DateSold] AS 98DateSold,
IIf([99 Vehicle Info Combined For Form]![99Fuel Costs]>0,[99 Vehicle Info
Combined For Form]![99Fuel Costs],0) AS 99Gas, IIf([99 Vehicle Info
Combined
For Form]![99FuelPerMile]>0,[99 Vehicle Info Combined For
Form]![99FuelPerMile],0) AS 99FuelPerMile, IIf([99 Vehicle Info Combined
For
Form]![Sum Of CostofService]>0,[99 Vehicle Info Combined For Form]![Sum Of
CostofService],0) AS [Sum Of CostofService], IIf([99 Vehicle Info Combined
For Form]![99SerPerMile]>0,[99 Vehicle Info Combined For
Form]![99SerPerMile],0) AS 99SerPerMile, IIf([99 Vehicle Info Combined For
Form]![Max Of WeeklyMileage]>0,[99 Vehicle Info Combined For Form]![Max Of
WeeklyMileage],0) AS 99Max, IIf([99 Vehicle Info Combined For
Form]![99Miles]>0,[99 Vehicle Info Combined For Form]![99Miles],0) AS
99Miles, [99 Vehicle Info Combined For Form]![DateSold] AS 99DateSold,
IIf([2000 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2000 Vehicle
Info
Combined For Form]![Sum Of Cost],0) AS 2000Gas, IIf([2000 Vehicle Info
Combined For Form]![2000FuelPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000FuelPerMile],0) AS 2000GasPerMile, IIf([2000 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2000 Vehicle Info Combined For
Form]![Sum
Of CostofService],0) AS 2000Ser, IIf([2000 Vehicle Info Combined For
Form]![2000SerPerMile]>0,[2000 Vehicle Info Combined For
Form]![2000SerPerMile],0) AS 2000SerPerMile, [2000 Vehicle Info Combined
For
Form].[Max Of WeeklyMileage], [2000 Vehicle Info Combined For
Form].[2000MilesDrvn], [2000 Vehicle Info Combined For Form].DateSold,
IIf([2001 Vehicle Info Combined For Form]![Sum Of Cost]>0,[2001 Vehicle
Info
Combined For Form]![Sum Of Cost],0) AS 2001Gas, IIf([2001 Vehicle Info
Combined For Form]![2001FuelPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001FuelPerMile],0) AS 2001GasPerMile, IIf([2001 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2001 Vehicle Info Combined For
Form]![Sum
of CostofService],0) AS 2001Ser, IIf([2001 Vehicle Info Combined For
Form]![2001SerPerMile]>0,[2001 Vehicle Info Combined For
Form]![2001SerPerMile],0) AS 2001SerPerMile, [2001 Vehicle Info Combined
For
Form].[2001MilesDrvn], [2001 Vehicle Info Combined For Form]![2001Max] AS
2001MaxMiles, [2001 Vehicle Info Combined For Form].[2001Sold], IIf([2002
Vehicle Info Combined For Form]![Sum Of Cost]>0,[2002 Vehicle Info
Combined
For Form]![Sum Of Cost],0) AS 2002Gas, IIf([2002 Vehicle Info Combined For
Form]![2002FuelPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002FuelPerMile],0) AS 2002GasPerMile, IIf([2002 Vehicle Info
Combined
For Form]![Sum Of CostofService]>0,[2002 Vehicle Info Combined For
Form]![Sum
of CostofService],0) AS 2002Ser, IIf([2002 Vehicle Info Combined For
Form]![2002SerPerMile]>0,[2002 Vehicle Info Combined For
Form]![2002SerPerMile],0) AS 2002SerPerMile, [2002 Vehicle Info Combined
For
Form].[2002MilesDrvn], [2002 Vehicle Info Combined For Form]![2002Max] AS
2002MaxMiles, [2002 Vehicle Info Combined For Form].[2002Sold], IIf([2003
Vehicle Info Combined For Form]![2003Gas]>0,[2003 Vehicle Info Combined
For
Form]![2003Gas],0) AS 2003Gas, [2003 Vehicle Info Combined For
Form].[2003FuelPerMile], IIf([2003 Vehicle Info Combined For
Form]![2003SerCosts]>0,[2003 Vehicle Info Combined For
Form]![2003SerCosts],0) AS 2003Ser, [2003 Vehicle Info Combined For
Form].[2003SerPerMile], [2003 Vehicle Info Combined For Form].[2003Max],
[2003 Vehicle Info Combined For Form].[2003MilesDrvn], [2003 Vehicle Info
Combined For Form].[2003Sold], IIf([2004 Vehicle Info Combined For
Form]![2004Gas]>0,[2004 Vehicle Info Combined For Form]![2004Gas],0) AS
2004Gas, [2004 Vehicle Info Combined For Form].[2004FuelPerMile],
IIf([2004
Vehicle Info Combined For Form]![2004SerCosts]>0,[2004 Vehicle Info
Combined
For Form]![2004SerCosts],0) AS 2004Ser, [2004 Vehicle Info Combined For
Form].[2004SerPerMile], [2004 Vehicle Info Combined For Form].[2004Max],
[2004 Vehicle Info Combined For Form].[2004MilesDrvn], [2004 Vehicle Info
Combined For Form].[2004Sold], IIf([2005 Vehicle Info Combined For
Form]![2005Gas]>0,[2005 Vehicle Info Combined For Form]![2005Gas],0) AS
2005Gas, [2005 Vehicle Info Combined For Form].[2005FuelPerMile],
IIf([2005
Vehicle Info Combined For Form]![2005Ser]>0,[2005 Vehicle Info Combined
For
Form]![2005Ser],0) AS 2005Ser, [2005 Vehicle Info Combined For
Form].[2005SerPerMile], [2005 Vehicle Info Combined For Form].[2005Max],
[2005 Vehicle Info Combined For Form].[2005MilesDrvn], [2005 Vehicle Info
Combined For Form].[2005Sold], IIf([2006 Vehicle Info Combined For
Form]![2006Gas]>0,[2006 Vehicle Info Combined For Form]![2006Gas],0) AS
2006Gas, [2006 Vehicle Info Combined For Form].[2006FuelPerMile],
IIf([2006
Vehicle Info Combined For Form]![2006Ser]>0,[2006 Vehicle Info Combined
For
Form]![2006Ser],0) AS 2006Ser, [2006 Vehicle Info Combined For
Form].[2006SerPerMile], [2006 Vehicle Info Combined For Form].[=2006Max],
[2006 Vehicle Info Combined For Form].[2006MilesDrvn], [2006 Vehicle Info
Combined For Form].[2006Sold], [VEHICLES Total Miles]![Vehicle Total
Miles]
AS TtlMiles,
IIf([98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas]>0,[98Gas]+[99Gas]+[2000Gas]+[2001Gas]+[2002Gas]+[2003Gas]+[2004Gas]+[2005Gas]+[2006Gas],0)
AS TltFuel, IIf([TtlMiles]>0,[TltFuel]/[TtlMiles],0) AS TtlFuelPerMile,
IIf([98Ser]+[Sum Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser]>0,[98Ser]+[Sum
Of
CostofService]+[2000Ser]+[2001Ser]+[2002Ser]+[2003Ser]+[2004Ser]+[2005Ser]+[2006Ser],0)
AS TtlSer, IIf([TtlMiles]>0,[TtlSer]/[TtlMiles],0) AS TtlSerMile,
IIf([TltFuel]+[TtlSer]>0,[TltFuel]+[TtlSer],0) AS [TtlGas&Ser],
IIf([TtlMiles]>0,[TtlGas&Ser]/[TtlMiles],0) AS TtlCostPerMile
FROM ([2005 Vehicle Info Combined For Form] RIGHT JOIN ([2004 Vehicle Info
Combined For Form] RIGHT JOIN ((([2001 Vehicle Info Combined For Form]
RIGHT
JOIN ((([99 Vehicle Info Combined For Form] RIGHT JOIN ([98 Vehicle Info
Combined For Form] RIGHT JOIN VEHICLES ON [98 Vehicle Info Combined For
Form].ID = VEHICLES.ID) ON [99 Vehicle Info Combined For Form].ID =
VEHICLES.ID) LEFT JOIN [2000 Vehicle Info Combined For Form] ON
VEHICLES.ID =
[2000 Vehicle Info Combined For Form].ID) LEFT JOIN [VEHICLES Total Miles]
ON
VEHICLES.ID = [VEHICLES Total Miles].ID) ON [2001 Vehicle Info Combined
For
Form].ID = VEHICLES.ID) LEFT JOIN [2002 Vehicle Info Combined For Form] ON
VEHICLES.ID = [2002 Vehicle Info Combined For Form].ID) LEFT JOIN [2003
Vehicle Info Combined For Form] ON VEHICLES.ID = [2003 Vehicle Info
Combined
For Form].ID) ON [2004 Vehicle Info Combined For Form].ID = VEHICLES.ID)
ON
[2005 Vehicle Info Combined For Form].ID = VEHICLES.ID) LEFT JOIN [2006
Vehicle Info Combined For Form] ON VEHICLES.ID = [2006 Vehicle Info
Combined
For Form].ID;


John Spencer said:
The way I would try would be to look at all the fields that aren't
completely designated and add the table name to them.

If you can't figure it out, you might post the SQL text of your query.
Perhaps someone can suggest what you need to do. It would help if you
posted the table and associated field names.

Are you using any calculated fields? That may be where the problem lies.
When opening my Query, (which used to display information correctly) I
now
get "Specified field could refer to more than one table in the FROM
clause
of
your SQL statement. The Query then opens in the SQL View. I'm trying
to
view the Query in Design View and it will not allow me. How can I
change
from SQL View to Design View?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error: Specified field refers to more than one table 6
specified field could refer to more than one table 6
specified field error 9
"the specified field could refer to more than one table" error 1
Feild could refer to more than one table? 2
Referring to fields in a subquery 1
The specified field could refer to more than one table listed in theFROM clause of your SQL statemen 5
Wicked error- could refer to more than one table in the FROM 1

Top