What formula should be used

[Frank Situmorang]

Hello,

My church membership query will have the followeing fields:
H. Adress ID Name Role Date of Marriege, date of birth
--------- --------- ----- ----------------
--------------
1 Mr. White husband 23 Jan 1960 1 Jan 1940
1 Mrs. Whilte Wife 23 Jan 1960 5 Mar 1941
1 Miss Helen daughter 01 Mar
1962
1 Mr. Mark Son 15 Feb
1964

2 Mr. Barlett husband 20 Mar 1995 2 April 1972
2 Mrs. Barlett Wife 20 Mar 1995 5 Mar 1975
2 Miss Susan daughter 01 Mar
2000
2 Mr.Lukas Son 15 Feb
2002

My church needs a report or can just can view in the form in order to know

Couples Category:

1. How many couples/family that has marriige age;1-5 years
2.How many couples/family that has marriige age;6-15 years
3. How many couples/family that has marriige age;16-30 years
4.How many couples/family that has marriige age;31 - above years

Age Category:

Children: Age 1 - 14
Youth : 15 19
Adult 20 - above

How can I sum up the field date of marriege and date of birth in order to
result in the above category. especially it is difficult to ck first if the
same address id and the same marriiage date sould be counted as one.

Thanks for any help

 

[strive4peace]

Hi Frank,

I am assuming that you are using the HomeAddressID to relate families?
This may not be the best idea as there can be multiple families with the
same address -- or multiple singles living at the same address who are
not related and you wish to treat separately

Here is a suggestion:

People
- PID, autonumber
- Lastname, text
- Firstname, text
etc

Families
- FamilyID, autonumber -- PK
- PID_HH, long integer, FK to People

WHERE
PID_HH is the PID for the head of Household

FamilyMembers
- FamMbrID, autonumber
- FamilyID, long integer -- FK to Families
- PID_Fam, long integer -- FK to People

Members (of the church)
- MemberID, autonumber -- PK
- PID_Mbr, long integer -- FK to People

Events
- EventID, autonumber -- PK
- EvTypID, long integer -- FK to EventTypes
- EventDate, date/time

EventPeople
- EvPplID, autonumber
- EventID, long integer -- FK to Events
- PID_Ev, long integer -- FK to People
- RoleID, long integer -- FK to Roles

EventTypes
- EvTypID, autonumber -- PK
- EventType, text
- numPpl, integer -- number of people needed (null if no limit)

records will be, for instance:
Wedding
Graduation

Roles
- RoleID, autonumber -- PK
- Role, text

records will be, for instance:
Husband
Wife
Son
Daughter

PK is Primary Key
FK is Foreign Key

these are just some ideas for you to think about -- if I worked with
your data, I could come up with something better and more complete. The
main thing is that you need more tables! do not use one table to keep
track of your membership -- make separate tables for each 'noun'. While
it takes longer to set the data up this way, it will give you greater
flexibility down the road -- and you will not be repeating information
like a Wedding Date because that date will be tied to an Event and so
will the people involved.

when you repeat information in your data structure, you take a chance
that it will be wrong (or not updated) in one of the places it is stored.

~~~

.... but, to answer your question...
you can use DateDiff to get a number of years between 2 dates -- but the
problem with this is that is ONLY looks at year ... so if the marriage
was 12/30/08 and now it is 2/4/09, DateDiff will return 1 -- and that is
not correct

better to construct dates to look between using the DateSerial function,
which takes 3 parameters: year, month, and day

DateSerial(Year, Month, Day)

each argument can be an equation. This is very nice!

I am assuming you are always going to base your calculations on the
current date. The current year is:

Year(Date())

the current month is

Month(Date())

and the current day is

Day(Date())

so, to get dates that are 1 to 5 years old, you can use this for criteria:

BETWEEN DateSerial(Year(Date())-5, Month(Date()), Day(Date()) AND
DateSerial(Year(Date())-1, Month(Date()), Day(Date())


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~

 

[Frank Situmorang]

Thanks Strive for your comprehensive explanation, I do not even catch up all
the understandings because I am not so good in MS Access. I have to admit
that I can develop my chrurch database, is just by the help of the good
people in this news group.

What I have reflected in my email is the records look like in the datasheet
view. It is the link between the Address table( Mother) and membership table
( child) and role yes sourced from table but let it be put in the membership
table.

Yes, I have to admit that I do not have the event table. all the date
related is in the member table.

My memership databse is already finished, but now I want to make a report
producing what I mentioned in my email. Without adding event tabble what is
the best we can do to produce the report especially producing how many couple
has marriage age is 1 to 5 etc...

Thanks in advance if you still can help me.

I will still learn the Date series that you propose.

 

[strive4peace]

Hi Frank,

I probably should have put the DateSerial stuff first ... restructuring
the database would put you back to square one and if you are not
committed to helping them for awhile, then it might not get done!

In order to help you with exact syntax, we need to know exactly what
everything is called -- so here are some steps you can take to document
that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
'click HERE
'press F5
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub

'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub

'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~
***********************************
***********************************

Create General Module, Reference DAO Library, Run Code
---


*** How to Create a General Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste <smile>) the code in

for this code, make sure you have a reference to a Microsoft DAO Library

once the code is in the module sheet, from the menu -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

then, save. You will be prompted for a module name. Call it -->
mod_ShowFields

the code you will want to paste in starts with

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library

and goes down to the last -->

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

any line starting with a single quote ' is a comment and it will be green

as the instructions say, you will need to reference a Microsoft DAO
library (it could be selected already but you will have to look). do
this BEFORE you try to compile the code


*** DAO Library Reference ***

from the menu -->
Tools, References... from a module window

if there is not a DAO Library checked near the top of the list, scroll
to Microsoft DAO 3.6 Object Library and check it

~~~~~~~~~~~~~~~~~~~~``
once you have done this...

click in the *RunShowFieldsForAllTables* sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread



Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~