Best ways to update date

[quacka]

Hello all,

I am creating a database with prices per customer per product.
Let's say 100 customers, 1000 products, max 10 prices per product.
The database is working as I want. But now I do need to find a way to updates prices. This updating needs to be done partly manually, partly automatically.

Example:
(c=customer, p=product)
c1 p1 50
c1 p2 500
c1 p3 1000
c2 p1 55
c2 p2 500
c2 p3 1050
c3 p1 55
c3 p2 500
c3 p3 1050
c4 p1 50
c4 p2 550
c4 p3 1100

So I have the following values
p1 50 55
p2 500 550
p3 1000 1050 1100

When someone manually updates the values of "50 and 55" to "55 and 60" for p1, it must be automatically updated for all customers.
I gives me a headache for weeks.... I cannot find any way to create something that is workable.

First I thought to create a list (crosstab-query) with all products with columns for different prices. But that is not working: it creates a column for each customer, in stead of each different value. And besides that problem it isn't updatable. So that doesn't work.

Second try was creating a form, with 2 subforms.
In het mainform I could choose 2 customers (the reference and the customer I wanted to change). In the first subform I would get all prices for the to be changed customer, with an extra column for new prices.
I can type in new prices for that customer. So this works.
So exemple: I change the prices of 'C1'. So 'C1' has for each product an 'price' and a 'new price'.
After that I choose 'C2', with as reference 'C1'.
In the first subform I again get the price of 'C2', with an empty 'new prices'-column. In the second subform I get the prices of 'C1', with old and new prices.

And after that I want something to be done, what I cannot program / I don't know any ways to program this:
I wanted now to create a button that copies the values of the field 'new prices' of the second subform to the field 'new prices' of the first subform.
If it would have worked now 'C1' and 'C2' would have the same values for 'new prices'.
Now I manually only need to change the values that are different for 'old prices'.

But I do think I am creating something what isn't necessary. Maybe someone can give me some clues how to start.
Do I need to do it with a form, with queries, or do I need to do it partly in other programs (excel by example)?

Thanks for your help, and please ask if I wasnt clearly enough.

 

[jzwp22]

I'm not sure your table structure is correct which might be at the root of your problem. Can you explain in more detail about what this database is suppose to do? I'm curious as to why there can be many prices for a product?

 

[quacka]

I'm not sure your table structure is correct which might be at the root of your problem. Can you explain in more detail about what this database is suppose to do? I'm curious as to why there can be many prices for a product?

Different volumes -> different prices. A big customer can negotiate better prices.

The reason I created the database was that the current way of working is that 1 person manually writes new prices on a paper. The second types that prices in a word-document (sended to customers), the third person types it in excel and transfers the data in our real system. And this for every customer....
A lot of work.

In my system I import/change prices one time. After that automatically a file for the customer is created and a file to be transfered in our system.
I now only need to find the best way to change the prices.

 

[jzwp22]

The best way to do an update is to use an UPDATE query. The trick is to set the criteria appropriately to change only the records you want, in the order you want. In your original post you wanted to change the 50 and 55 to 55 and 60 respectively for p1. Given this as your table structure:

(c=customer, p=product)
c1 p1 50
c1 p2 500
c1 p3 1000
c2 p1 55
c2 p2 500
c2 p3 1050
c3 p1 55
c3 p2 500
c3 p3 1050
c4 p1 50
c4 p2 550
c4 p3 1100

It is a good practice to always make a backup of your DB before running global update or delete queries especially when you are first developing the DB.

1st update query
UPDATE tablename SET pricefield=60
WHERE pricefield =55 and productfield ="p1"

2nd update query:
UPDATE tablename SET pricefield=55
WHERE pricefield=50 and productfield="p1"

In terms of your application which sounds like you are producing quotes for customers, you can have your users record everything in Access and then Access can generate a Word document to be sent to the customer and then export data in an Excel format to your primary application. But for that to be successful, you will need to work on your table structure.

This structure should help get you started:

tblCustomers
pkCustomerID primary key, autonumber
txtCustomerName
txtCustomerAddr
other fields

tblProducts
pkProdID primary key, autonumber
txtProdNumber
txtProdName

tblProdPrices
pkProdPricesID primary key, autonumber
fkProdID foreign key-->tblProducts
longQTYbreakpoint (max quantity for this price & product)
currPrice

tblCustQuotes
pkCustQuoteID primary key, autonumber
fkCustID foreign key->tblCustomers
txtQuoteNumber
dteQuoteDate
other quote info

tblQuoteDetails
pkQuoteDetID primary key, autonumber
fkCustQuoteID foreign key-->tblCustQuotes
fkProdID foreign key-->tblProducts
currPrice (you would populate this from the tblProdPrices based on the longQTY)
longQty

The trick with pricing, if you want to increase the prices, you may not want to increase those prices that are part of currently active quotes, so you would want to store the price in the tblQuoteDetails. A price increase would be achieved by running an update query on tblProdPrices that way all future quotes will reflect the new prices.

 

[quacka]

The database is a lot more complicated.

I have a table 'customer'
A table 'product'
A table 'product-family'
A table 'prices'
And another few tables, that are less important, mostly some workarounds for exceptions.

It is only a database for pricelists. So changing a pricelist, printing a version for customer and a layout to be imported in our normal system.
So I don't have to worry about quotation and so on.

I think I will try to someting with the update-code. It gave me some ideas for a solution. Maybe it will work.

 

[jzwp22]

I'm glad the UPDATE query route gave you some ideas to try. Good luck on the project.