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

Multi field value cross sum

P: 16
Hi, i have a problem in ms access form.
I have two fields
1) Gender = male or female values
and
2) Country = London or Germany
Now i want to add few boxes in form which can display
1) Total number of female from London
2) Total number of female from Germany and
3) Total number of male from London
4) Total number of male from Germany
I am a teacher of sociology and don't know how to write a vb code. I tried an expression in control source of the text boxe -
=Sum(IIf([Gender]= "Male" & [City]= "London"))
But this expression didn't provide me total numbers of Male from London.
Please help, i want to use this form to help my student in their project.
Please healp.
May 24 '18 #1
Share this Question
Share on Google+
21 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,032
Jugnu,

We would love to help, but it is difficult to assist when we don't know how you have your data structured in your project.

In general, I think what you are asking is relatively simple, but we are unable to provide an answer without some further information from you.

How is your data stored? Do you have a record for each individual, indicating in each record whether the person is Male/Female and where they are from?

This additional information will help us guide you to a solution.

Thanks.
May 24 '18 #2

P: 16
Hi twinnyfo,
Thanks for your kind reply.
I have a table in database where these are columns-
1) Student name
2) Gender
3) DOB
4) Country
5) Course
6) Home Address, in home address we only put country name like London or Germany.
We record for each Student name, indicating in each record whether the person is Male/Female and where they are from.
Now we want to count -
1) how many females are from London
2) how many females are from Germany
3) how many males are from London
4) how many males are from Germany.
I don't know how to write a vb code or expression for this, please help.
Many regards for your outstanding support.
May 24 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,032
You can get the results of what you are looking for using a Cross Tab Query, which would create two columns (one for male and one for female) and as many rows as you have different countries.

If you need to put this into a form, you can (and I would recommend as a contiuous form). It just depends on what you are using the data for.

Again, not much information has been provided.
May 24 '18 #4

NeoPa
Expert Mod 15k+
P: 31,186
Hi again Jugnu.

If you put controls in the Header or Footer sections of your Form that have the following expressions they should give you what you're looking for. I'm not sure if the Form needs to be in Continuous Forms mode or not but it certainly should work if it is.
Expand|Select|Wrap|Line Numbers
  1. [txtEngMal] = "=Sum(IIf(([Country]='England') AND ([Gender]='Male'),1,0))"
  2. [txtGerFem] = "=Sum(IIf(([Country]='Germany') AND ([Gender]='Female'),1,0))"
I'm sure you can work the others out for yourself ;-)
May 25 '18 #5

P: 16
Hi NeoPa.
Thanks for your reply. We tried those expressions but they didn't work for us. When we put that code into control source it shows error. we just copy and paste that code to avoid any typing error. our text boxes are as yours like txtEngMal and fields are also like Country and Gender. these fields are having combo box for LONDON & GERMANY and MALE & FEMALE respectively.
We are working on a continuous form and our database table is not a split type. We want to add text boxes in footer of form which can automatically show us total number of males and females from different countries. we don't want to create a query table, it will be hard for us to use. please suggest any other expression or vba code for this problem.
thanks a lot for your precious time. I appreciate your selfless efforts, I hope you will provide us an appropriate solution.
May 25 '18 #6

P: 16
Hi twinnyfo,
sorry for incomplete information,we are just learning it.
we are using a continuous form for a database table which is not a split type.
the gender and country fields have combo box like male & female and London & Germany.
we want to put some unbounded text boxes in the footer of a continuous form like txtEngMal and txtEngFem to count total number of males and females which belongs to England and Germany.
we don't want to use a query option for this.
an expression or Vba code will be good for us.
I am asking for your precious time, but it will help my laborious students.
thanks.
May 25 '18 #7

NeoPa
Expert Mod 15k+
P: 31,186
Ah no. You may be right. I needed to make the expressions refer to the Controls on your Form rather than the Fields in your record. Try this instead :
Expand|Select|Wrap|Line Numbers
  1. [txtEngMal] = "=Sum(IIf(([cboCountry]='England') AND ([cboGender]='Male'),1,0))"
  2. [txtGerFem] = "=Sum(IIf(([cboCountry]='Germany') AND ([cboGender]='Female'),1,0))"
Obviously, this assumes you have ComboBoxes named to match the code.

Let me know if this works. I can't test it properly but I think it ought to now.
May 25 '18 #8

P: 16
Hi NeoPa,
Thanks for your precious reply.
The expression code is still not working for us. Our combo boxes for gender and country are named as "GENDER" and "COUNTRY". They do not have a cbo-prefix.
We tried the code without cbo-prefix but it didn't work.
In contry field we have three options
London, Germany and France. But in all records (we have total 50 records) we use only London and Germany.
We are trying to add unbounded text boxes in footer of a continues form. Our unbounded txet boxes are named as -
1) txtEngMal
2) txtEngFem
3) txtGerMal
4) txtGerFem
Please find out us a working expression or VBA code.
Thanks!
May 25 '18 #9

NeoPa
Expert Mod 15k+
P: 31,186
I suggest you change the names of the ComboBoxes so that they are not exactly the same as the field names. This is a daft default that MS have used - particularly as it introduces problems such as this. The names I suggested would be a good place to start.
May 25 '18 #10

P: 16
Hi NeoPa,
We changed the name according to your suggestions. But still we are gating error. Some times we get wrong number of arguments when we change "AND" with "&" or "+" signs.
Really don't know what to do. ��
May 26 '18 #11

P: 16
Hi NeoPa and twinnyfo,
Here is our database table, which looks a like-
Expand|Select|Wrap|Line Numbers
  1. STUDENT NAME | GENDER |   DOB   | COUNTRY | COURSE | HOME ADDRESS
  2. -------------|--------|---------|---------|--------|-------------
  3. ALEX NASH    | MALE   |2/3/1999 | LONDON  | PGDCA  |.........
  4. SIANA JOSH   | FEMALE |3/1/1998 | LONDON  | B.Sc   |.........
  5. RITA         | FEMALE |8/2/2000 | GERMANY | B.Sc.  |.........
  6. BHARAT PATEL | MALE   |7/1/2000 | LONDON  | MBA.   |.........
  7. RANJNA PATEL | FEMALE |3/9/1998 | FRANCE  | Ph.D   |.........
  8. BASH ERICSSON| MALE   |9/3/2000 | LONDON  | PGDCA  |.........
  9. SALINA JATLEY| FEMALE |8/4/2001 | GERMANY | MBA.   |.........
Now we want to know total number of FEMALE or MALE from LONDON or GERMANY or FRANCE or any other country in unbounded text boxes. Which are located in the footer region of a continues form.
Combo box for gender named as "GENDER" (we changed it to cboGender) and has vale text MALE and FEMALE. Combo box for country named as "COUNTRY" (changed as cboCountry).
I think these details are sufficient to get an answer from experts.
Please provide an expression for control source or a VBA code.
Please see it in edit mode because after saving the the format do not looks Like a table.
May 26 '18 #12

NeoPa
Expert Mod 15k+
P: 31,186
I had to knock up an actual test to ensure this worked the way I suggested. I found that it's the Fields, and not the Controls that need to be referred to in the expressions. So, ignore my previous post and go back to the earlier one which was correct. There's no need (It's always a good idea so do it anyway.) to have the Controls named differently from the Fields for this to work.

Here are my expressions :
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf(([Country]='England') And ([Gender]='Male'),1,0))
  2. =Sum(IIf(([Country]='Germany') And ([Gender]='Male'),1,0))
  3. =Sum(IIf(([Country]='England') And ([Gender]='Female'),1,0))
  4. =Sum(IIf(([Country]='Germany') And ([Gender]='Female'),1,0))
Here is a picture of what it looks like when it's opened :

Attached Images
File Type: jpg Jugnu.jpg (86.2 KB, 304 views)
May 26 '18 #13

NeoPa
Expert Mod 15k+
P: 31,186
NB. You may notice this is exactly what I suggested at post #5. You reported that it didn't work, but clearly it does if applied correctly. Please try again and pay careful attention to getting this working before adding anything else to your Form. Once you have it working then you know that if it stops working then it must be down to a recent change.

It may help to know that, even when there are no Controls to show the individual values for [Country] & [Gender] in the Detail Section, the expressions in the Footer Section still work and produce accurate results. I even moved the Footer Controls into the Header Section and they worked there too.
May 26 '18 #14

P: 16
Hi NeoPa,
We really appreciate your efforts and want to thank you.
But some how this expression is not working for us.
We have a different designed form then yours. After filling all the details related to a student and clicking save button, it shows an empty form to fill next student details.
We already added three unbounded text boxes in footer of our form. Two out of 3 uses "SumIIf" formula to count total numbers of MALE and FEMALE, while the 3rd uses "Count" formula to count total number of students.They all work fine for us untill we apply your suggested formula in the new unbounded text box in footer.
It shows error along with the rest three.
We are unable to understand the problem.
Perhaps a VBA code should solve our problem?
May 27 '18 #15

NeoPa
Expert Mod 15k+
P: 31,186
Jugnu:
We have a different designed form then yours. After filling all the details related to a student and clicking save button, it shows an empty form to fill next student details.
That sounds like you have it in Single Form view instead of Continuous as you indicated earlier in the thread. Never mind. It works that way too.

If yours is different from mine then I can only really help if I know how it's different. Why don't you forget about multiple Controls and, for now, just focus on getting one working. The actual formula you're using would be a good place to start. Another useful piece of information would be a picture of all your Form Data properties (It must be legible of course).

NB. Using VBA instead of a working expression is a reliable way to ensure you never learn anything. Problems are there to be understood and overcome. Avoiding them simply ensures they come back and bite you again in future.
May 27 '18 #16

P: 16
Hi NeoPa,
thanks a lot for your support.
here are the pics of form data properties.
please check them and inform us if you need any other information regarding to our database.










we don't know all the technical details necessary to upload pictures. if they do not appear to you then please guide us how to create an URL for images which are save in desktop.
thanks.
May 28 '18 #17

NeoPa
Expert Mod 15k+
P: 31,186
Jugnu:
If they do not appear to you then please guide us how to create an URL for images which are save in desktop.
That isn't possible. If it were then your PC would be cracked in seconds and you'd be in a very bad way.

To use pictures on this website that are from your PC you need to upload the pictures using the Advanced button when posting. The [IMG], or even better the [IMGNOTHUMB], tags are the right thing to use once you have a links to work with that work from a web page which doesn't rely on being run from your PC.
May 28 '18 #18

P: 16
NeoPa
thanks for your kind suggestions.
here we attached some pics of our form properties. through attach files in advanced option.








Attached Images
File Type: jpg dtbs pic 1.jpg (50.8 KB, 262 views)
File Type: jpg dtbs pic 2.jpg (66.6 KB, 268 views)
File Type: jpg dtbs pic 3.jpg (119.0 KB, 262 views)
File Type: jpg dtbs pic 4.jpg (58.8 KB, 260 views)
File Type: jpg dtbs pic 5.jpg (53.1 KB, 257 views)
May 29 '18 #19

P: 16
2nd part of pics is here..
if you need any other information related to our form or database kindly suggest.
thanks :)
Attached Images
File Type: jpg dtbs pic 6.jpg (54.0 KB, 265 views)
File Type: jpg dtbs pic 7.jpg (58.9 KB, 258 views)
File Type: jpg dtbs pic 8.jpg (52.5 KB, 269 views)
May 29 '18 #20

NeoPa
Expert Mod 15k+
P: 31,186
Is [STUDENT DETAILS FORM FORMAT] the table that you showed the contents of in your post #12?

I can't see why it wouldn't work. You can try sending me the Zipped file if you like. I only have 2010 installed ATM so would have to be compatible. I'm also going away for a few days so you'll need to be patient.

If you make your database as small as possible (Make a copy for posting that has only what I need.) and the ZIP it up then you can attach it to a post. If you don't want anyone else to see it then encrypt it with a password and send that to me via PM.

No promises, but I'll do what I can when I get back next week.
May 31 '18 #21

NeoPa
Expert Mod 15k+
P: 31,186
Before you do that though, I suggest you go back through this thread very carefully and check that you've understood and done everything suggested. It will be very disappointing for both of us if I go to all that trouble to check it over only to find you missed something basic. I'll leave it with you for now.
May 31 '18 #22

Post your reply

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