what formula to be used

[Frank Situmorang]

Hello,

For statistical purposes, we need to know how many couple do we have with
marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is FK), so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database structure
because there a repition of the date/data. But I do not want to reset it back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage, but how
can we make it to count 2 records in the same household and the same date of
marriage will count AS ONE

Thanks for any help

 

[Allen Browne]

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.

 

[Frank Situmorang]

Allen,

Thanks for your help, but could you help me again. What is the query if we
only select the record with Marital Status is " B", means the only Married
couple, because it could be now is marrried, but later next year the status
could be Widow, because her husband died. Marital Status is C means Widow.

Thanks in advance
--
H. Frank Situmorang

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.

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

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

Hello,

For statistical purposes, we need to know how many couple do we have with
marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is FK),
so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database structure
because there a repition of the date/data. But I do not want to reset it
back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage, but
how
can we make it to count 2 records in the same household and the same date
of
marriage will count AS ONE

Thanks for any help

 

[Frank Situmorang]

Allen:

What is the result of the Select Distinct below. Normally the Date of
Marriage of husband and wife should be the same, say 14 Feb 2005. If the
operator mistype on the birthday of wife is 19 Feb 2005, what would be the
result.Suppose my query looks like this:

Household Name Name Marriege Date
========== ====== =========
Mr. White Arthur White 14 Feb 2005
Mr. White Ellen White 19 Feb 2005

Will it take only Arthur White's Marriage Date?

Thanks in advance
--
H. Frank Situmorang

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.

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

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

Hello,

For statistical purposes, we need to know how many couple do we have with
marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is FK),
so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database structure
because there a repition of the date/data. But I do not want to reset it
back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage, but
how
can we make it to count 2 records in the same household and the same date
of
marriage will count AS ONE

Thanks for any help

 

[Allen Browne]

In your original post, Frank, you recognised that this is not really a
relationally correct way to design this. There's no key that actually
unites the 2 people as part of the same marrigage, so there is no watertight
way to get the count right. That's a problem with the schema; so if you try
to fudge it by identifying a marriage as, "people from the same household
who were married on the same date", and the date is entered incorrectly,
then you will get flawed results.

So, your choices are:
a) If this is really important, change the schema so you identify households
as entities in their own right. Here's one way that might be done:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html
That would allow you to de-duplicate on household, and avoid the data
integrity issue (since the marriage date would be the 'birth-date' of the
household itself, it would be entered only once.)

b) If it's not important enough to warrant making such a change, try to
minimize the possible inconsistencies. For example, when entering a client,
you might try to see if there's another record for another person who is
part of the same household but has a different marriage date, and ask the
user if this is right.

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

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

Allen:

What is the result of the Select Distinct below. Normally the Date of
Marriage of husband and wife should be the same, say 14 Feb 2005. If the
operator mistype on the birthday of wife is 19 Feb 2005, what would be the
result.Suppose my query looks like this:

Household Name Name Marriege Date
========== ====== =========
Mr. White Arthur White 14 Feb 2005
Mr. White Ellen White 19 Feb 2005

Will it take only Arthur White's Marriage Date?

Thanks in advance
--
H. Frank Situmorang

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.

For statistical purposes, we need to know how many couple do we
have with marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is
FK),
so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database
structurebecause there a repition of the date/data. But I do not want
to reset it back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage,
but how can we make it to count 2 records in the same household
and the same date of marriage will count AS ONE

 

[Allen Browne]

I'm not sure what you intend here, Frank, but it probably involves adding a
WHERE clause so you only get those of the marriage status you want, e.g.:
WHERE [Marital Status] = "B"
or perhaps:
WHERE [Marital Status] IN ("B", "C")

Of course, the results will change over time, as people's marriage status
changes. If you actually wanted to be able to ask a question such as:
How many households did we have back in 2006?
then you would need to store the data differently to get an accurate answer
to that question now (storing the historical data on mariage status etc.)

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

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

Allen,

Thanks for your help, but could you help me again. What is the query if we
only select the record with Marital Status is " B", means the only Married
couple, because it could be now is marrried, but later next year the
status
could be Widow, because her husband died. Marital Status is C means Widow.

Thanks in advance
--
H. Frank Situmorang

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.

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

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

Hello,

For statistical purposes, we need to know how many couple do we have
with
marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is
FK),
so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database
structure
because there a repition of the date/data. But I do not want to reset
it
back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage, but
how
can we make it to count 2 records in the same household and the same
date
of
marriage will count AS ONE

Thanks for any help

 

[Frank Situmorang]

Allen,

Thanks very much Allen. This is not really important, so I just take option
B. In church annual Plan, there is a chruch profile needs to know, How many
child, marriage age, etc. this is just for statistical. This is not business
like

With many thanks,

--
H. Frank Situmorang

In your original post, Frank, you recognised that this is not really a
relationally correct way to design this. There's no key that actually
unites the 2 people as part of the same marrigage, so there is no watertight
way to get the count right. That's a problem with the schema; so if you try
to fudge it by identifying a marriage as, "people from the same household
who were married on the same date", and the date is entered incorrectly,
then you will get flawed results.

So, your choices are:
a) If this is really important, change the schema so you identify households
as entities in their own right. Here's one way that might be done:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html
That would allow you to de-duplicate on household, and avoid the data
integrity issue (since the marriage date would be the 'birth-date' of the
household itself, it would be entered only once.)

b) If it's not important enough to warrant making such a change, try to
minimize the possible inconsistencies. For example, when entering a client,
you might try to see if there's another record for another person who is
part of the same household but has a different marriage date, and ask the
user if this is right.

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

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

Allen:

What is the result of the Select Distinct below. Normally the Date of
Marriage of husband and wife should be the same, say 14 Feb 2005. If the
operator mistype on the birthday of wife is 19 Feb 2005, what would be the
result.Suppose my query looks like this:

Household Name Name Marriege Date
========== ====== =========
Mr. White Arthur White 14 Feb 2005
Mr. White Ellen White 19 Feb 2005

Will it take only Arthur White's Marriage Date?

Thanks in advance
--
H. Frank Situmorang

Create a query like this:
SELECT DISTINCT [Name of Household],,
[Date of Marriage],
DateDiff("yyyy",[Date of Marriage], Now()) \ 5 AS Bracket
FROM ...

You can then create another query to GROUP BY Bracket and COUNT the
households.


For statistical purposes, we need to know how many couple do we
have with marriage age 0 to 5 Years...etc..

I have family table PM linked with member table ( with family ID is
FK),
so
the query will show as follows

Name of Household Name Date of marriage
( family table) ( membertable) (Member table )
========== ========== ===========

1. Household A Mr White 14 Feb 1995
1. Household A Mr. While 14 Feb 1995
2. Household B Mr. Black 22 Oct 2000
2. Household B Mrs Black 22 Oct 2000

I have to admit that maybe there is a weakness of the database
structurebecause there a repition of the date/data. But I do not want
to reset it back.

So my question is what formula should I make in order to know how many
household with marriage age 5 years and then with 10 years.

There is formula using Date Different from NOw to date of marriage,
but how can we make it to count 2 records in the same household
and the same date of marriage will count AS ONE