Form to display sum results

[hullstorage]

Hi all,

I will try and make this as brief as I can.

What i am trying to do is get results from certain tables and add the values to another, here goes?

[InvoiceResultFrm] based on InvoiceTbl

InvoiceNo
InvoiceDate
Customer
Net
Vat
Total

[JobTbl]
Customer
Net
Vat
Total
Invoiced Yes/No

[InvoiceTbl]
InvoiceNo
InvoiceDate
Customer
Net
Vat
Total


on the form itself

all the values will be based on values from the jobs table where they equal "no"

I want the form to look up the last invoice number used and add 1
so probably have to use dmax +1 for the invoice number

then i have a drop down field that displays the customers

and then look up the total net value of all jobs that equal invoiced "no"
so will probably have to create a query or something

please see form picture attached

 

[Galaxiom]

I am not clear on exactly how you are doing all this but I think you might need to look closer at your data structure. There would appear to be a normalization error with the Invoiced field in the Job table. This should not be stored but determined by the related record in the invoice table which should have a Foreign Key to the Job.

 

[boblarson]

And Job Table should have a unique identifier (JobID) and that should be in the Invoice table as a foreign key.