Filter compute records in MS access

Question

1.00/5 (2 votes)

See more:

Is it possible to compute Records in the Datagridview or MS access using the Crystal Report? or in a Datagridview like

I have a Field Named Male,Female,school,District,Total,DistrictTotal,DistrictMale,DistrictFemale

Now what i'm trying to achieve is:
Compute all the Males from 1 district and save it in the DistrictMale

Like There are 3 districts which are district 1,2 and 3.
In 1 District there are 3 Schools that has males and females

is it possible to compute the total of the male in District 1 and Save it in the District Male.

The Expected Data Would Be:
When the user Will add number male Female and district of a certain school
Upon Clicking the Save Button
The Total of the Male and Female will be Calculated, ALSO the total Male of a Certain District will be calculated basing on the Total Male input with the same District Name.

This is the Expected output I'm Trying to Generate in my Report

District	SchoolName      	Grade 1	 Total	 Grade 2    Total	Grand Total
		                     Male Female	    Male Female		
   1	         BCS	        1	2	  3	     6	   5	 11      	14
   1	         TCS	        1	3	  4	     4 	   3	  7	        11
   1          	 RES	        4	6	  10	 6	   3	  9	        19
   1	         WES	        8	2	  10	 6	   2	  8	        18
   1	         VCS	        3	5	  8	     3	   5	  8	        16
District Total		           17	18	  35	 25	   18	 43      	78
								
   2	         QCS	        2	6	  8	     1	    3	  4	         12
   2	         YES 	        3	8	  11	 1	    3	  4	         15
   2	         GCS	        5	4	  9	     1	    3	  4	         13
   2	         KPS	        3	2	  5	     1	    3	  4	         9
District Total		            13	20	  33	 4	   12	  16	     49

What I have tried:

I haven't tried anything yet, because im still figuring out if there's a formula for it.
or should i just use the Crystal report and Add some Formulas there.

Posted 9-Aug-21 15:21pm

Beginner213456

Updated 11-Aug-21 14:22pm

v6

Add a Solution

Comments

Maciej Los 10-Aug-21 2:24am

Yes, it's possible.

Beginner213456 10-Aug-21 2:43am

can you pls show me how if u tried it before or any related article i can read?
i have browsed in the internet but can't find the right one

Maciej Los 10-Aug-21 2:54am

Can you share sample data?
Use text instead of image.

Beginner213456 10-Aug-21 3:25am

Fields: Male, Female, Districts, Total, District Male
Male: input Value
Female: Input Value
Total: sum of the the Male and Female
District Male: Total of all the males in a Certain District

Maciej Los 10-Aug-21 3:38am

I'd suggest to improve question by adding necessary description and input data. Then, add expected result. Based on the current description i'm unable to answer to your question.

Beginner213456 10-Aug-21 22:22pm

i have added the expected result in the solution, please check if its still doable, and what should i do to achieve it

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-08-09T23:03:00

As Maciej has suggested, you are going to get far better answers if you provide a clear description of the problem, some sample data and some expected results for the data provided.

First point, totals are not the sort of thing you should store. You have to go to the trouble of maintaining that data item for every change on the table. For small systems like this it is easier to just calculate the totals as and when you need them (For the purists, yes there are exceptions to that "rule" but you are a long way off rolling stock totals and the like). If you set yourself a standard query up you can join to that query for the totals in any of your other reports. E.g. I set up a query that returns all of the counts by District, by gender and overall like this: Query "DistrictTotals"

SQL

SELECT Schools.District, Schools.SchoolType, Count(*) As Total
FROM Schools
GROUP BY Schools.District, Schools.SchoolType
UNION
Select Schools.District, "All",Count(*)
FROM Schools
GROUP BY Schools.District
UNION
Select "All", Schools.SchoolType, Count(*)
FROM Schools
GROUP BY Schools.SchoolType
UNION Select "All", "All", Count(*)
FROM Schools
ORDER BY 1, 2;

which in my case returned these results

District	SchoolType	Total
1			All			3
1			Female		1
1			Male		2
2			All			4
2			Female		3
2			Male		1
3			All			4
3			Male		4
All			All			11
All			Female		4
All			Male		7

If I want to show information about the districts say I can then join to that query like this

SQL

SELECT Districts.*, DistrictTotals.SchoolType, DistrictTotals.Total
FROM Districts INNER JOIN DistrictTotals ON CStr(Districts.ID) = DistrictTotals.District;

I've had to convert the ID to a string because I included "All" in my totals.