Help with new database please..Invoices

[hullstorage]

Right here goes,

I have now got rid of my old database as we did not have any relationships etc in place so information and data entry was a bit of a nightmare so i am starting a fresh new one and would like any help.

I am quite new to relaionship and codes etc...

Right!

We have various customers who give a various deliveries to complete on a daily basis which in turn are invoiced at the end of each month so there may be 20 records or so for each customer:

at the moment we have 3 tables which are customers, Jobs, Invoices

The current relationship in place are what you can see in the attachement, although more fields will be added i am just trying to get this bit to work then i will add fields and queries etc.. later on.

Also attached to the picture is our current job entry form where we select the customer then add the details about the job, the collection from fields are normally defaulted to customers collection address as they also have a invoice address what i mean is we will collect from their warehouse ( collection address ) which might be in another town, and invoice their head office.. or this might just be the same as the invoice address

the main problem i am having is adding the invoice number to this form as i want to avoid using a subform do to the amount of information being entered.

I would like to create a form like the one i have but automatically assign the invoice number when i enter a new record, so if this is for a customer that already has jobs enetered this would enter the invoice already in place and if this is the first record that the customer has placed this will create a new invoice number taken from the invoice table

Please bare in mind that the invoicing is done monthly so i dont want the invoice number on next month deliveries

dont know if it might be easier maybe just entering the deliveries without invoice number then create a form that at the end of each month show all records for a given customer the assigns an invoice number

the form is currently based on a query where all the jobs have a invoiced value as "No".

many thanks all

 

[neileg]

Your relationship is the wrong way round. If you have many jobs on one invoice you need to hold the invoice number in the job table, not the job number in the invoice table.

I don't understand why you don't want a subform, but you can slot the invoice number against the jobs using an append query.

 

[hullstorage]

Your relationship is the wrong way round. If you have many jobs on one invoice you need to hold the invoice number in the job table, not the job number in the invoice table.

I don't understand why you don't want a subform, but you can slot the invoice number against the jobs using an append query.

I knew something was wrong but can you explain a bit more as i am still learning

i have attached a bit of the database i use to give you some idea

thanks

 

[neileg]

Sorry, I don't do A2007.

Which bit don't you understand, the relationship or the append query?