Corsstab Query for statistics with dynamic header

[Frank Situmorang]

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Duane Hookom]

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

 

[KARL DEWEY]

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Thanks Karl and Duane for your kind explanation. My problem is I am not too
well in SQL view. Normally I make a query by desing view. Moreover my
expertise is in Accounting. I have to admit that I can make a simple church
membership database is just by the help of all good people in this forum.

So is there any approach to understand what you dan Duane explanined?.
BEcause when I tried to make it with wizzard there is a wizard for crosstab
queery but no for Union Query.

Thanks for your help
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Thanks Duane... probably it is because my English langguage problem, what I
meant to say is 12 months but for the current year. For example, Jan 2008 is
differenct if I want to know how many babies were born in our chruch in Jan
2008, and then when we were in Jan 2009 next year, how many babies were born.
So our report always shows for the 12 months of the current year.

Thanks very much
--
H. Frank Situmorang

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Karl: Sorry that I mentioned English name of the filed to make it easier to
understand, but in fact the name is in Indonesian. Here is Duane suggestion
already works with Indonesian Name, when I subtituted your codes it does not
work. Could you please help?

More over how can we make it with the interval in the year. I mean if we
want to know for Jan - Dec 2008. So we can choose year from the form ( I will
make a form)

Thanks in advance
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[KARL DEWEY]

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[KARL DEWEY]

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Thanks very much Karl, you are so generous to us. Sorry I know you are very
bussy, but maybe you still can help us on how to filter the query for certain
year only. The format by month is ok but we want to filter it for certain
month, like only for 2008 for example, because we want to see the performance
of the church officers in gaining souls, how many baptism do we have for the
year.

I know how to make it in Grids Query by putting a creteria to select from
form, but since this is SQL I really do not know to make it.

My next question is about the copy right law. I already made the church
membership database that coulld have a selling price. I want to sell it to
our denomination, but the sales proceeds is not for me, I want to donate all
to the students who are poor in our Almamauter/University. Do I violate the
copyrights law of MSAccess. Of course this database will use MS Access.

I have to admit that I can finish building this database is also by the help
of many others in this forum.

Greetings from Jakarta, Indonesia.
--
H. Frank Situmorang

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

Dear Karl:

I tried to use your suggestion as my basis to make the 12 month current year
statistics, The following is the VBA, but still can not work. I appreciate
your help.

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union WHERE(Year(FrankS_Union.TheDate)=Year(Date())
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm & Year(Date()) ") In ("Jan & Year(Date()) ","Feb
& Year(Date()) ","Mar & Year(Date()) ","Apr & Year(Date()) ","May &
Year(Date()) ","Jun & Year(Date()) ","Jul & Year(Date()) ","Aug &
Year(Date()) ","Sep & Year(Date()) ","Oct & Year(Date()) ","Nov &
Year(Date()) ","Dec & Year(Date()) ");

Thanks in advance
--
H. Frank Situmorang

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Rob Parker]

Hi Frank,

I suspect that your problem is coming from the literal strings in your query
(there are lots of them), which look like "mmm & Year(Date()) ". This
construction will not include the current year in the string; it will create
the string exactly like the one you have entered. To get the current year
into the output string (in either the Pivot clause or the values in the In
clause, you will need to use:

"mmm " & Year(Date())

for each of these values.

HTH,

Rob

Dear Karl:

I tried to use your suggestion as my basis to make the 12 month current
year
statistics, The following is the VBA, but still can not work. I appreciate
your help.

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt,
FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union WHERE(Year(FrankS_Union.TheDate)=Year(Date())
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt,
FrankS_Union.DateType
PIVOT Format([TheDate],"mmm & Year(Date()) ") In ("Jan & Year(Date())
","Feb
& Year(Date()) ","Mar & Year(Date()) ","Apr & Year(Date()) ","May &
Year(Date()) ","Jun & Year(Date()) ","Jul & Year(Date()) ","Aug &
Year(Date()) ","Sep & Year(Date()) ","Oct & Year(Date()) ","Nov &
Year(Date()) ","Dec & Year(Date()) ");

Thanks in advance
--
H. Frank Situmorang

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt,
FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt,
FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hello Karl:

I do not seem to have received your response, in case you have not
received
here again below the name of the fields. I appreciate your help, this
would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath
School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as
lost

The table structure is that all of the fields are in the record but we
want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current
year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for
All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting
for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang


:

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR]
As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby
UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM
bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan
to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS
CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the
crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been
Jan - Dec for as
long as I can remember and I don't think they will ever change
;-)

I believe your issue again is one of un-normalized table
structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As
TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order
to know how
many member do we have for each field in 12 months which can
be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec
Total

Number of birht x 0 xx ...............
xx
Num of New member xx ..................................
xx
Numb of Baptized 0 x 0 3
xx
and so forth

Thanks for anyidea provided.

 

[KARL DEWEY]

This prompts for year --
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
WHERE (((Format([TheDate],"yyyy"))=[Enter year (2005)]))
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

This also display the year in the output --
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity,
Format([TheDate],"yyyy") AS [Stats Year]
FROM FrankS
WHERE (((Format([TheDate],"yyyy"))=[Enter year (2005)]))
GROUP BY Replace([DateType],"Date","Number"), Format([TheDate],"yyyy")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

Dear Karl:

I tried to use your suggestion as my basis to make the 12 month current year
statistics, The following is the VBA, but still can not work. I appreciate
your help.

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union WHERE(Year(FrankS_Union.TheDate)=Year(Date())
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm & Year(Date()) ") In ("Jan & Year(Date()) ","Feb
& Year(Date()) ","Mar & Year(Date()) ","Apr & Year(Date()) ","May &
Year(Date()) ","Jun & Year(Date()) ","Jul & Year(Date()) ","Aug &
Year(Date()) ","Sep & Year(Date()) ","Oct & Year(Date()) ","Nov &
Year(Date()) ","Dec & Year(Date()) ");

Thanks in advance
--
H. Frank Situmorang

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang


:

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.

 

[Frank Situmorang]

You'r really a great help to us Karl, thank you very very much. I have try to
open the query and put it as a base of our Monthly Statistcs report.

We appreciate it if you could inform me how to make the name of the field to
be a more meaning full. If the control tex box we can make a caption to make
it more meaning full but if it is in the query I do not know how to make it.

Like the Date "TGLLAHIR", we just want to mention it in the report as " Born"
TGLBPTIS = " Baptism"
TGL_pen = "Member transfer IN"
ATSPERCA_M= " Admission of Faith"
ATSSUR1_K= member transfer OUT"
"KMATIAN_K="deceaced"
KELMURT_K="Backsliding"
KELHILA_K= " Reported as Lost"

Thanks in advance Karl

--
H. Frank Situmorang

This prompts for year --
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
WHERE (((Format([TheDate],"yyyy"))=[Enter year (2005)]))
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

This also display the year in the output --
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity,
Format([TheDate],"yyyy") AS [Stats Year]
FROM FrankS
WHERE (((Format([TheDate],"yyyy"))=[Enter year (2005)]))
GROUP BY Replace([DateType],"Date","Number"), Format([TheDate],"yyyy")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little

Dear Karl:

I tried to use your suggestion as my basis to make the 12 month current year
statistics, The following is the VBA, but still can not work. I appreciate
your help.

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union WHERE(Year(FrankS_Union.TheDate)=Year(Date())
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm & Year(Date()) ") In ("Jan & Year(Date()) ","Feb
& Year(Date()) ","Mar & Year(Date()) ","Apr & Year(Date()) ","May &
Year(Date()) ","Jun & Year(Date()) ","Jul & Year(Date()) ","Aug &
Year(Date()) ","Sep & Year(Date()) ","Oct & Year(Date()) ","Nov &
Year(Date()) ","Dec & Year(Date()) ");

Thanks in advance
--
H. Frank Situmorang

Been busy --

Union query --
SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLLAHIR" AS
[DateType], bukuangkby.TGLLAHIR AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGLBPTIS_M" AS
[DateType], bukuangkby.TGLBPTIS_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "TGL_pen" AS
[DateType], bukuangkby.TGL_pen AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSPERCA_M" AS
[DateType], bukuangkby.ATSPERCA_M AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "ATSSUR1_K" AS
[DateType], bukuangkby.ATSSUR1_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KMATIAN_K" AS
[DateType], bukuangkby.KMATIAN_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELMURT_K" AS
[DateType], bukuangkby.KELMURT_K AS TheDate
FROM bukuangkby
UNION SELECT bukuangkby.STAT_CODE, bukuangkby.JnsAngt, bukuangkby.FName,
bukuangkby.MName, bukuangkby.LName, bukuangkby.JenisKel, "KELHILA_K" AS
[DateType], bukuangkby.KELHILA_K AS TheDate
FROM bukuangkby;

TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.DateType, Count(FrankS_Union.TheDate) AS [Total Of
DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OR
TRANSFORM Count(FrankS_Union.TheDate) AS CountOfTheDate
SELECT FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType,
Count(FrankS_Union.TheDate) AS [Total Of DateType]
FROM FrankS_Union
GROUP BY FrankS_Union.STAT_CODE, FrankS_Union.JnsAngt, FrankS_Union.DateType
PIVOT Format([TheDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

Hello Karl:

I do not seem to have received your response, in case you have not received
here again below the name of the fields. I appreciate your help, this would
be a great help for us.

Thanks Karl for your willingness to help me.

The name of my table is : bukuangkby

The fieldnames, I just mentioned the one that relates to building a
statistics:
Field name Meaning in English
======== ===========
1. STAT_CODE Status whether Active, or Passive or Move
2. JnsAngt Member Type ( Church member or Sabbath School Mem)
3. FName First Name
4. MName Middle Name
5. LName Last Name
6. JenisKel Gender
7. TGLLAHIR Date of Birth
8. TGLBPTIS_M Date Baptized
9. TGL_pen Date Accepted thru transfer
10.ATSPERCA_M Date accepted as member thru faith confession
11.ATSSUR1_K Date Quit thru transfer
12. KMATIAN_K Date of Quit for passed away
13. KELMURT_K Date of Quit becaause of backsliding
14.KELHILA_K Date of Quite for Unknown so reported as lost

The table structure is that all of the fields are in the record but we want
to have a kind of statistics in the 12 months format for:

2 kind of statistics from inception and for 12 months of current year)

1. From incepttion todate for Row Tittle JnsAngt, column STAT_CODE
2. From Inception todate for JenisKel Column title STAT_CODE
3. 12 months of Current Year : Row title:JnsAngt Column tittle is for All
dates (fields no. 7 to 14).

Sorry Karl to bother you. My software is almost complete and waiting for
this. Actually I want to donate this software to my denomination.

Thanks in advance for your great help


--
H. Frank Situmorang


:

Post your table structure with the actual field names your table has.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl, I forgot to copy paste Duane's suggestion: here it is
SELECT [FName],[MName],[LName], "TGLLAHIR" as DateType, [TGLLAHIR] As
TheDate FROM bukuangkby UNION ALL SELECT [FName],[MName],[LName],
"TGLDIBAPTIS", [TGLBPTIS_M] FROM bukuangkby UNION ALL SELECT
[FName],[MName],[LName], "TGL DITERIMA", [TGL_pen] FROM bukuangkby UNION ALL
SELECT [FName],[MName],[LName], "TGLKELUAR", [ATSSUR1_K] FROM bukuangkby;

And here is the crosstab, but I can not make it like yours from Jan to Dec
with option of Year:
TRANSFORM Count([bukuangkby Query_statistics2].TheDate) AS CountOfTheDate
SELECT [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName,
Count([bukuangkby Query_statistics2].TheDate) AS [Total Of TheDate]
FROM [bukuangkby Query_statistics2]
GROUP BY [bukuangkby Query_statistics2].FName, [bukuangkby
Query_statistics2].MName, [bukuangkby Query_statistics2].LName
PIVOT [bukuangkby Query_statistics2].DateType;

Thanks a lot
--
H. Frank Situmorang


:

Carrying Duane's union query (Named FrankS) through into the crosstab ---
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity
FROM FrankS
GROUP BY Replace([DateType],"Date","Number")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

--
KARL DEWEY
Build a little - Test a little


:

What is so dynamic about months of the year. They have been Jan - Dec for as
long as I can remember and I don't think they will ever change ;-)

I believe your issue again is one of un-normalized table structure. You
would need to use a union query to normalize all the dates:

SELECT [Name], "Date Of Birth" as DateType, [Date Of Birth] As TheDate
FROM tblNoName
UNION ALL
SELECT [Name], "Date Of Membership", [Date accepted as member]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Baptized", [Date of Baptized]
FROM tblNoName
UNION ALL
SELECT [Name], "Date Quit", [Date Quit as member]
FROM tblNoName;

You can then make a crosstab query from the union query.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

In my church membership table I have these fields:
1. Name
2. Date of Birth
3. Date accepted as member
4. Date of Baptized
5. Date Quit as member

My question is how can I make a query for statistics in order to know how
many member do we have for each field in 12 months which can be dynamic:
So the outlay is more or less as follows:
Jan Feb Mar...............Dec Total

Number of birht x 0 xx ............... xx
Num of New member xx .................................. xx
Numb of Baptized 0 x 0 3 xx
and so forth

Thanks for anyidea provided.