Insurance Carrier list printed

[Craig-DMA]

Is there a way to get the Insurance carrier list printed? maybe by a query?
Basically if you select list at the top then insurance carriers I need that list printed out and can't find a query that lists this..

[Jorgebon]

Run this query and then print the results:

Select carrier.carriername, carrier.Address, carrier.Address2, carrier.City, carrier.State, carrier.Zip, carrier.Phone, carrier.ElectID from carrier
where carrier.ishidden = 0

[Craig-DMA]

Almost perfect but I also need the number of ppl on each carrier as well

[Arna]

The built in Insurance Plan List in the Reports window will list all your plans and carriers. You'll need a query for a count of subscribers for each plan.

[Craig-DMA]

That list just lists out each patient. I just want the number in each like its listed in the lists/insurance carriers

[Tom Zaccaria]

Try this:

/*122*/ SELECT carrier.CarrierName, COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM carrier
INNER JOIN insplan ip ON carrier.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.PlanNum=ip.PlanNum
INNER JOIN patplan pp ON pp.InsSubNum=ib.InsSubNum
INNER JOIN patient p ON pp.PatNum=p.PatNum
WHERE p.PatStatus=0
GROUP BY CarrierName
ORDER BY CarrierName;

drtmz

[Jorgebon]

If you combine both queries you can get all the info:

SELECT carrier.CarrierName, COUNT(DISTINCT p.PatNum) AS 'Patients',carrier.Address, carrier.Address2, carrier.City, carrier.State, carrier.Zip, carrier.Phone, carrier.ElectID
FROM carrier
INNER JOIN insplan ip ON carrier.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.PlanNum=ip.PlanNum
INNER JOIN patplan pp ON pp.InsSubNum=ib.InsSubNum
INNER JOIN patient p ON pp.PatNum=p.PatNum
WHERE p.PatStatus=0
AND carrier.ishidden = 0
GROUP BY CarrierName
ORDER BY CarrierName;

[Craig-DMA]

I saw that one in the examples already, I appreciate all the help but If I could combine that query and one that adds the carrier info it'd be great but I'm having a hard time writing this sql. It's a bit of a learning curve if you havent done much with it. I thought I could just add carrier.address, carrier.city etc but I always get errors and I just dont understand

[Craig-DMA]

If you combine both queries you can get all the info:

SELECT carrier.CarrierName, COUNT(DISTINCT p.PatNum) AS 'Patients',carrier.Address, carrier.Address2, carrier.City, carrier.State, carrier.Zip, carrier.Phone, carrier.ElectID
FROM carrier
INNER JOIN insplan ip ON carrier.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.PlanNum=ip.PlanNum
INNER JOIN patplan pp ON pp.InsSubNum=ib.InsSubNum
INNER JOIN patient p ON pp.PatNum=p.PatNum
WHERE p.PatStatus=0
AND carrier.ishidden = 0
GROUP BY CarrierName
ORDER BY CarrierName;

I missed this reply. Thank you so much!!!! You're seriously the best!