Ok.. i used the follownig code to get what i wanted

select  a.agtrefno, a.memindex from membership a

where a.memindex in (select top 5 c.memindex from membership c where c.agtrefno = a.agtrefno...

Thanks Ken, that seems to work a treat!

Did not know you could use sub queries with an in statement to refer to the main query as it were.. you learn...

sincere apologies have foudn the answer..

hi cheers for the reply,

that's what i thought of doing but i wasnt sure whether there was a neater way of doing it than self joining on a date field

appears...

thanks for the feedback guys, i think David's suggestion lokos most promising given the tool i have to use..

ta,

Alex

that's perfect Phil, many thanks

i got as far as doing using round,2  which gave me 3434.350000000000 but then couldn't get any further.

hi guys,

thanks for the responses..

im happyto use anything really, not nec a float. i needto convert a long number such as 12.8634456776 to something that resembles currency (covnerting to...

thanks for the reply!

i think im just gonna make a mapping table as it only has to be for a few numbers.

cheers,

alex

cheers guys, the second one worked a treat.

and remi- almost 3000 posts...! good work!

ah didnt think to use count.. very good thanks!

hi guys thanks so muchfor your replies!!

 Coach James im afriad your suggestion returns 2 values for most of the records and W_Squared yours doesnt appear to work as you havent selected...

Sure ok... table financialperiods:

FY Period  StartDate   EndDate

1    1        01/04/2005  31/04/2005

1     2        01/05/2005  29/05/2005

1    3        30/05/2005    29/06/2006

etc etc

Table Events

Eventnumber Arrangmentnumber StartDate EndDate

1                        1                   01/04/2005  01/04/2005

2                         1                   02/04/2005   03/04/2005

3                         1                     01/04/2005   02/04/2005

4                          2                   ...

well you lost me pretty early on there!

i guess that's why i always have the problems, and you always have the answers haha!

cheers remi.. this doesnt appear to work all too well though but i think that's down to my poor explanation.

its part of a report writing tool.. basically this particular query...

i can always rely on you remi ;-)))