Access 2003. Count text from records of checked boxes.

[J Hall]

Here is one I've been struggling with for a couple of weeks.

On a report I have a query that has records with both Check box fields and Test Fields.

I was able to get a sum of all the records in that query that had the text "GAMC" in the two text fields [Pl Ri Ki] & [Pl Lt Ki] by using

=Sum(IIf([Pl Rt Ki]="GAMC",1,0))+S um(IIf([Pl Lt Ki]="GAMC",1,0) )

Now I want to know how may of that text from the two text fields are on the records of two check fields.

From Column A (check box yes) and Column B (check box yes), how many times was "GAMC" listed in the text fields {Pl Rt Ki} & {Pl Lt Ki}.

Hope that makes sense and is it doable?

Thanks again,
I do try to research and figure things out on my own but today I just had to reach out on a few items.

J

[liimra]

Solution/

You are welcome.
One easy approach is to add another field and add condition there.

Suppose the name of the field with the first expression is "FieldA"
First Expression:

sum(IIf([A]=0 And [b]=0,0,IIf([A]=0 And [b]=-1,0,IIf([A]=-1 And [b]=0,0,"1"))))

and the field of the second expression is "FieldB"
Second Expression:

Sum(IIf([Pl Rt Ki]="GAMC",1,0))+Sum(IIf([Pl Lt Ki]="GAMC",1,0))

Then the expression in the third field would be:

Sum(IIF(FieldA="1" And FieldB="2","1",0))

Regards,
Ali

[J Hall]

Well,
I've been trying for a couple of days, but I'm just not getting this one.

I'm working expressions from the report. I have FieldA and FieldB set up.

Now not to sure about third field. I tried entering the field name in your example for FieldA and FieldB and I tried entering the expression for FieldA and FieldB. I guess I'm not sure what goes in the FieldA and FieldB spots.

Hope that make sense. Been looking in a big book for help and loaded a disk of examples and couldn't find any help.

Thanks again and again,
J

[beacon]

Hi J,

I think you're trying to do too much in a single query. If you can create a query that shows that both check boxes are equal to yes and the other field is equal to "GAMC", then you it would be super easy to create another query, that includes the first query, and just do a count on the expression you created in the first query.

Does that make sense?

Sometimes it's not pretty, but it is effective...som ething I've had to learn the hard way over the years. My rule of thumb, which others may disagree with, has been, if you have to obtain the total number of records for a situation that relies on certain conditional statements, then it's best to break it down into pieces and use subqueries to arrive at the total.

Hope this helps...

beacon

[J Hall]

Thanks for your reply,
As a very new user of Access,I guess my current homework is to learn more about subqueries. I'll be studying.

[beacon]

As a very new user of Access,I guess my current homework is to learn more about subqueries. I'll be studying.

It's fairly easy...I'll try to show you what I mean using your question above.

It sounds like you've already created this query, but in case you haven't, create a query that includes the fields Column A, Column B, and the fields that have "GAMC" in it (the "[Pl Lt Ki]" and "[Pl Rt Ki]" fields).

Create the expression from your first post (I'm going to call this one "GAMC Test"). Once you've created that one, create the following expression (I'm calling this one "GAMC Flag")...be sure to replace the field names I've used with the actual field names:

=IIf(([Column A] = True) And ([Column B] = True) And ([GAMC Test] = 1), 1, 0)

The last expression should flag each record that has a yes for both check boxes and GAMC in either of the two text fields.

Now, save and close that query. Create a new query and add the query that you just saved and closed to this query. Add the "GAMC Flag" to the fields, right-click it, click on "Totals", and then change the total to "Count". Then, run the query. It should have a count of all the records that you need.

Hope this helps...

beacon

[liimra]

Solution/

Please find what I explained/You need in the attached database. Open the tables to see the records (checked boxes, gamtype..) and then open the report to see how it works. After you are done, try to create the same from scratch and you will become very familiar with writing conditional expressions.


Regards,
Ali

[liimra]

Did you work it out?

Regards,
Ali