many thanks for the very useful information

After that What do I do??

Hi Peter

The proper expected output would be something like this:

ServiceName          Dementia            Hospital   Rest home        Total

Dementia                    0                    63          2                      65            

Hospital                      4                    21           6                     31

Rest Home                  5                    10            0                    15

Hi Gail Shaw If you look at the problem carefully...

Hi

Thanks guys..It worked perfectly!!

Hi

thanks for yor query.

I have given it a go but its taking forever to show the results. My table is a huge one with 4 million rows in it....

Good to know that!!!

Thanks for that Jeff

It works perfectly!!!

Keep up the good work

Cheers

Hi Peter

Thanks for your query.I ran the above query but I am gettin the following erro message:

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,...

Hi

Yes Patient_ID is an incrementing sequence number

Basically, the order of records is through a different field called the patient_id which I have not included in my sample data...

Thanks

Hi Guys

I am sorry if I could not explain my problem correctly.

The sample which I posted for my table has got dates in the ascending order which is just by...

Hi Jeff

The first entry of start date for an NHI becomes the end_date and last entry of start date of that NHI becomes the start date

Hope this helps

Hi Jeff

Thanks for your help but this problem is not as straight forward as it sounds.The dates are not in any particular order(asc or desc)Which means I have to pick...

Hi

Basicallly I have connected SQL as back end and MS Access as Front end

This is the current situation.

Thanks

Mita

Could you help me in that??

what should I write in the report parameter window??