normalize my table - track price levels

[SamDeMan]

i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.

i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.

i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.

i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.

All Help will be appreciated.

I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.

thanks,

sam

 

[SamDeMan]

thanks for replying.

it sounds like you understood my dilemma.

first, i am not sure, will this technique work for mulit ple updates. i was actually thinking of doing this, but i wasn't sure if this will work even for many price updates.

second, i think i have already designed queries as you describe. usually access will name the second one with _1 like myTbl and myTbl_1. unless you were saying that design view will not work on the left join in a case like this.

i will test your solution tomorow morning, and i will update this posting.


thanks,

sam

 

[SamDeMan]

amazing!!!!

it worked. i am working on this all day. not the join thing, just putting together the reports i need. this solution rocks. i will apply this to other applications.

the short commings:

1. i needed to put an ending date for rates that are current. so i pushed them till 2007. i would have preffered to leave them empty.

2. the join is not viewable in access, so i am constantly copy/paste ing the join. you did warn me about this.

thanks a lot,

i just joined these groups, seems to work better than the microsoft groups.


sam

 

[qqq]

tricky case of nested subqueries?

hello there, I've got an other tricky issue of similar kind and would appreciate any help:
I got 2 tables for keeping track of working time and working costs of different people:
t1 lists daily salary costs for each person after a certain date (fields: person, startdate, salary).
t2 has one row for each day / person combination; this is continuously filled by people tracking their working hours (fields: person, workdate, workinghours).
To calculate work costs for each row in t2 I added a field called "salary": this should have the respective daily salary filled in from t1.
I understand the logical link for an update query populating the new field salary in t2 should be something like: UPDATE t2, t1 SET t2.salary = t1.salary WHERE t1.startdate<=t2.workdate AND t1.person = t2.person; now the missing part: how to I tell it to use exactly that value from t1.salary where the startdate is the latest among those earlier than the workdate?
Should this work with something like "WHERE startdate= (SELECT max(stardate)...)"?
Thanks for any assistance!

 

[SamDeMan]

sorry for the delay, but i am wondering if this was resolved. did you try "where startdate = max(startdate) etc.. without using select

sam