Comparing 2 fields on 2 different tables

[ericxmiller]

I am looking to do a fuel comparison query for a database that I put together. I have all of the purchase data in one table and I have all of the vehicle data in another table with 3 common fields joining the tables. I want to compare the amount of fuel purchased from Table 1 to the vehicle fuel capacity (not a shared field) in Table 2. I can't seem to get the query to work. I am pretty new to writing Access queries so I am sure this is an easy query to write. Does anyone know how I can get this to work?

[June7]

Show the attempted query SQL statement. Provide samples of source data and desired output. If you want to provide db, follow instructions at bottom of my post.

[ericxmiller]

Here is the attempted SQL statement:

SELECT [VAR Database].VehicleModelYear, [VAR Database].VehicleMake, [VAR Database].VehicleModel, [VAR Database].LicenseNumber, [VAR Database].[Driver Name], [VAR Database].DateofPurchase, [VAR Database].TimeofPurchase, [VAR Database].VendorName, [VAR Database].VendorState, [VAR Database].ProdCode, [VAR Database].PurchaseQuantity, [VAR Database].PurchaseAmount, [Fuel Tank Capacity].[Fuel Tank Capacity]
FROM [Fuel Tank Capacity] INNER JOIN [VAR Database] ON ([Fuel Tank Capacity].Model = [VAR Database].VehicleModel) AND ([Fuel Tank Capacity].Make = [VAR Database].VehicleMake) AND ([Fuel Tank Capacity].[Model Year] = [VAR Database].VehicleModelYear);
WHERE ((([VAR Database].PurchaseQuantity)>[Fuel Tank Capacity].Fuel Tank Capacity));

I am looking to take the fuel tank capacity of our fleet vehicles and see if employees are purchasing more gas than the capacity.

[June7]

Only thing I see wrong is lack of [] around Fuel Tank Capacity in the WHERE clause.

[ericxmiller]

It keeps telling me that characters are found after end of SQL statement. Not sure what characters they are talking about since what I posted is everything that is in the SQL view. Any ideas of what my WHERE clause should look like to eliminate this error?

[June7]

Just the [] around field name.

Don't use spaces and special characters/punctuation (underscore is exception) and the [] will not be mandatory.

[ericxmiller]

Now I am getting a data type mismatch in criteria expression. Here is what I have: WHERE ((([VAR Database].PurchaseQuantity)>[Fuel Tank Capacity].[Fuel Tank Capacity]));

Should work shouldn't it with the previous select and from clauses as in my previous post? At least I am getting a new error now.

[June7]

Verify that related fields used in the JOIN and WHERE are the same data type in tables.

[ericxmiller]

Ok it works now, thank you so much! The only problem now is that in the text data type it does not correctly compare the numbers. How do I convert the data type to number for with 2 decimal places without converting it to a full value number without the decimal places? I made a copy of the database but I keep losing the data I need for each decimal place when I convert. Is it possible to convert it without losing those values?

[June7]

You made a copy of database or did you mean table?

Why are you comparing a text type with a number type? Which fields are involved? Why aren't they the same datatype in the tables? Select Double as the FieldSize in the field properties section at the bottom of the table designer.