Hi



Can anyone help me with a multiple lookup I need

I have a table of services users who have been clustered onto a treatment category when they are reviewed

It looks like this


NHS No Review Date Cluster No
1234 15/01/11 15
1234 23/02/11 16


I also have a table showing the activity a service user has had on a particular date

Date Activity Type NHS No
17/01/11 IP 1234
07/03/11 DC 1234


I want to attach the cluster no to the activity table but it need to look at the NHS no and date if the date is after the review date in the cluster table use that cluster no

It would look like this

Date Activity Type NHS No Cluster No
17/01/11 IP 1234 15
07/03/11 DC 1234 16

Hope this makes sense

Thanks for your continued help

Andy


Andrew Gill
Financial/Systems Accountant