Create an Append Query

[Gusio]

I have the following table that I would like to Append. dbo.InvPrice

It contains the following fields: StockCode, PriceCode, SellingPrice. Everytime we create new Stock Codes I need to update the price table. Each new stock code will have different pricing for different PriceCodes. For example lower case a to k (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o) We would like to create and update or append query to automatically add the pricing. Each PriceCode has it's own discount percentage which are listed in the second table.

dbo.Pricing



PriceCode, Discount.
a 30
b 60
c 55

All discounts are based on Retail price. The append query should contain a parameters for Stock Code and Retail price. So the query will add records into dbo.InvPrice Table where pricing will be calculated based on Prompt entered Retail and discount from the other table.

Any advice how to create this query?

Thanks

[ItsMe]

Everytime we create new Stock Codes I need to update the price table

Is it not feasible to calculate the discounts on the fly? When I hear, "...or append query to automatically add the pricing...", I think there may be a better approach. It may be appropriate to have tables with calculated fields that store the discounts. Maybe if there are thousands or 10's of thousands of calculations necessary to create a single sales order or quote.

Building a query object to UPDATE the field that has the calculation may be the way to go. I don't understand the need to append. The append process should be separate. Get a new product or a new category; append the table. Need to adjust the calculated field; run an UPDATE query.

[Gusio]

Just to be more precise I have the two tables for now:
Table one contains stock codes and Discounts

PriceCode	PriceDiscount
a	30
b	45
c	50
d	33
e	25
f	31
k1	32
k2	33
k3	34

Table 2 Contains Pricing information

StockCode	PriceCode	SellingPrice	PriceBasis	CommissionCode	TimeStamp
0110100153	a	0	S	0
0110100153	b	0	S	0
0110100153	c	0.45	S	0
0110100153	d	0.45	S	00
0110100153	f	0.45	S	00
0110100153	k1	0.45	S	00
0110100153	k2	0.35	S	00
0110100153	k2	0.69	S	00
0110100153	k4	0.45	S	00

Sales Department will supply us with a retail price base on which we need to calculate pricing for each pricecode. Retail price * PriceDiscount = price
Price Discount is in my first table.

Once all calculated it needs to be appended to my dbo.InvPrice table. Do I need to create an update and append queries?

[ItsMe]

If I were tasked with building the DB I would ask when you need to know the price. Is it when you create a quote, a sales order, or an invoice? Or do you need to provide customers with a full price sheet that illustrates their discounted price for each item?

I would consider it rare that there would be a need to store the discounted price in the DB. It would be a best practice to store the Retail Price and the formula for discounts. Then, when you need to quote a customer, apply the formula and calculate their price. It boils down to performance of the DB and how much work up front and how much work to maintain. It is generally easier to maintain the retail price. Ask the questions, analyze the pros and cons.

If you want to store the adjusted price or calculated value, you are going to need a junction table. Table 2 looks like the beginning of a junction table. The idea is you have a table of stock items, one record for each stock code. You will have a table for the Price Code, one record for each price code. Then the junction table will have all of the possible combinations. The junction table will have multiple records for a single stock item.

Whatever you decide on, I would suggest you add an autonumber field for the PK in each table. Normally, things like Product Codes, Part Numbers, and serial numbers do not perform very well as primary keys values.