Multiple invoices attached single job

[lpkorn423]

Thanks for any help in advance.



Currently have a database that's main table has individual jobs. Each job has multiple 'parts'. I'd like to be able to create multiple invoices for each job because sometimes we are forced to bill by part instead of by the entire job. What I'd like to be able to do is create an invoice based on a single job, but pick and choose what 'parts' it populated from the job table.

Example:
Let's say we have a job that requires fabricating 3 unique items. The job table would include all of the information, including price, for each of the unique parts.
If the buyer requires it, I'd like to be able to have 3 separate invoices that all populate the information from each individual part. Alternatively we might have to include 2 parts on one invoice and 1 part on another invoice.

[jzwp11]

Welcome to the forum!

If a job has multiple parts, you should have 2 tables not 1 since having multiple parts to a job describes a one-to-many relationship. If you properly structure that, the invoicing by parts or a number of parts becomes easier.

The structure might look like this

tblJob
-pkJobID primary key, autonumber
other job related fields

tblJobParts
-pkJobPartID primary key, autonumber
-fkJobID foreign key relating back to tblJobs (must be a long integer number datatype field)
-currPartPrice
other part fields

tblInvoice
-pkInvoiceID primary key, autonumber
-dteInvoice (invoice date)

tblInvoiceParts
-pkInvPartID primary key, autonumber
-fkInvoiceID foreign key relating back to tblInvoice (must be a long integer number datatype field)
-fkJobPartID foreign key relating back to tblJobParts (must be a long integer number datatype field)

[lpkorn423]

Well that makes things a lot easier...

New to access so much thanks!

[jzwp11]

You're welcome.