Click here to Skip to main content
15,068,164 members
Please Sign up or sign in to vote.
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
Updated 11-Aug-21 14:22pm
v6
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

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.
   
Comments
Beginner213456 10-Aug-21 22:21pm
   
I have added the expected result in the solution, please check if it is still possible to achieve it with vb.net and ms access.
Beginner213456 10-Aug-21 23:03pm
   
I am having an idea on the codes you provided thank you, but can you please show me how can i code it vb

should i do it like:
dim com as oleDbCommand
com = New OleDbCommand("SELECT Elementary.District, Elementary.SchoolName, Count(*) As Total FROM(Elementary) GROUP BY Schools.District, Elementary.SchoolName")

* My Table Name is Elementary
CHill60 11-Aug-21 3:55am
   
Yes, that is one way of doing it
Maciej Los 13-Aug-21 2:08am
   
5ed!
Beginner213456 13-Aug-21 2:17am
   
5ed!?
Maciej Los 13-Aug-21 3:24am
   
I gave 5 stars for this answer. This mean that answer is very helpful and @Chill60 has reached reputation points. I'd suggest to read this: Code Project Rating and Reputation FAQ[^]
Beginner213456 6-Sep-21 20:33pm
   
I understand the code, and the result is what i wanted, but im confused where should i input this code in my project? should i add this in a button in the winform?
or should i add this in my database instead?
CHill60 7-Sep-21 3:00am
   
Put the query into your report...either a Crystal report if that is what you are using or into the VB code that populates a datagridview or other UI control
Beginner213456 8-Sep-21 5:05am
   
if i were to add this in my crystal report. should i add some textboxes?
and insert this code in the Load Form?
CHill60 8-Sep-21 8:50am
   
Add textboxes where and for what reason?
Why would you put this into the Form load event - unless you are populating a control on the form with the data - in which case why use Crystal Reports?
You need to get a clear idea in your head of what you are trying to achieve, don't just randomly go off after ideas. This article might help How to Write Code to Solve a Problem, A Beginner's Guide[^]

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900