I have to create a price discount database - to be used for entering discount information needed for accounting department such as product no. , name, customer, discount % or discount in currency (I was thinking of combining those the last two if possible giving the user possibility to use which he likes and the other option would
be calculated automatically afterwards - is it possible?), period for discount, volume minimum and target, reason for discount and the way of giving discount.
Our product list is very large containing about 100.000 items which are grouped to 5 tables looking like a pyramid (only about 30.000 are active so I was thinking of making a table query with only active products) the ground table(X1) contains all part nr. then those are linked to X2 table to models, then X3 to product groups and so on. I would like to enable some sort of search in my dataentry form for the user so he does not have to type product no and name. How can I do this with such a large product list?
Also cost price and current sales price have to be shown on the dataentry form witch the user can see margin % and if there is base for discount.Sales prices can be obtained from orderline table from third database which
is, you can imagine, huge. How can I make it lookup price for the right product and right customer in the right time?
the same database will be used to follow up on results of discount campaigns in terms of volume increase.
I would like to enable results to be seen by a single campaign, or by all open campaigns, by a specific product group or by customer.
therefore I was thinking to design campaign code and campaign name to be entered in a separate table and in the same time enter campaign code to the price discount table with additional line no (those two would be primary key
in price discount table) Is it possible to do in this way?
Please can someone help?
.
Thanks.
Anna
be calculated automatically afterwards - is it possible?), period for discount, volume minimum and target, reason for discount and the way of giving discount.
Our product list is very large containing about 100.000 items which are grouped to 5 tables looking like a pyramid (only about 30.000 are active so I was thinking of making a table query with only active products) the ground table(X1) contains all part nr. then those are linked to X2 table to models, then X3 to product groups and so on. I would like to enable some sort of search in my dataentry form for the user so he does not have to type product no and name. How can I do this with such a large product list?
Also cost price and current sales price have to be shown on the dataentry form witch the user can see margin % and if there is base for discount.Sales prices can be obtained from orderline table from third database which
is, you can imagine, huge. How can I make it lookup price for the right product and right customer in the right time?
the same database will be used to follow up on results of discount campaigns in terms of volume increase.
I would like to enable results to be seen by a single campaign, or by all open campaigns, by a specific product group or by customer.
therefore I was thinking to design campaign code and campaign name to be entered in a separate table and in the same time enter campaign code to the price discount table with additional line no (those two would be primary key
in price discount table) Is it possible to do in this way?
Please can someone help?
.
Thanks.
Anna
