Urgent!Please help :(

[RunLikeaRiver]

Hi Friends,

I am not very gud in MS Access.Please check out the below example and help me.
Product_name Price
P1 $20
P2 $10
P3 $5.......

Mon Week_no Prod_name No_of items
1 1 P1 4
1 4 P1 10
2 4 P1 20
3 4 P1 10
2 3 P2 50........

Assume that Price of P1 changed to $40 in Feb.
Now I want to calculate amount earned by selling P1 from Jan to March.
Remember (For Jan the rate will be $20 and from Feb to March it will be $40)
If I calculate manually it will be - 4*$20 + 10*$20 + 20*$40 + 10*$40

But How do I do it using a query or code??

 

[GaryPanic]

Cannot help you - but phrase your question slightly better and you will get a half way decent answer-- try to show you have approached this
so I am thinking out loud here
date range from and to

so if P1 between 01/01/07 and 31/01/07 (UK style dates) then P1 = x
if P1 between 01/02/07 and 31/03/07 then P1 =y

now an easy way of doing this would be as a one off - have your data have the costing fields P1value
and run a qry to update this value
sing the if's above if < than 31/01/07 then value = 20 if >01/02/07 then value=40

it does depend on how many times you are going to be doing this

the logical route to this is when the price changes change the P1 value to its new value and from that point onwards whenever P1 is selected the new value will appear in its value box

rather going backwards do it "live" ie new prices come in on a set day then update you tables to refelct this

 

[namliam]

Please just ask questions once... http://www.access-programmers.co.uk/forums/showthread.php?t=123476

 

[GaryPanic]

looking at my post - see how many typo you can see ???