Query Problems!!!!

[eXtacy]

hi

Please can someone help me....

i have created a query and evertime i try to run it, i am getting a Type Mismatch error....

i have four tables containing the folowing data:

Company_Details: company_ID, Company_Name, Contact_Name, Address, Discount (in percent).

Car_details: Registration_Number, Tyre_type, Company ID.

Price: Type_Type, Price_ex_vat, Manufacturers_code.

Transaction: Invioce_Number, Date, Registration_Number, Number_of_Types_fitted.

what i am trying to do is to create a query which would display the details of all transactions together with the price (excluding vat), the subtotal (price_ex_vat * number_of_tyres_fitted), price after discount (subtotal - discount)(discount from the company_details table), and finally the total price includin VAT.

i have manage to create this query so far....but as soon as i display the company details table to calculate the discount i get the error...

SELECT DISTINCT Transaction.Invoice_Number, Transaction.Number_of_Tyres_Fitted, Price.Price_Ex_VAT, [Number_of_Tyres_Fitted]*[Price_Ex_VAT] AS Subtotal, [Subtotal]*1.175 AS Total
FROM Price INNER JOIN (Car_Details INNER JOIN [Transaction] ON Car_Details.Registration_Number = Transaction.Registration_Number) ON Price.Tyre_Type = Car_Details.Tyre_Type;

the above query is working fine....its just the discount section that i cant get to work.

please can someone help me out here.....
thanks
kay..


------
eXtacy

 

[eXtacy]

this is the sql when im getting the type mismatch error:

SELECT DISTINCT Transaction.Invoice_Number, Transaction.Number_of_Tyres_Fitted, Price.Price_Ex_VAT, [Number_of_Tyres_Fitted]*[Price_Ex_VAT] AS Subtotal, [Subtotal]*1.175 AS Total
FROM Company_Details INNER JOIN (Price INNER JOIN (Car_Details INNER JOIN [Transaction] ON Car_Details.Registration_Number = Transaction.Registration_Number) ON Price.Tyre_Type = Car_Details.Tyre_Type) ON Company_Details.Company_ID = Car_Details.Company_ID;


------
eXtacy

 

[lespaul]

Is the field Company ID in Company_Details the same data type as Company ID in Car_Details?

Leslie

 

[eXtacy]

thanks for your reply....

yes it is...

------
eXtacy

 

[eXtacy]

opps....no it is'nt ........i thought it was

i tried it and it works

thanks for your help...

------
eXtacy

 

[lespaul]

OK, let's try this query just to get back the customer discount information, then we'll move back into your calculated fields:

SELECT Transaction.Invoice_Number, Transaction.Number_of_Tyres_Fitted, Price.Price_Ex_VAT, Company_Details.Discount FROM Transaction INNER JOIN Car_Details on Transaction.Registration_Number = Transaction.Registration_Number
INNER JOIN Car_Details.CompanyID = Company_Details.CompanyID

So does that at least run without an error?

leslie

 

[eXtacy]

i get a syntax error....it says

missing operator in query expression

'Transaction.Registration_Number = Transaction.Registration_Number
Inner Join Car_Details.CompanyID = Company_Details.CompanyID'



------
eXtacy

 

[lespaul]

My bad! wrote that part wrong and missed a join! try this instead:

SELECT Transaction.Invoice_Number, Transaction.Number_of_Tyres_Fitted, Price.Price_Ex_VAT, Company_Details.Discount

FROM Transaction

INNER JOIN Car_Details on Transaction.Registration_Number = Car_Details.Registration_Number
INNER JOIN Company_Details on Car_Details.CompanyID = Company_Details.CompanyID
INNER JOIN Price on Car_Details.Tyre_Type = Price.Tyre_Type


might want to make sure I spelled everything right!

Leslie

 

[eXtacy]

im still getting an error...missing operator in query expression in the inner join part..

------
eXtacy

 

[lespaul]

did you add the ';' to the end of the query?

It may be required.

It looks like all the joins are right. We're getting the information from transaction, joining CarDetails to Transaction, company details to cardetails and price to cardetails.

If you post the SQL and switch to design view, does Access complain about the query?

Leslie

 

[eXtacy]

yes ive tried it with the ';' and still get the error..

i cant switch to design veiw either....gives the error message.



------
eXtacy

 

[lespaul]

Ok, then let's try to step through it in the Query Designer:

1. Open a new query
2. Drop Transaction table in the grid
3. Add the fields InvoiceNumber & Number of Tires
--make sure it runs!--

4. Drop CarDetails in the Grid and join the Registration Number (be sure to go FROM Transaction TO CarDetails)
--make sure it's still happy - you should also check the SQL view and see if it's still FROM TRANSACTION
5. Drop CompanyDetails in the Grid and join the CompanyID FROM CarDetails TO CompanyDetails
--check to make sure its still Happy!!
6. drop the Price table in the Grid and join CarDetails to it.

Ok, if it still runs, add your other NON-CALCULATED fields (discount, priceexvat and discount)

Do we have a working query?

leslie