Changing prices

[Guest]

I have a table which includes a field for Date of service, a field for Test
Code and a field for no. of services., and a second table which has these 3
fields , Test Code, Test Code name and unit price.

Depending on the date of service, unit price varies, , eg Test Code M123
before May 1, $105, May 1 and after $95.

I am trying to build a summation query that can pick out the correct unit
price.
something like group by TEST CODE, sum(unit price * # of services). I get
to the point of adding an extra colum in the second table for
lastModifiedDate, so for eg M123 has 2 records
Record ### M123 $95 May 1,2007
Record ### M123 $ 105 Nov 1, 2006

I have strucked at how to set up the query to pick the correct price
depending on the Date of service on the first table.

Can someone please help?

Thanks!

 

[Allen Browne]

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

It may not be quite as obvious as you expected, but it's worth persuing.