Tricky calculation to perform

[jzwp11]

I think the problem you encountered stemmed from having lookup fields in your tables. Although Access has this capability, it is generally not recommended. See this site for the problems table level lookups can cause. I removed all of the lookup fields I could find and added a new table as a result. I also added the discount table as we originally had discussed and cleaned up some of the other items we discussed. Assuming that you may have many payments for an invoice not an order, I restructured that as well. The revised DB is attached.

[leeli67]

would I be correct in thinking that you have only altered the tables and not forms, queries and reports, also is it possible I can use combobox in forms but not related table.

[jzwp11]

Correct, I did not alter any of your forms, queries or reports. Those would probably have to be redone in order to work properly with the new structure. And yes, you should use the combo/listboxes in your forms.

[leeli67]

and the Relationships in the relationships view is all ok ?, thank you again for your help, You are most knowledgeable on this subject

[jzwp11]

You're welcome. Good luck with your project.

[leeli67]

just a quickie , what does the "sp" mean in spDiscount field. I believe its a field format , i.e text, num and so on.

[jzwp11]

The sp denotes single precision number datatype for the field.

[leeli67]

and I guess my next step is to fill in all the data in the tblCustomerCatergryDiscount table?

[jzwp11]

Actually you are probably ready to create forms for data entry.

[leeli67]

still a bit confused as to how I may intergrate the Discounts table in the forms, or does this system mean I have to Manually put in the discount when im entering an order, I was hoping that it would know that a certain customer has a certain discount on a certain item.


I thought the discount table should look like this ?

Attachment 6780

[jzwp11]

Since the discount applies to the category in which the item belongs as well as the customer, those are the two criteria you will need. I assume that when you do an order, you have the customer info in the main form and the order details in a subform. If my assumption is correct, you would need a combo box based on the item/product table in the subform. Be sure to include the category (the fkcategoryID) field in that combo box (you do not have to show it to the user). You would add a control to the subform. I would probably use some Visual Basic for Application (VBA) code in the after update event of the product combo box that looks up the applicable discount based on the criteria mentioned earlier. The code will need to use the DLookup() function.

I'm not sure what your VBA skills are like, so if you need more help, could you post an updated copy of your database and identify which form needs to have the discount added?

[leeli67]

yeah I think your right about the control in the subform , that sounds right, and as you will see I added a combobox to the order form for customer lookup and one for product lookup incorporating 3 fields to auto populate the price field, thanks for having a look.

[jzwp11]

One quick question; can the discount that a customer receives for a category change over time? For example, let's say a customer currently gets a 10% discount on bread, but he tells you that he is expanding his operations and will be buying twice as much bread starting next month. He has had an offer from another supplier who is willing to offer a similar product for less than your 10% discounted price. I assume that you will want to push up your discount to keep the business. Am I correct in my thinking?

[leeli67]

yes that may happen , but wouldnt be a problem if the discounts were fixed

[jzwp11]

Yes it would be a problem because if the discount changes earlier invoices (before the change) will not reflect the correct discount. Remember, that calculated values such as an invoice total are generally not stored; only the raw data is. So it would be best to take care of that now since it requires a change to the table structure. Considering that a combination of customer and category may have many discounts (over time), how would you structure the change?