get started a (complicated) database

[isabela]

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

 

[willir]

isabela

With respect to tracking by "campaigns", this sounds like a many-to-many. Many products can be assigned to one campaing. A product could be sold under differnet campaigns.

Next is the customer. You have not talked about invoicing, and order entries. Are these to be tracked? Based on what you have stated, ti sounds like it would be useful to develop profiles for each customer. Invoicing information would be helpful.

If this is not readily available, then you may need to create your own. Customer + Product + Campaign + sales info.

Using this same table, you will also be able to work backwards and see which products responded to which campaigns.

Obviously, with even 30,000 active products, tracking M:M will generate a lot of data.

Next data entry...
One thing I have seen is the use of a price contract table. In your case, you are probably not interested in the "contract price", but rather a profile of what the customer orders. Search this table first, if not found, then search the larger active table, if not found, search the older product list.

This same table may be where any volume discounts values are stored. The I see it, discounts can be applied globally strictly dependant on the dollar or quantity, or at the customer level - favoured customers type of thing.

Hope this rambling helped.

Richard

 

[jfgambit]

Anna:

A couple of questions you need to ask yourself in regards to this:

1. Are the discounts going to be given on an individual product line item level or as a complete total of the order?

2. Sale price from the linked database can be obtained, but if an order is changed what Sale price will be used, the orginal or the most current?

Jeff

http://jfconsult.home.comcast.net

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[isabela]

Thanks for your answers.
I realize i asked a lot of questions at once.
I am really new at using access and this forum.
The discounts will be given only on single product line and to some customers not all. The discount will be valid only for a limitid period of time, that's why I am calling it campaigns. I will work with most current sales prices from invoicing table.
I have began on building of my database and have all the tables I need. Right now I am working on data entry form and triying to figure out how to make a easy and user friendly search in the product list. (the user cannot remember all Product's ID only maybe few letters of the product name). A simple combobox seems not applicable as the product list is large.
Thansk a lot.
Anna

 

[Golom]

Re: Easy & User Friendly

I had a similar problem and chose the route suggested by the following


SelectClause = _
"(Select DISTINCT StockCode as Code, " & _
" Descript As [Description] "

If KeyCode = vbKeyReturn Then

SearchString = Replace(txtStockCode.Text, "'", "''&quot

If Len(SearchString) = 0 Then
' Show all items if nothing was entered.
SQL = _
"Select StockCode as

, " & _
                "       Descript  As [Description] " & _
                "From   StockMst " & _
                "Order By StockCode "
                
        Else
            [/color][COLOR=green]' Select everything that starts with the entered string.[/color][COLOR=blue]
            SQL1 = SelectClause & _
                    "From StockMst " & _
                    "Where StockCode LIKE '" & SearchString & "%';) "
            [/color][COLOR=green]' Select descriptions that contain the entered string.[/color][COLOR=blue]
            SQL2 = SelectClause & _
                    "From StockMst " & _
                    "Where Descript LIKE '%" & SearchString & "%';) "
            [/color][COLOR=green]' Select aliases that start with the entered string.[/color][COLOR=blue]
            SQL3 = SelectClause & _
                    "From StockMst INNER JOIN PluMast ON StockMst.StockCode = PluMast.Plu_Prim " & _
                    "Where PluMast.Plu_Code LIKE '" & SearchString & "%' AND " & _
                    &quot;PluMast.Plu_Code <> PluMast.Plu_Prim ;)&quot;
    
            SQL = SQL1 & &quot; UNION &quot; & SQL2 & &quot; UNION &quot; & SQL3 & &quot; Order By Code &quot;

        End If
[/color]
The third retrieval (SQL3) just accounts for the fact that a stock code may have several aliases in my system and those are stored in a separate table called PluMast. You may not need that in your system.

The whole thing is displayed in a data grid and the user can select the one required.

 

[isabela]

How to try this, should I make a combobox and write codes at row source or...
Thanks, I appreciate your help.
Anna

 

[Golom]

I use a data grid. Exactly which one you use (DBGrid Or DataGrid) depends on whether you are using DAO or ADO. Whichever, place a data control on the form and make it the data source for your grid. Then just set the data control's recordset to this SQL. The mechanisms are somewhat different for ADO or DAO. Let me know which you are using and I'll supply some code for you.

You can use a combo box but they are unpleasent because queries like this can return 100's or even 1000's of records and scrolling through a combo drop down with that many records sort of defeats the "easy and friendly" objective.

 

[isabela]

This is all new for me.
I have a microsoft access 97 version, so I guess it only support DAO method.
I have a access 2000 book and it says that the new method is ADO. Just to make sure I understand: either ADO or DAO method would enable me to get a selective recordset from my product list table based on some cretarie user define (some few letters from product name for exsample)and then as it would be a "short" list, the user can easily choose the one he needs. When the user make his choice the productID can be stored to the Product ID field in my discount table. Right?
Thanks for your help.
Anna

 

[Golom]

Oh ... sorry ... I thought you were using VB as a front end to Access. No matter ... this should work in Access as well. Access 97 does support both ADO and DAO; you just need to reference the proper libraries.

Yes. Either data access method will allow you to do what you want but you will need to write the code to do it. All I have supplied here is some skeleton code that shows you how to pull records from the table when the user types a partial stock code or description.