Form using count function

[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

 

[KARL DEWEY]

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

 

[Ranjith Kurian]

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

 

[Ranjith Kurian]

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

 

[KARL DEWEY]

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

 

[Ranjith Kurian]

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.

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

 

[KARL DEWEY]

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.

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.

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

 

[Ranjith Kurian]

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.

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.

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.

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