Problems generating an Invoice

[Craig Armitage]

Hi,

I would like to generate a printable invoice based on job data in my
database.. basically, i have 3 tables.. Jobs, Customers and JobsPartsUsed

Relating to this problem, the tables have the following fields..

Jobs table
----------
JobsID (pk)
CustomerID (fk)
JobReport

Customers table
----------------
CustomerID (pk)
CustomerName
CustomerAddress

JobsPartsTable
----------------
JobsPartsID (pk)
JobsID (fk)
JobsItemDescription
JobsItemCost
JobsItemQuantity

Ok, hopefully you can see that i want to generate an invoice based report
that shows the customers details at the top, then the JobReport and finally
a list of any parts used in the job.

I cant seem to get the JobsPartsTable to only show the parts related to the
JobsID.. it always shows all the parts.

How would you guys set this up?


Also, on a slightly different note, i would like to calculate the sub totals
of each of the items in the list.. is the best way to have a field called
subtotal and get it to be filled in on an changed event for the qty/cost
fields?

Thanks in advance for any help given..

Craig

 

[Damian S]

Hi Craig,

If it's showing all records, you need to have a link between the ID
fields... The SQL would be something like this:

select * from Customers
inner join Jobs on Jobs.CustomerID = Customers.CustomerID
inner join JobsParts on JobsParts.JobID = Jobs.JobID

Use report grouping to separate your Customer info, then Job info and have
the JobParts in the details section. You can then subtotal by Job and Grand
Total overall using the sum() function in the relevant footers.

Hope this helps.

Damian.