the best way to know events

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello,

In my church membership database, I have field name: Name; Date of Birth,
date of Marriage; date of Baptized, date of death.

My aquestion is what is the best way to know the events, say, how many
babies were born or member baptized, etc.. during February 2009. is there any
best way to filter?. Is there any sample of query to make like a crosstab?
a the report will show like this:
jan feb mar apr......................dec 09

Birth
Baptized
Marriage
Death

Thanks for any help
 
Frank, a relational data structure would store these dates in a related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;
 
Thanks Allen, I will try to make Union Query, because to change the structure
would be very difficult I think since all my forms and reports I already
finished and even to make the reports and forms in foreign language is also
almost finished.

But wen you say clumsy it will work very slow?. I have desingned the
membership database to be able to be used in the consolidation of all
membership per region. So will it be very slow if huge records exist?.

Any way, at least this feature can be used in the church level.

When I used this Union Query how can we filter it for Jan 2009, or Feb 2009
only. Because union query can not be viewed in the designed view. Normally in
designed view we just put in the grid of criteria, the filter.

Thanks for your help again,

--
H. Frank Situmorang


Allen Browne said:
Frank, a relational data structure would store these dates in a related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Frank Situmorang said:
Hello,

In my church membership database, I have field name: Name; Date of Birth,
date of Marriage; date of Baptized, date of death.

My aquestion is what is the best way to know the events, say, how many
babies were born or member baptized, etc.. during February 2009. is there
any
best way to filter?. Is there any sample of query to make like a crosstab?
a the report will show like this:
jan feb mar apr......................dec 09

Birth
Baptized
Marriage
Death

Thanks for any help
Click to expand...
Click to expand...
 
Yes, it's hard to change structure after building everything.

Clumsy: not as easy to create or maintain: you can't do UNIONs in design
view. Also, results are read-only.

Slow: depends how many records you have. Should be fine with a few thousand,
possibly even tens of thousands. But it does get slower as the number of
records increases.

Filtering: you could save the UNION, and then create another query that uses
the UNION query as its source 'table.' You can then filtere on EventDate,
just as you would any other query.

That's where the performance falls off. You are UNIONing all the records
into one long list, and then filtering on a calculated field. That's going
to be much slower than a correctly normalized table where you can create in
index on the date field. But again: it depends on the number of records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Frank Situmorang said:
Thanks Allen, I will try to make Union Query, because to change the
structure
would be very difficult I think since all my forms and reports I already
finished and even to make the reports and forms in foreign language is
also
almost finished.

But wen you say clumsy it will work very slow?. I have desingned the
membership database to be able to be used in the consolidation of all
membership per region. So will it be very slow if huge records exist?.

Any way, at least this feature can be used in the church level.

When I used this Union Query how can we filter it for Jan 2009, or Feb
2009
only. Because union query can not be viewed in the designed view. Normally
in
designed view we just put in the grid of criteria, the filter.

Thanks for your help again,

--
H. Frank Situmorang


Allen Browne said:
Frank, a relational data structure would store these dates in a related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;

Frank Situmorang said:
Hello,

In my church membership database, I have field name: Name; Date of
Birth,
date of Marriage; date of Baptized, date of death.

My aquestion is what is the best way to know the events, say, how many
babies were born or member baptized, etc.. during February 2009. is
there
any
best way to filter?. Is there any sample of query to make like a
crosstab?
a the report will show like this:
jan feb mar apr......................dec 09

Birth
Baptized
Marriage
Death

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
Allen:

Thanks for your explanation. But I think of another way maybe, I just used a
usula select query and then on the date field in the query I just used the
filtering like this?

Between [Forms]![frmReportDates]![txtBegMonth] And
[Forms]![frmReportDates]![txtEndMonth]

Can I put on every date field of events, I put the criiteria which all of
the fields will just refer to the month that I search.

Thanks for your enlightening.


--
H. Frank Situmorang


Allen Browne said:
Yes, it's hard to change structure after building everything.

Clumsy: not as easy to create or maintain: you can't do UNIONs in design
view. Also, results are read-only.

Slow: depends how many records you have. Should be fine with a few thousand,
possibly even tens of thousands. But it does get slower as the number of
records increases.

Filtering: you could save the UNION, and then create another query that uses
the UNION query as its source 'table.' You can then filtere on EventDate,
just as you would any other query.

That's where the performance falls off. You are UNIONing all the records
into one long list, and then filtering on a calculated field. That's going
to be much slower than a correctly normalized table where you can create in
index on the date field. But again: it depends on the number of records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Frank Situmorang said:
Thanks Allen, I will try to make Union Query, because to change the
structure
would be very difficult I think since all my forms and reports I already
finished and even to make the reports and forms in foreign language is
also
almost finished.

But wen you say clumsy it will work very slow?. I have desingned the
membership database to be able to be used in the consolidation of all
membership per region. So will it be very slow if huge records exist?.

Any way, at least this feature can be used in the church level.

When I used this Union Query how can we filter it for Jan 2009, or Feb
2009
only. Because union query can not be viewed in the designed view. Normally
in
designed view we just put in the grid of criteria, the filter.

Thanks for your help again,

--
H. Frank Situmorang


Allen Browne said:
Frank, a relational data structure would store these dates in a related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;

Hello,

In my church membership database, I have field name: Name; Date of
Birth,
date of Marriage; date of Baptized, date of death.

My aquestion is what is the best way to know the events, say, how many
babies were born or member baptized, etc.. during February 2009. is
there
any
best way to filter?. Is there any sample of query to make like a
crosstab?
a the report will show like this:
jan feb mar apr......................dec 09

Birth
Baptized
Marriage
Death

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
That will be fine provided ALL the date fields have a value.

You will need to modify it if any field is null (e.g. if the person is not
married or not dead.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Frank Situmorang said:
Allen:

Thanks for your explanation. But I think of another way maybe, I just used
a
usula select query and then on the date field in the query I just used the
filtering like this?

Between [Forms]![frmReportDates]![txtBegMonth] And
[Forms]![frmReportDates]![txtEndMonth]

Can I put on every date field of events, I put the criiteria which all of
the fields will just refer to the month that I search.

Thanks for your enlightening.


--
H. Frank Situmorang


Allen Browne said:
Yes, it's hard to change structure after building everything.

Clumsy: not as easy to create or maintain: you can't do UNIONs in design
view. Also, results are read-only.

Slow: depends how many records you have. Should be fine with a few
thousand,
possibly even tens of thousands. But it does get slower as the number of
records increases.

Filtering: you could save the UNION, and then create another query that
uses
the UNION query as its source 'table.' You can then filtere on EventDate,
just as you would any other query.

That's where the performance falls off. You are UNIONing all the records
into one long list, and then filtering on a calculated field. That's
going
to be much slower than a correctly normalized table where you can create
in
index on the date field. But again: it depends on the number of records.

Frank Situmorang said:
Thanks Allen, I will try to make Union Query, because to change the
structure
would be very difficult I think since all my forms and reports I
already
finished and even to make the reports and forms in foreign language is
also
almost finished.

But wen you say clumsy it will work very slow?. I have desingned the
membership database to be able to be used in the consolidation of all
membership per region. So will it be very slow if huge records exist?.

Any way, at least this feature can be used in the church level.

When I used this Union Query how can we filter it for Jan 2009, or Feb
2009
only. Because union query can not be viewed in the designed view.
Normally
in
designed view we just put in the grid of criteria, the filter.

Thanks for your help again,

--
H. Frank Situmorang


:

Frank, a relational data structure would store these dates in a
related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each
type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or
crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy
workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;

Hello,

In my church membership database, I have field name: Name; Date of
Birth,
date of Marriage; date of Baptized, date of death.

My aquestion is what is the best way to know the events, say, how
many
babies were born or member baptized, etc.. during February 2009. is
there
any
best way to filter?. Is there any sample of query to make like a
crosstab?
a the report will show like this:
jan feb mar apr......................dec 09

Birth
Baptized
Marriage
Death
Click to expand...
Click to expand...
Click to expand...
 
Back
Top