Inventory Database

[srammij]

O.K. This one's complicated, i hope i can explain this in an understandable way.

I'm working on an exsisting Database system, with customers, inventory, etc.. The problem is, is that there's a file for all the inventory items, and a file for all the price codes ( which is all the parts with a code at the end to specify the customers price). That makes it hard to give certain customers different prices, because then to start a new price code for a customer, we have to duplicate every part in the inventory and add a new code, and change all new prices.

Now, I've been working on a new system, that has all inventory in ONE file, and different fields for each price code. So if we want a new price code for a customer, i just add a new field. I just can't figure out how to pull a price from the inventory for a part, according to which price code the customer has specified in the customer database. (example) customer code = code1 (cust. dat.), then use price code1 (inv. dat. code1 field) for part#4.

Does this make sense? Maybe there's is an easier, more efficient approach that would work better!?

 

[Schlogg]

Do I read you correctly that you can have, in theory, a different price for every customer for one particular item?
Or does a customer have a single price code for all items?

Paul J. Cheers,
Paul J.

 

[srammij]

Well, not really, there are a couple price codes, that different customers have, so maybe 5 different codes, but different customers are asigned one of the five codes. Each code will have a price for each inventory item.

So in the inventory, each item will have 5 fields for the prices, code1...code2...
Now in the customer dat., if it says their pricing is code2, then i need for the price to be pulled from code2 field for that item#.

Does this make sense?

 

[Schlogg]

It makes sense to me but is what I see what you see?

This sounds too simple - it probably has a major flaw which will hit me at 3.00am.
Not sure where/when you get the item code but here goes.

Cust Item Price = Case(Price Code = 1, ItemPrice1,
Price Code = 2, ItemPrice2,
Price Code = 3, ItemPrice3, .... etc.)

If you have the info, write the Price Code sequence to test the most frequent code first.

Of course it isn't as simple as that. Assuming you have the Item # to start with, you need a relationship to the Item file. "ItemPrice1" then becomes "Items::ItemPrice1" and so on.

HTH
Paul J.


Cheers,
Paul J.