Form using count function

  • Thread starter Thread starter Ranjith Kurian
  • Start date Start date
R

Ranjith Kurian

I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];
 
Hi Karl
Thanks a lot, its working fine....

KARL DEWEY said:
Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


Ranjith Kurian said:
I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
But in the below query there is no Total column.

i even need a total column

Ranjith Kurian said:
Hi Karl
Thanks a lot, its working fine....

KARL DEWEY said:
Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


Ranjith Kurian said:
I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
Ok ---
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT, Count([Post]) AS Total_Post
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


Ranjith Kurian said:
But in the below query there is no Total column.

i even need a total column

Ranjith Kurian said:
Hi Karl
Thanks a lot, its working fine....

KARL DEWEY said:
Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


:

I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
Thanks a lot.
Actually i want this to show in form view, so i went to form wizard selected
the query to view in form, till here it worked fine. But the problem is if i
add any new data to Dept or Post columns to my table the new additions are
not picked in form view.

example:
1) In Dept A if there is Quit for post Lead this is shown in query but not
picked in form (i need to create again a new form)
2)a new Dept F is add to table even these are shown in query but not picked
in form view.




KARL DEWEY said:
Ok ---
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT, Count([Post]) AS Total_Post
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


Ranjith Kurian said:
But in the below query there is no Total column.

i even need a total column

Ranjith Kurian said:
Hi Karl
Thanks a lot, its working fine....

:

Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


:

I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
After adding data to the table (you should not be interfacing with the table
but using a form for data entry) you must refresh the form.

--
Build a little, test a little.


Ranjith Kurian said:
Thanks a lot.
Actually i want this to show in form view, so i went to form wizard selected
the query to view in form, till here it worked fine. But the problem is if i
add any new data to Dept or Post columns to my table the new additions are
not picked in form view.

example:
1) In Dept A if there is Quit for post Lead this is shown in query but not
picked in form (i need to create again a new form)
2)a new Dept F is add to table even these are shown in query but not picked
in form view.




KARL DEWEY said:
Ok ---
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT, Count([Post]) AS Total_Post
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


Ranjith Kurian said:
But in the below query there is no Total column.

i even need a total column

:

Hi Karl
Thanks a lot, its working fine....

:

Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


:

I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
Hi Karl

Thanks,

Could you please tell how can i refresh my form or do you mean that i need
to update the form manually.

As the data of Post and Status column are not fixed the changes are often
expected, but in query these changes are easily accepted.




KARL DEWEY said:
After adding data to the table (you should not be interfacing with the table
but using a form for data entry) you must refresh the form.

--
Build a little, test a little.


Ranjith Kurian said:
Thanks a lot.
Actually i want this to show in form view, so i went to form wizard selected
the query to view in form, till here it worked fine. But the problem is if i
add any new data to Dept or Post columns to my table the new additions are
not picked in form view.

example:
1) In Dept A if there is Quit for post Lead this is shown in query but not
picked in form (i need to create again a new form)
2)a new Dept F is add to table even these are shown in query but not picked
in form view.




KARL DEWEY said:
Ok ---
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT, Count([Post]) AS Total_Post
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


:

But in the below query there is no Total column.

i even need a total column

:

Hi Karl
Thanks a lot, its working fine....

:

Use a crosstab query.
TRANSFORM Count([Post]) AS CountPost
SELECT DEPT
FROM [ABC]
WHERE [Status] = "Quit"
GROUP BY DEPT
PIVOT [Post];

--
Build a little, test a little.


:

I have a table(ABC) with the below three columns as shown below

DEPT Status POST
A Quit AVP
A Quit AVP
B Quit TL
C Quit Specialist
C Quit Specialist
D Board TL
E Board TL

I need a form to view the above table as shown below, it should calculate
only the count of Post if the Status is Quit based on Dept


DEPT AVP TL Specialist Total
A 2 0 0 2
B 0 1 0 1
C 0 0 2 2
D 0 0 0 0
E 0 0 0 0
 
Back
Top