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

Count checkboxes and text fields

P: 15
Hi All,

I am trying to set up a report based on a Select Query. I need to do a count based on four different fields.

Field 1: Product Type (text field - 5 different products)
Field 2: Claim type (text field - 2 different claim types)
Field 3. ysnApproved (Checkbox)
Field 4: ysnNonComp (Checkbox)

I am trying to get a count of each product that has a certain claim type and if checked approved. A count of each product that has a certain claim type if checked NonComp. This is what I am using in my query.

Sum(IIf([strProduct]="Product1" And [strtypeofclaim]="IL" And [ysnApproved]=True,1,0)). This is to get the products with type of IL and Approved box checked..

Sum(IIf([strProduct]="Product1" And [strtypeofclaim]="IL" And [ysnNonComp]="True",1,0)). To get the number of products with type of IL and NonComp box checked.

I need to get a count for each product with each type of claim and if it was approved or noncomp. Again there are five different products, and two different types of claims.

When I run a test of the query it is not giving me the correct count.

Any help would be appreciated.
BeaBea
Aug 20 '09 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,287
I believe you can create 2 queries, then join them together on product and claim type for the source of your report.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Product Type], [ClaimType], Count([anyField]) AS CountOfApproved
  2. FROM myTable 
  3. WHERE [ysnApproved] = Yes
  4. GROUP BY [Product Type], [Claim Type]
Aug 20 '09 #2

P: 15
I tried doing two different queries.

First query: Selected, product (group by), claim type (group by), ysnApproved (Where and Yes in criteria row), ysnNonComp (Where and Yes in criteria row). I then joined this query to my existing query but it did not count the records correctly.

This is what I want my report to look like, if this helps:

IL Approved IL NonComp NewApproved NewNoncomp

Product 1 1 1 1 1
Product 2 10 10 0 10

Meaning there was 1 product 1, type IL that was approved, and so forth.

Thank you for any help you can give me.
Aug 20 '09 #3

Expert 100+
P: 1,287
The result of your first query is going to show only records where BOTH ysnApproved and ysnNonComp = Yes. This is the reason you need two queries, one for each.
Aug 20 '09 #4

P: 15
I apologize for the frequent postings but I am just not able to get it.

The first query, which I'll call qryProductApproved I used the following:
strProduct (group by), ysnApproved (group by), strtypeofclaim (group by) and ysnApproved (where and then in the criteria row I have yes).

The second query, which I'll call qryProductNonComp I used the following:
strProduct (group by), ysnNonComp (group by), strtypeofclaim (group by) and ysnNonComp (where and then in the criteria row I have yes).

I then did another query called qryProducts and added the two queries with the following fields:

strProduct (group by), strtypeofclaim (group by), TypeofClaim: Sum(IIf([strtypeofclaim]="IL",1,0)) as an expression, TypeofClaim2: Sum(IIf([strtypeofclaim]="OCM",1,0)) as an expression, ysnApproved (group by from the qryProductApproved query) and ysnNonComp (group by from the qryProductApproved query).

The results that I get when testing the querty is: the product, the type of claim and approved check box and a noncomp checkbox. It does not tally the numbers it only gives me a check box next to each product and type of claim.

The report I am trying to get should show the product, the type of claim and how many approved/noncomp records for each.

I am doing something wrong but not quite sure what.

Again, thank you for your help.
Aug 21 '09 #5

Expert 100+
P: 1,287
The best way I can see to do this is to use two queries like the one I posted. Did it not work for you?
Aug 21 '09 #6

P: 15
Chip,

Sorry for the long delay. For some reason I was not able to get the two queries to work. Beacuase either one of the ysnApproved or the ysnNonComp fields need to be checked, I set the query up with a between date and used Sum(IIf([strTypeofClaim]="IL" And [ysnApproved]=True,1,0)) and Sum(IIf([strTypeofClaim]="IL" And [ysnApproved]=False,1,0)). This worked on the report.

Thank you so very much for your help. I will be using your suggestions to practice creating 2 queries and join them together so that I'll know how to do it in the future.
Aug 27 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.