473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access 2003. Count text from records of checked boxes.

6
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))+Sum(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
Aug 29 '10 #1
7 2258
liimra
119 100+
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:
Expand|Select|Wrap|Line Numbers
  1. sum(IIf([A]=0 And [b]=0,0,IIf([A]=0 And [b]=-1,0,IIf([A]=-1 And [b]=0,0,"1"))))
  2.  
and the field of the second expression is "FieldB"
Second Expression:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Sum(IIF(FieldA="1" And FieldB="2","1",0))
Regards,
Ali
Aug 29 '10 #2
J Hall
6
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
Sep 1 '10 #3
beacon
579 512MB
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...something 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
Sep 1 '10 #4
J Hall
6
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.
Sep 1 '10 #5
beacon
579 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. =IIf(([Column A] = True) And ([Column B] = True) And ([GAMC Test] = 1), 1, 0)
  2.  
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
Sep 1 '10 #6
liimra
119 100+
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
Attached Files
File Type: zip Boxes.zip (23.5 KB, 96 views)
Sep 1 '10 #7
liimra
119 100+
Did you work it out?

Regards,
Ali
Sep 16 '10 #8

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

Similar topics

5
by: j.mandala | last post by:
Someone is trying to run my Access 2002 database under Access 2003. He has had a number of problems: 1) i used the MSComCt2.ocx for it's Date and Time picker. I can't find it under 2003. Do I...
7
by: Killer42 | last post by:
Hi all. I dabble with Access, but haven't done anything in-depth for a number of years. What I want to do now is probably a fairly simple JOIN or something, but I just can't recall how to go about...
13
by: Ulv | last post by:
I have a table (TblItems) with fields Lenght, Width and Height as decimalfields. I also have the fields Desc, a text field. In a form I have created this line of code after updating: Desc =...
6
by: mahowe | last post by:
Hi, I have had this problem for a while and have not been able solve it. What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission...
1
by: Bobby | last post by:
Hi I am using Access 2003 mdb as a front end to an application which uses SQL Server 2000 as the backend. The two are connected using ODBC. On one particular table (the Stock table), I have a...
6
by: stuart | last post by:
I have 2 users who ran into a problem with a data entry program (written in Access 2003). One user was keying into one of the forms when she got the message "ACCESS Error Number: 3218 Could not...
8
by: Irene | last post by:
Hi, I have an MS Access Database with 1 Table containing about 2 million records in Unicode (diferent languages). I would like to export the Table to a Text file (CSV, Tab, etc.) Access...
0
by: TheMightyZog | last post by:
Hello, I wonder if anybody can throw some light on the following problem. In 2006, I coded a form with RichtextCtrl controls, which displayed formatted text from a memo database field. I...
1
by: NLR2008 | last post by:
Hi there, Can anybody help me and provide me with a SIMPLE solution to create a search form in Access 2003. I have created a database for Finance Payments and want to enable the user to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.