Help please problem with database sample attached

[hullstorage]

hi all

i wondering if somebody can help i have been trying to get this database up an running but can't get by this bit...

i have attached a database so you may be able to see what i am trying to acheive.

we enter deliveries for a number of customers on a daily basis then at the end of the month we create an invoice for all of the deliveries made for a given customer.

I have set up relationships but cant get to work out how to create the invoice number from the invoice table

i have also in the upload created a form to show you how i want the databse to work ( this is not based on any of the tables )

so we would open the deliveries form then select the customer from the drop down box, this will then let us call up any deliveries made for the selected customer and add more deliveries to the invoice.



any help please

 

[John Big Booty]

Is this the sort of thing you're after?

 

[hullstorage]

Is this the sort of thing you're after?

thanks for that.

nearly there, what i need is that when i start up the delivery form there will be no records showing and when i select the customer from the combo box then the records for that customer will show in the subform

any ideas

thanks
simon

 

[hullstorage]

dont know if this helps

open delivery/details form
enter delivery details for selected customer
on a daily basis

at the end of the month
open form invoice
this will then call all records not invoiced
and allocate the invoice number to the records
maybe by way of a command button


thanks
simon

 

[John Big Booty]

Firstly do you have a way to identify which deliveries have been invoice?

Once you have done that you will need to write a query that can select the uninvoiced deliveries and who they belong to.

you will then need an append query to append the clientID to the Invoice Header Table, at this point you can also assign your invoice number.

Then you will need to append the DeliveryID number and cost into the InvoiceDtl Table, that's all you should need to store in the Invoice Dtl Table along with the InvoiceID. All the rest of the information you should be able to collect with a query from which you can then build you Invoice Report, or display it in a form per the sample, I posted earlier.

Finally you will then need to update those deliveries that have just been invoiced as having been invoiced.