Question New database project any help!!

[hessleroader]

Hi all,

I am beginning a new database project based on the following tables below.
My main objective is to assign an invoice number to orders which can be 1 or more orders per invoice

customers
orders
invoice

I am still learning access but would consider myself as advanced
thanks
all

 

[PaulO]

1) I think you need to give us all a lot more to go on ... but one observation I have is that it would be more normal to have multiple invoices associated with a single order rather than the other way around as described?
2) I'd also heartily recommend splitting both the orders and invoice tables into two, being a header and detail table for each with the link within sets being the order # and invoice # respectively
3) In terms of other useful tables you'll probably also need, at the very minimum, a products table so that your orders can be populated with products/services?

 

[Rabbie]

1) I think you need to give us all a lot more to go on ... but one observation I have is that it would be more normal to have multiple invoices associated with a single order rather than the other way around as described?
2) I'd also heartily recommend splitting both the orders and invoice tables into two, being a header and detail table for each with the link within sets being the order # and invoice # respectively
3) In terms of other useful tables you'll probably also need, at the very minimum, a products table so that your orders can be populated with products/services?

I issue invoices on a monthly basis and orders can happen any time so I do have a 1 to Many relationship between Invoices and Orders. I run a VBA procedure at Month end to generate the invoices. This also allocates the Invoice numbers.

I would agree that you need to have an Order table and a OrderDetails table.

 

[hullstorage]

I issue invoices on a monthly basis and orders can happen any time so I do have a 1 to Many relationship between Invoices and Orders. I run a VBA procedure at Month end to generate the invoices. This also allocates the Invoice numbers.

I would agree that you need to have an Order table and a OrderDetails table.

Hi Rabbie,

This is the same thing that we do
can you eliviate on this

at moment i have something like this, we do not use products it is a service we supply and varies a great deal on day to day

[invoice table]
InvoiceId
InvoiceNo
InvoiceDate
CustomerID
TotalInvoice

[OrderTable]
JobId
JobDate
CollectGoodsFrom
DeliverGoodsTo
CustOrderNo
£Cost
Invoiced_Yes/No

 

[Rabbie]

What I do is to have a field in the order table that holds either the Invoice number or zero if an invoice has not been issued. I also have a field for CustomerID in the Ordertable so I know who to invoice.

When I want to create invoices I first run a query to select all unvoiced orders and sort this by customer. Then I run VBA code to create the Invoice records. The Invoice number is created using a MAX(Invoice) function on the existing table and adding 1 to it. Finally I store the invoice number in each order so I know it has been invoiced. I don't actually store the value of the Invoice in the Invoice table as I can easily calculate it in a query when I need it.

 

[hullstorage]

What I do is to have a field in the order table that holds either the Invoice number or zero if an invoice has not been issued. I also have a field for CustomerID in the Ordertable so I know who to invoice.

When I want to create invoices I first run a query to select all unvoiced orders and sort this by customer. Then I run VBA code to create the Invoice records. The Invoice number is created using a MAX(Invoice) function on the existing table and adding 1 to it. Finally I store the invoice number in each order so I know it has been invoiced. I don't actually store the value of the Invoice in the Invoice table as I can easily calculate it in a query when I need it.

Hi there yes this is the way i would like to do it:

use a query to raise a customers orders where the the invoiced field, which is a yes/no ( invoiced )= No

i Know how to use the dmax function but how do i assign the invoice number to that order

thanks

simon

 

[hullstorage]

What I do is to have a field in the order table that holds either the Invoice number or zero if an invoice has not been issued. I also have a field for CustomerID in the Ordertable so I know who to invoice.

When I want to create invoices I first run a query to select all unvoiced orders and sort this by customer. Then I run VBA code to create the Invoice records. The Invoice number is created using a MAX(Invoice) function on the existing table and adding 1 to it. Finally I store the invoice number in each order so I know it has been invoiced. I don't actually store the value of the Invoice in the Invoice table as I can easily calculate it in a query when I need it.

what is the VBA code you use

 

[Rabbie]

what is the VBA code you use

I am at work and don't have access to that system. I'll post it when I get home tonight.

 

[hullstorage]

Thanks

Would it also be possible to send me a small sample of your database of your so i can see how yours works as i think it is a simular sort of thing i need to do

thanks

simon