Need a lookup querry

[Emma Chapman 123]

Hi

I have looked at a reply from Allen Browne to someone else but still can't
get my head around the querry.

Please can somebody help me with a querry for this. I have a table which
shows each members test results for each month. On a report that i am
building i need to show the price that the member would get for that
particular test result each month. For example If the result is between 0 &
50 they will receive 0 deductions. If it is between 51 & 60 they will receive
-0.3 pence. There are 23 different bandings for this result.
On my report i need to show it like this below:
200711 200712 200801 200802 200803 200804 200805 200806 200807
Price Price Price Price Price Price Price
Price Price

The actual test results are shown at the top of the report. Each member has
there own report to show the results and prices.

I think that i need a querry to do this but i don't know where to start. If
i was in excel i would use a lookup querry that looks up the bandings table
and looks for the value.

My first table (Bacto 12 month) looks like this
Member Number 200711 200712 200801 200802 200803 200804
016832301 50 69 53 50 53
49

My second table (bacto schedule) looks like this
Bacto ppl
51 -0.3
61 -0.6
71 -0.9
81 -1.2
91 -1.5
101 -1.8
111 -2.1

Please could somebosy help me.

Thanks

Emma

 

[Clifford Bass]

Hi Emma,

It would be better if you had what is called a normalized database
where the data was stored down instead of across. Also, it is not a good
idea to use purely numbers for field names. Too easy to confuse with actual
values. So your Bacto table would look like:

Member_Number Test_Date Test_Result
016832301 11/1/2007 50
etc.

If you do not have days, you can always use 1 for the day. And you can
format its display to exclude the day. Anyway, like that you could probably
use a crosstab query to get your results.

However, to address it as is: Add another column to your bacto
schedule table so that you have a lower end and an upper end. Also add
another row for the 0 to 50 range.

Bacto_Start Bacto_End ppl
0 50 0.0
51 60 -0.3
61 70 -0.6
etc.

Then your query might look something like this (untested):

select DLookUp("[ppl]","[bacto schedule]",[200711] & " >= [Bacto_Start] and
" & [200711] & "<= [Bacto_End]") as [Price_200711], DLookUp("[ppl]","[bacto
schedule]",[200712] & " >= [Bacto_Start] and " & [200712] & "<= [Bacto_End]")
as [Price_200712], .....
from [yourtable]

Or maybe (also untested):

select A.ppl as Price_200711, B.ppl as Price_200712, C.ppl as Price_200801,
....
from [yourtable], [bacto schedule] as A, [bacto schedule] as B, [bacto
schedule] as C, ......
where [200711] between A.Bacto_Start and A.Bacto_End and [200712] between
B.Bacto_Start and B.Bacto_End and [200801] between C.Bacto_Start and
C.Bacto_End and .....

Hope that helps,

Clifford Bass