Count total schools by stage (1 Viewer)

[mohamedmatter]

I want the total number of schools by stage. When the stage is changed, the number of schools is updated
I insert attach sample school database

 

[isladogs]

Use a totals query. Group by Stage, Count by School

SELECT Schooltbl.Stage, Count(Schooltbl.SchoolName) AS CountByStage
FROM Schooltbl
GROUP BY Schooltbl.Stage;

 

[Gasman]

I want the total number of schools by stage. When the stage is changed, the number of schools is updated
I insert attach sample school database

You have not even bothered to make an attempt?

 

[mohamedmatter]

Use a totals query. Group by Stage, Count by School

SELECT Schooltbl.Stage, Count(Schooltbl.SchoolName) AS CountByStage
FROM Schooltbl
GROUP BY Schooltbl.Stage;

thanks but i like counter in form by event help me

 

[mohamedmatter]

You have not even bothered to make an attempt?

I tried, but it didn't work, there were errors such as not updating the values

 

[Gasman]

thanks but i like counter in form by event help me

Make a query from what @isladogs has supplied, then use a DCount() for the relevant stage against that query.

 

[mike60smart]

I tried, but it didn't work, there were errors such as not updating the values

Hi
Using Colin's Totals Query produces the following result:-

 

[mohamedmatter]

Make a query from what @isladogs has supplied, then use a DCount() for the relevant stage against that query.

how can move value in form and when change stage in form update value

 

[mohamedmatter]

Hi
Using Colin's Totals Query produces the following result:-

how move value to form

 

[Gasman]

Recalc, Refresh the form?
Never sure which TBH, so just experiment and try and learn something for yourself?

 

[isladogs]

I've no idea what you mean but you can display the results of the query in a subform.
If the data changes, just requery the subform to get the new values.
Time you tried for yourself

 

[Gasman]

I've no idea what you mean but you can display the results of the query in a subform.
If the data changes, just requery the subform to get the new values.
Time you tried for yourself

Colin,
I opened the DB with bypass as you never know what they might contain.
So initially never saw the form, which must be a test version anyway?
O/P has a form with three controls for each of the stages.

I suggested a DCount() against your query.
So, if that form somehow added another stage to the table, what would you use to update the DCounts in the controls?

 

[isladogs]

If you want to use DCount, then no need for the query. Just base it on the table.

However, I wouldn't use DCount precisely because another stage such as Tertiary might get added at some point.
I would scrap those 3 controls and just display the results in a subform

 

[mike60smart]

how move value to form

You can create a Form in Continuous View based on your Table.

On this Form in the header you can create a Command Button which would open a Form based
on Colin's Totals Query. This Form would be a PopUp Form.

It would then give you the following Result when you click the View Totals Button

 

[Gasman]

If you want to use DCount, then no need for the query. Just base it on the table.

However, I wouldn't use DCount precisely because another stage such as Tertiary might get added at some point.
I would scrap those 3 controls and just display the results in a subform

Fair point.

 

[mohamedmatter]

I've no idea what you mean but you can display the results of the query in a subform.
If the data changes, just requery the subform to get the new values.
Time you tried for yourself

I've no idea what you mean but you can display the results of the query in a subform.
If the data changes, just requery the subform to get the new values.
Time you tried for yourself

I found a solution, but I encountered a simple problem, please help. It is when the stage is changed, the values are not changed after the stage is updated. I have attached the rule after the modification

 

[Gasman]

You seriously are not going to try and do any of the work yourself are you?.

I am out of this thread.

 

[isladogs]

This is my last post as well

If you must use DCount then you need to ensure the changed data is saved before recalculating.
Add the line Me.Dirty =False at the start of the Stage after update event