Hello,
On my database, I have the following tables, job, labour and material.
I create a custom view called invoice.
I edit the sql to the following code.
select job.Job_No,
job.Order_Date,
job.File_No,
job.Order_Site_Address,
job.Job_Description,
customer.Customer_Name,
customer.Billing_Address,
labour.Working_Hrs * Paying_Rate,
labour.Working_Hrs * Paying_Rate * 0.175
from job inner join labour
on (job.Job_No = labour.Job_No)
inner join customer
on (job.Customer_Name = customer.Customer_Name)
This works fine, If I look on the job table and select Job_No 1000000 I can see 1 working hour and paying rate of 15 and it displays 15. The next colume shows the VAT content of 2.63. Any info in the labour table is displayed. But if I want to look at labour and material, I enter this code,
select job.Job_No,
job.Order_Date,
job.File_No,
job.Order_Site_Address,
job.Job_Description,
customer.Customer_Name,
customer.Billing_Address,
labour.Working_Hrs * Paying_Rate,
labour.Working_Hrs * Paying_Rate * 0.175,
material.Quantity * Unit_Price
from job inner join labour
on (job.Job_No = labour.Job_No)
inner join customer
on (job.Customer_Name = customer.Customer_Name)
inner join material
on (job.Job_No = material.Job_No)
Now, In the job table on Job_No 1000000 I have the same info as above for the labour, but as there are no materials to show, the result shows "No Records Found".
The only difference in the two bits of code are,
inner join material
on (job.Job_No = material.Job_No)
Please help, I only have a few days of the trial period left before I can convince my boss to buy it.
Cheers
Paul.