delivery notes/invoice tables

[harps]

i would like to link the delivery note table to the invoice table so that each delivery note will automatically create an invoice.

how can i do this?

 

[vbaInet]

Create a query and link them using the Query Wizard.

 

[harps]

I will try that

 

[harps]

While you are here, is there anyway to get subdata sheets to update fields from the previous parent/child, to save having to repeat data input?

 

[vbaInet]

Please elaborate.

 

[harps]

I have a series of tables: Customer,OrderHeader, OrderDetails, MeasurementHeader,MeasurementDetails,InvoiceHeader, InvoiceDetails. is there any way (apart from update query) by which data entered into say the customer table will update the same fields in the other tables (eg customer no), without the need to manually type it in each table.

 

[vbaInet]

Yes it's possible. It's relevant in cases where you're moving from one form to the other and don't want to have to enter the ID again. It wouldn't be a sensible thing to just propagate the ID all over without setting focus on the form.

So it would depend in what situation you would like to propagate the ID. Is it when you open a form? Could it be a page on a tab control?

 

[harps]

this is for entering data for a customer who is serviced through a franchisee.
basic flow diagram would be customer------franchisee-----orderheader-----orderdetail-----measurementheader-----measurementdetail.

the repeating fields would be CustomerID and FranchiseeID. both become foreign keys in relevant tables. this is still in tabular form as i have to finish off a few queries. is it necessary ro worry about it at this stage or wait until i would do forms and connect as sub forms?

 

[gemma-the-husky]

the delivery note needs to store an invoice number - therefore any DN without a number hasnt been invoiced.

you then have an invoicing process which finds the order details etc, relating to the uninvoiced DN's, and invoices them accordingly.

Is that what you mean?

 

[harps]

to give a background, this is for a suit hire company. the suits are hired out through a franchisee, who is then invoiced for the suit. so the flow is
1. the customer places order with franchisee
2. the franchisee forwards the order to the hire company
3. the order is delivered to franchisee with DN
4. the DN should produce an invoice.

so how can I repeat fileds such as customer no and franchisee no to automatically update to these tables without the constant need to re enter data?
also do i need to produce a query to calulate out the invoice total (inc vat), or would this be better done as a form to produce the invoice to print?

 

[gemma-the-husky]

you dont need to repeat fields, or dont HAVE to anyway

the DN should be linked to the order which is linked to the customer/franchisee

so all the information should be available, based on this.

 

[harps]

so does this mean i create the DN via a query on the order? and if so is the invoice created via a likewise query?

 

[harps]

this is what i have got so far. could somebody please give me a critical review, bearing in mind i am not a db expert but somebody who is on a very steep learning curve with all your collective help. For this I thank you all in advance. At this stage I am not sure of my success so far and if it is okay I will move onto forms and reports.

thx

Harpur