>ID Visit Date Primary Insurance Age >___ __________ _________________ ___ >1 12/19/00 BC 12 >1 12/20/00 BC 12 >1 12/21/00 BC 12 >2 12/18/00 MC 3 >2 12/19/00 MC 3 >3 12/17/00 007 24 >4 12/17/00 BC 15 >4 12/18/00 BC 15 >4 12/19/00 BC 15 >5 12/18/00 BC 36 >5 12/19/00 BC 36 >>The Report looks like this:
>BC > Children: > 1 12/19/00 > 1 12/20/00 > 1 12/21/00 > 4 12/17/00 > 4 12/18/00 > 4 12/19/00 > Total Children (visits): 6 > > Adults: > 5 12/18/00 > 5 12/19/00 > Total Adults (visits): 2 > >Total BC Visits: 8 >>etc...
select Primary, sum(iif(age<18,1,0)) as ChildVisits, ; sum(iif(age>=18,1,0)) as AdultVisits; from Visits ; group by Primary, ; into cursor curTotal select * from Visits inner join curTotal on Visits.Primary=curTotal.Primary ; into cursor curFinal && Here I'm not 100% sure, you may use Lookup function insteadNow you have all data, and designing report would be very easy.