Tricky calculation to perform

[jzwp11]

First, looking at your table structure, you should not have the customerID in the invoice table since you are already joining the invoice to the order. The order has the customerID (so no need to repeat it). How you handle invoices in your database, depends on how you handle them in your business. The way you show it now, you can have many invoices from an order. Is that consistent with your business process? Or will you only have 1 invoice per order? Do you wait for all items that have been order to be shipped/sent before you issue an invoice? Is the invoice amount generally the same as the order amount?



Just looking at your database, the order details gives you the information you will need for the invoice, does it not?

[leeli67]

Ok, Having just had a chat with the bakerman he has told me that a few of the customers in my list Order the same stuff for daily delivery every week (a standing order), so i was wondering should I start a new table and work those in by way of a query to populate my orderdetail subform, im thinking about a new table with the days of the week in due to these customers needing a delivery everyday of their standing orders and then linking the customer and products table with this new days off the week standing orders table

[jzwp11]

Since they are still orders, they belong in the order table. Can a customer have multiple standing orders? Let's say that they have a standing order as you describe for each week that covers every day in a week. Next, let's say that they have another standing order that happens on the 1st and the 15th of the month. So, the 1 customer would have 2 different standing orders. Can this happen?

In terms of setting up the standing orders, I would probably add a field to the orders table to identify the order as a regular order or a standing order. A standing order would actually not be filled by the baker, , but it would act as a template to create a regular order or orders that would be filled. Alternatively, you could create a table that associates a customer with an existing order# that would be the template for a standing order

tblCustomerStandingOrders
-pkCustStandOrderID primary key, autonumber
-fkCustomerID foreign key to customer table
-fkOrderID foreign key to tblOrders
-fkFreqID foreign key to a table that holds the frequency (daily, weekly, twice weekly, twice monthly etc.)

Then if you want to specify exactly which days, another table would be necessary

tblStandingOrderDays
-pkSODaysID primary key, autonumber
-fkCustStandOrderID foreign key to tblCustomerStandingOrders
-longDay

For daily standing orders you would have a record for each day, a 1 might signify Sunday, 2 Monday, 3 Tuesday etc.

If the standing order is a weekly order you could specify just one record with a 1 (the weekly order is to be delivered on Monday)

For a twice monthly order, you could specify two records: 1 and 15 which actually would represent the date which the order is to be delivered i.e the 1st and the 15th of the month.

BTW, you never answered my question on the invoicing.



Next you have to define a frequency

[leeli67]

Hi Jzw , yes you are correct the orderDetails do have everything I need for an Invoice. Product, quantity and price. and couple that with the order table , yes that would be enough. also reading above I have determined that I think I need to do the template type route because thats close to what the baker has now

5 or 6 of his customers have a range of products delivered everyday , but they will also ring up a adjust their Regular orders on some occasions, so I think a template that is pre filled with these items, but can be altered if needed. the whole process repeats weekly so no need for me to factor in certain days of the month .

So I reckon that i should start by creating the tblCustomerStandingOrders you mentioned

Thanks JZW

[jzwp11]

OK on the standing order table.

Regarding the invoices again. Based on previous discussions there were different invoicing frequencies. So does this mean that an invoice can include multiple orders? If so, your current structure is not set up for that.

[leeli67]

The customers will get invoiced once a week for all the orders they placed in the week previous, so I think that the Invoice would include more than one order.

[jzwp11]

Then your current structure is not correct. You need to unlink the invoice table from the order table and remove the orderID field from the invoice table and add the customerID field back in. So the invoice table should look something like this:

tblInvoice
-pkInvoiceID primary key, autonumber
-fkCustomerID foreign key to customer table
-dteInvoice (invoice date)
other invoice related fields

Now, since an invoice can have many orders we need another table

tblInvoiceOrders
-pkInvOrdID primary key, autonumber
-fkInvoiceID foreign key to tblInvoice
-fkOrderID foreign key to order table

[leeli67]

With Ref this Table

tblStandingOrderDays
-pkSODaysID primary key, autonumber
-fkCustStandOrderID foreign key to tblCustomerStandingOrders
-longDay

what is the LongDay , Day is the field name , is long , long Integer?

[jzwp11]

longday was going to be for which day of the week or month the standing order was for but you said that all were going to be the same day, so it would not be necessary.

[leeli67]

I only need days of the week not days of the month , do i still need second table ?

I think i do.

[jzwp11]

Yes, you still need the second table. If you want you can also have the days of the week table you mentioned and then reference the foreign key in place of the longdays field

[leeli67]

Right these are the tables I have so far, how do i link the 3 at the bottom to main frame ? customerID is 1 ,I think

.Attachment 6886

[jzwp11]

CustomerID of tblCustomer to CustomerID of tblCustomerStandingOrders (BTW, your table name is spelled tblCustomerStandingOders) and orderID of tblOrders to orderID of tblCustomerStandingOrders. You do not need the FreqID field in tblCustomerStandingOrders since your frequency is the same for all standing orders. Also, I believe the word "day" in tblDaysOfWeek is a reserved word in Access, so you will want to change that, perhaps dayname. I would probably put a long number integer field into the tblDaysOfWeek table so that you have number that corresponds to the day of the week, for example 1=Sunday, 2=Monday etc. There are some built-in functions that require a numeric value to represent the day of the week. You could rely on the autonumber field, but I generally do not recommend that.

[leeli67]

Just one thing that Im not sure on JZW is these standing orders , Should I have a pre filled table with the products and quantity that I drag into the main order form or am I querying from somewhere else, im guessing that my tblCustomerStandingOrder will hold all the data for that ?

[jzwp11]

Should I have a pre filled table with the products and quantity that I drag into the main order form or am I querying from somewhere else, im guessing that my tblCustomerStandingOrder will hold all the data for that ?

No other tables are necessary & no other fields are necessary on tblCustomerStandingOrder, to do so would be redundant which is one of those things you do not want to do in a relational database. If you reference an existing order as the "standing order" you would use an append query to extract the items and quanitities from that existing order and append those records to a new order.