Question (1 Viewer)
- Thread starter Nicolette
- Start date
boblarson
Smeghead
- Local time
- Today, 10:56
- Joined
- Jan 12, 2001
- Messages
- 32,059
I just popped in to this thread but the pdf file looks pretty good to me as far as fields and relationships go.
Add to tblOrderdetails OrderQty and OrderPrice.
Also, you still need a field OrderDetailPK (first field) which is the unique field for the tblOrderDetails.
The join to tblOrderHeader is by OrderID, as you have but this can not be the tables Primary key / unique field because it will be repeated where an OrderHeader has many line items listed in tblOrderDetails.
You should hold the price in the OrderDetails table as the Inventory table will be updated but your sales history won't change.
The join between tblOrderHeader and tblOrderDetail is One to many. One OrderHeader to Many OrderDetails. Not 1 to 1 as shown.
Also, tblCutomers to tblOrderHeaders will be One Customer to Many OrderHeaders.
Sorry but also noticed... tblPayments should be joined to tblOrderHeader by One OrderID (OrderHeader) to Many InvoiceID (Payments).
Reason being, tblOrderDetails won't be as easy to calculate the AmountToPay as tblOrderHeader.
You don't need a field to Pay, Just an OrderNumber. The amount to Balance Against will be Calculated and because the Qty & Price are in the OrderDetail and each record there has a field OrderID, then you can get the AmountToPay, as and when you wish.
If you wish to have a different Number for the Invoice, this should be moved to tblOrderHeader and use this then as the join to tblPayments, but... what if the payment arrives before the Invoice is raised. ie, you only have an OrderID.
Modern systems keep the same number all through the process. You may find it difficult to keep track of two sets of accounting numbers. OrderID and InvoiceID.
If you add a field to tblOrderHeader, say OrderConf and make this a text field to carry "Yes" (not a tick box) then it will be easy to have a command button on your form that is clicked when an Order becomes an Invoice and "yes" will be entered in the field. From then on, the system will check for "yes" and if present, the number will be referred to as an Invoice rather then an Order.
No big issue with the Sku, just a suggestion.
Also, you still need a field OrderDetailPK (first field) which is the unique field for the tblOrderDetails.
The join to tblOrderHeader is by OrderID, as you have but this can not be the tables Primary key / unique field because it will be repeated where an OrderHeader has many line items listed in tblOrderDetails.
You should hold the price in the OrderDetails table as the Inventory table will be updated but your sales history won't change.
The join between tblOrderHeader and tblOrderDetail is One to many. One OrderHeader to Many OrderDetails. Not 1 to 1 as shown.
Also, tblCutomers to tblOrderHeaders will be One Customer to Many OrderHeaders.
Sorry but also noticed... tblPayments should be joined to tblOrderHeader by One OrderID (OrderHeader) to Many InvoiceID (Payments).
Reason being, tblOrderDetails won't be as easy to calculate the AmountToPay as tblOrderHeader.
You don't need a field to Pay, Just an OrderNumber. The amount to Balance Against will be Calculated and because the Qty & Price are in the OrderDetail and each record there has a field OrderID, then you can get the AmountToPay, as and when you wish.
If you wish to have a different Number for the Invoice, this should be moved to tblOrderHeader and use this then as the join to tblPayments, but... what if the payment arrives before the Invoice is raised. ie, you only have an OrderID.
Modern systems keep the same number all through the process. You may find it difficult to keep track of two sets of accounting numbers. OrderID and InvoiceID.
If you add a field to tblOrderHeader, say OrderConf and make this a text field to carry "Yes" (not a tick box) then it will be easy to have a command button on your form that is clicked when an Order becomes an Invoice and "yes" will be entered in the field. From then on, the system will check for "yes" and if present, the number will be referred to as an Invoice rather then an Order.
No big issue with the Sku, just a suggestion.
Thought a better way then "Yes" Have default word "Order" in the new field (proposed) and when you click the button, "Invoice" is updated on the field.
This means you simply include this new filed in your Forms and Reports and Wala, you show it as either an Order or an Invoice.
Of course your Queries will take note and filter Invoice when you just want Orders shown and vice versa when you want Invoices only returned.
This means you simply include this new filed in your Forms and Reports and Wala, you show it as either an Order or an Invoice.
Of course your Queries will take note and filter Invoice when you just want Orders shown and vice versa when you want Invoices only returned.
Ok I made your suggested adjustments with the exception of the OrderConf field, because that table already has a status field which I had hoped would do just what you proposed but also included a option to say that it was shipped which in my mind completes the sale. I was basically thinking along the same lines I had it planned out to include ordered, Invoiced, and shipped as the choices for the status field. I do believe this is the same thought you had, is it not?
wouldn't this allow me to do the summary that I want with at a glance being able to see what needs to be done still meaning what orders still need to be invoiced, what orders need to be shipped out? or am I thinking about this in the wrong way
When you have a form and subform they should be linked by a field.
This will follow how you setup your table relationships, if you are using tables, or queries directly from the tables.
You don't say what form but if it is Orders/Invoices then the tblOrderheader is the Data Source for the Main Form and tblOrderDetail is the Data Source for the sub form and the link fields will be OrderID
This will follow how you setup your table relationships, if you are using tables, or queries directly from the tables.
You don't say what form but if it is Orders/Invoices then the tblOrderheader is the Data Source for the Main Form and tblOrderDetail is the Data Source for the sub form and the link fields will be OrderID
ok after I sat staring are it for a while that was what I thought but I wasn't sure. So now the first thing I have to do is to make a record in tblOrderHeader right? so that I have something to link the customer to the orderdetails with right?
yes it is, What I keep running into is a message saying "Control Can't be edited, it's bound to AutoNumber field "CustomerID" but If make it Unbound i can select from the dropdown but nothing populates. Also If i use the record navigation on the bottom everything changes
now I got this to work using a subform for everything but the CustomerID but even then it isn't quite what I'm looking for because there is always an empty row
now I got this to work using a subform for everything but the CustomerID but even then it isn't quite what I'm looking for because there is always an empty row
The empty row is because you have the properties set to Allow Additions = Yes.
When you get the form working properly you can have the properties change so when you go in to view a record, no extra line will show, unless you click a button to change the property and allow additions.
When you get the form working properly you can have the properties change so when you go in to view a record, no extra line will show, unless you click a button to change the property and allow additions.
