Multi field value cross sum

[Jugnu]

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.

[twinnyfo]

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.

[Jugnu]

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.

[twinnyfo]

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.

[NeoPa]

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.

[txtEngMal] = "=Sum(IIf(([Country]='England') AND ([Gender]='Male'),1,0))"
[txtGerFem] = "=Sum(IIf(([Country]='Germany') AND ([Gender]='Female'),1,0))"

I'm sure you can work the others out for yourself ;-)

[Jugnu]

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.

[Jugnu]

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.

[NeoPa]

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 :

[txtEngMal] = "=Sum(IIf(([cboCountry]='England') AND ([cboGender]='Male'),1,0))"
[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.

[Jugnu]

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!

[NeoPa]

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.

[Jugnu]

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. ��

[Jugnu]

Hi NeoPa and twinnyfo,
Here is our database table, which looks a like-

STUDENT NAME | GENDER |   DOB   | COUNTRY | COURSE | HOME ADDRESS
-------------|--------|---------|---------|--------|-------------
ALEX NASH    | MALE   |2/3/1999 | LONDON  | PGDCA  |.........
SIANA JOSH   | FEMALE |3/1/1998 | LONDON  | B.Sc   |.........
RITA         | FEMALE |8/2/2000 | GERMANY | B.Sc.  |.........
BHARAT PATEL | MALE   |7/1/2000 | LONDON  | MBA.   |.........
RANJNA PATEL | FEMALE |3/9/1998 | FRANCE  | Ph.D   |.........
BASH ERICSSON| MALE   |9/3/2000 | LONDON  | PGDCA  |.........
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.

[NeoPa]

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 :

=Sum(IIf(([Country]='England') And ([Gender]='Male'),1,0))
=Sum(IIf(([Country]='Germany') And ([Gender]='Male'),1,0))
=Sum(IIf(([Country]='England') And ([Gender]='Female'),1,0))
=Sum(IIf(([Country]='Germany') And ([Gender]='Female'),1,0))

Here is a picture of what it looks like when it's opened :

[NeoPa]

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.

[Jugnu]

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?

[NeoPa]

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.

[Jugnu]

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.

[NeoPa]

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.

[Jugnu]

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

[Jugnu]

2nd part of pics is here..
if you need any other information related to our form or database kindly suggest.
thanks :)

[NeoPa]

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.

[NeoPa]

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.