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?