By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

Count Value Across Multiple Fields

P: n/a
I have two fields that draw their combobox values from the same table.

LogBook04.Insurance is the Primary Insurance and LogBook04.SecIns is
the Secondary Insurance. Both draw their values from
tblInsurance.Insurance. The Primary Insurance or the Secondary
Insurance could be any one of the values on the Insurance table.

I'm trying to count the occurances of each type of insurance,
regardless of whether it is primary or secondary.

My goal is to get something like this:

Medicare: 164
Medicaid: 92
Coventry: 24
BCBS: 52

Even though the entries could be Medicare with BCBS or BCBS with no
second insurance. Basically, we're looking to see how many total of
each insurance had to be filed.

Here's my current SQL, but it's only counting instances where the
LogBook04.Insurance == tblInsurance.Insurance. So it's not adding the
second insurance counts.

SELECT Count(tblInsurance.Insurance) AS CountOfInsurance,
tblInsurance.Insurance
FROM tblInsurance INNER JOIN LogBook04 ON tblInsurance.Insurance =
LogBook04.Insurance
WHERE (((LogBook04.Insurance)=[tblinsurance]![insurance])) OR
(((LogBook04.SecIns)=[tblinsurance]![insurance]))
GROUP BY tblInsurance.Insurance;

I've about given up on this, so any help would be greatly appreciated.
Otherwise I'm just going to end up adding by hand. Thanks.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Create two SELECT queries, one for primary insurance and one for secondary
insurance (no aggregate functions at this point). Take the SQL from each and
create a UNION ALL query. Now, run your count query on the UNION query.

A UNION query is a SQL only query, you can't create it in the design grid,
but you can create each of its components using the design grid then switch
to SQL view and copy and paste into the UNION query.

Example:
SELECT ......
UNION ALL
SELECT .....;

The SELECT queries will have to have the related columns line up (i.e. 1st,
2nd, 3rd column).

--
Wayne Morgan
MS Access MVP
"Chris Wolfe" <gr****@thewolfecamp.com> wrote in message
news:2c*************************@posting.google.co m...
I have two fields that draw their combobox values from the same table.

LogBook04.Insurance is the Primary Insurance and LogBook04.SecIns is
the Secondary Insurance. Both draw their values from
tblInsurance.Insurance. The Primary Insurance or the Secondary
Insurance could be any one of the values on the Insurance table.

I'm trying to count the occurances of each type of insurance,
regardless of whether it is primary or secondary.

My goal is to get something like this:

Medicare: 164
Medicaid: 92
Coventry: 24
BCBS: 52

Even though the entries could be Medicare with BCBS or BCBS with no
second insurance. Basically, we're looking to see how many total of
each insurance had to be filed.

Here's my current SQL, but it's only counting instances where the
LogBook04.Insurance == tblInsurance.Insurance. So it's not adding the
second insurance counts.

SELECT Count(tblInsurance.Insurance) AS CountOfInsurance,
tblInsurance.Insurance
FROM tblInsurance INNER JOIN LogBook04 ON tblInsurance.Insurance =
LogBook04.Insurance
WHERE (((LogBook04.Insurance)=[tblinsurance]![insurance])) OR
(((LogBook04.SecIns)=[tblinsurance]![insurance]))
GROUP BY tblInsurance.Insurance;

I've about given up on this, so any help would be greatly appreciated.
Otherwise I'm just going to end up adding by hand. Thanks.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.