Updating Access 2000 Database

[Bigwoofa]

I am creating a database for my brother who is in sales. At first I thought
he had a set UnitPrice list for all customers, but as it turns out, he
charges some customers for certain items more or less than he does for
others. My first thought is to have some kind of update where he can input
the customer name and create a changeable price list for each customer. When
completing a form, he would be prompted to enter the customer name, whereas
that customer's price list would appear or become active. Any products he
includes in that order would be assigned the apprpiate UnitPrice pertaining
to that customer.

My problems are:

1) I am still fairly new to Access, but I am learning quickly
2) How can I create an updateable customer table and assig a changeable
price list table for each customer
3)How would I set up a query/form to be sure the right price list is being
used?

Any help you could lend would be awesome.

Thanks

 

[Tom Wickerath]

How can I create an updateable customer table and assig a changeable

price list table for each customer

What you are describing is a many-to-many relationship between customers and
products, with the current selling price being included in a junction table.
This seems like it would quickly become a maintenance headache to me. Would
your brother be happy with a discount field instead? This could be in an
Order Details table, much like what is shown in the Northwind.mdb sample
database.

Find a copy of Northwind.mdb (it's likely already installed on your hard
drive) and then open the Orders form. A subform includes the ability to add
order detail records, with the ability to add a discount to each record.
Would that work for your brother?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

First suggestion. Instead of random prices, he give discounts or premiums to
a standard price. Then you could store the percentage with the customer and
the price discount could be applied uniformly. You can round up or down to
any amount from 1¢ to $1000.

If that isn't the way he wants to do business, You need a Customer table a
Product table (without prices) and a Join table CustomerProducts like the
following:

tblCustomerProducts
CustomerID - PK - Joined to CustomerID in tblCustomers
ProductID - PK - Joined to ProductID in tblProducts
PriceDate
Price

If you want history, you'll need to add the PriceDate to the compound
primary key.

The orders are filled by entering the CustomerID in the main form, then
filling a subform by using the ProductID and Price supplied by a combo box
with data from the Join table.

 

[Bigwoofa]

Thanks Tom. It was giving me a headache, that's why I tried posting. I have
been studying the Northwind sample for ideas. The Discounts just might work.
I didn't think about that eariler, because I didn't know upfront that he
charged customers differently.

Thatnks for the advice.