Help how to make an invoice

[manoskok]

I need help how to design an invoice for customers applying for chemical analysis

Each customer has specific price for each chemical substance

Each customer can apply for certain chemical substances

I have a table where the analysis is recorded :
customer name, date, code of analysis
substances examined
value for each substance

I have another table where the cost is recorded :
customer name
substance name
substance cost

I don't know how to evaluate the total value for each analysis (ie for the specific customer, to identify which substances were examined,find the relevant cost for each substance, add the costs to find the total for that analysis and find the total cost for all the analyses for that customer)

Please help

 

[JustMaybe]

On making an invoice for my system...

All the calculations need to be made in a form or table before making the report. the report is made by copying those fields.

Does this help at all.

if not come back to me.

Invoices have been all i've worked on for the last week!

 

[Rich]

Calculations shouldn't be made in a table, have a look at Northwind and Orders sample db's which ship with Access

 

[manoskok]

Thank you all for your help
I finally come with an idea I would like your opinion if is correct.

My database :

tblcustomerscost
customername
chemical1cost
chemical2cost
chemical3cost

tblanalysis
labcode
customername
chemical1value
chemical2value
chemical3value

qrycustomercost (with fields)
labcode, customername, chemical1value etc, chemical1cost etc,
amount(expression : Amount: [chemical1cost]*(IIf(nz([chemical1value]);1;0))+[chemical2cost]*(IIf(nz([chemical2value]);1;0)) ...etc

This query shows all chemicals and the relevant costs for each chemical and adds at the last field(amount) the values for those fields (chemical1value...etc) that are not empty.

From this point I can easilly create the report with the final total amount for the invoice.

Do you think this is a good practise to deal with my problem ?

The fields chemicalvalue can be eg : 1, 150, <1, >1000 or -

What happens when the value is - ?

I would appreciate your thoughts!
Thanks again