Membership History

[unionhorse]

Hello,

We use an access database for the membership administration of thousands of musicians. Membership status varies from "active" to "suspended" to "expelled".

Musicians go in and out of active membership. The database keeps track of the last time a member was suspended and the last time a member was expelled. Unfortunately, like many programs, the history is written over each time.

My goal is to automate capturing a "30-years of membership" date and a "50-years membership" date. We hold a special event each year for 30 and 50 year members. Since our files go back over 100 years, the old handwritten records are great. Folks did not write over old dates in those days! We have brought the old pre-computer imformation into our database with a DaysOut field and an AsOfDate field. For example, Willy Makeit, a lead trumpet player, was admitted on 2/25/46 and has been out of the union for 656 days, I wrote a simple query to do the math and provide data for a "30-years of membership" field and a 50-years of membership" field in a form.

However, there is new data to be considered after the AsOfDate.

Due to the limited history in this database all I have to work with is a History Table. A query of Member Number 1072 yields the following data:


Date Desc MusID
2/4/2003 Suspended for non-payment of dues 1072
2/21/2003 Dues thru 3/31/03 (Rec# 19401). Status was Suspen 1072
11/7/2003 Suspended for non-payment of dues 1072
12/15/2003 Dues thru 12/31/03 (Rec# 24630). Status was Suspe 1072
2/9/2004 Suspended for non-payment of dues 1072
4/5/2004 Dues thru 6/30/2004 (Rec# 28110). Status was Susp 1072
10/31/2004 Suspended for non-payment of dues 1072
7/31/2005 Suspended for non-payment of dues 1072
9/30/2005 Expelled for non-payment of dues 1072
10/17/2005 Dues thru 12/31/2005 (Rec# 51852). Status was Exp 1072
1/31/2006 Suspended for non-payment of dues 1072
3/10/2006 Dues thru 6/30/2006 (Rec# 58927). Status was Susp 1072
7/31/2006 Suspended for non-payment of dues 1072
8/9/2006 Dues thru 12/31/2006 (Rec# 64736). Status was Sus 1072
1/31/2007 Suspended for non-payment of dues 1072

My goal is to write a code that selects the first *non* date and the next *was* date and subtracts the difference. The aggregate of each date difference will be the total DaysOut.


Please excuse me if this post is too long winded. Basically I need to get the next date in the record and subtract it from the first previous date and add up each group of two. If the last date is a *non* it will be subtracted from Date()

Any help will be greatly appredciated.

Unionhorse

[sierra7]

Hi Horse
I'm looking at your data and want to check that I understand properly before starting to think about a program. This is going to be tedious but there is no point doing it wrong.

1. The first record shows that 1072 was suspended on 4-Feb-2003, so presumably his membership pre-dates this but these days will not be included, hence can only calculate DaysOut. (It would be nice to run a total for DaysIn as across-check.)
2. On 21-Feb-2003 the record indicates dues were thru but what does the date 31-Mar-2003 indicate? Which of these two dates are to be used? The first date is easy but extracting the second date is going to be 'more challenging'.
3. On 31-Oct-2004 the guy was Suspended but the record on 31-Jul-2005 say he was suspended again and there is no in-between record of membership. He was finally Expelled on 30-Sep-2005 and not a member again until 17-Oct (or 31-Dec ?) 2005. Are the TotalDaysOut calculated between 31-Oct-2004 and 17-Oct (31-Dec?) 2005, or something more difficult ?
4. Is you data sufficiently consistant that the first letter of Description will be either D,S or E? so these can be used to interpret the 'ins' and 'outs' ?

S7

[unionhorse]

Hi Horse
I'm looking at your data and want to check that I understand properly before starting to think about a program. This is going to be tedious but there is no point doing it wrong.

1. The first record shows that 1072 was suspended on 4-Feb-2003, so presumably his membership pre-dates this but these days will not be included, hence can only calculate DaysOut. (It would be nice to run a total for DaysIn as across-check.)
2. On 21-Feb-2003 the record indicates dues were thru but what does the date 31-Mar-2003 indicate? Which of these two dates are to be used? The first date is easy but extracting the second date is going to be 'more challenging'.
3. On 31-Oct-2004 the guy was Suspended but the record on 31-Jul-2005 say he was suspended again and there is no in-between record of membership. He was finally Expelled on 30-Sep-2005 and not a member again until 17-Oct (or 31-Dec ?) 2005. Are the TotalDaysOut calculated between 31-Oct-2004 and 17-Oct (31-Dec?) 2005, or something more difficult ?
4. Is you data sufficiently consistant that the first letter of Description will be either D,S or E? so these can be used to interpret the 'ins' and 'outs' ?

S7

Hello S,

Thank you for your quick response.

1. Yes, his AdmitDate pre-dates 4-Feb-2003. I have a field in a table that already has his previous total DaysOut as of a certain date. This was a huge job done by hand. A total DaysIn is a good idea if things work out.

2. On 21-Feb-2003 the member paid his dues. This is the relevant date. His membership was active thru 31-Mar-2003 which is the end of the first quarter. Dues can be paid by the quarter. So, the dates in the Date field are the ones we need to use.

3. The data in the Description is consistent. This data was culled from
a larger group which includes a descriptive history of routine dues payments and receipt numbers. I used the wildcard *non* and *was* for consistent 'ins' and 'outs' but other wildcards could work.

This guy had some health problems so we let him slide for another quarter before expelling him (see we are not that hardcore in Detroit!) Yes, the TotalDayOut are calculated between 31-Oct-2004 and 17-Oct-2005

I am not being lazy writing this code.
After trying several access functions like date difference and date maximum I hit a wall.

Thanks again,

UH

[sierra7]

Hi again
Thanks for answering those queries. I always prefer to ask the silly questions up-front.

I know in essence what is needed and will try and put some code together tomorrow if no one your side of the 'pond' beats me to it!


S7

[sierra7]

Hi UH

Sorry I'm a bit late getting this up together. It's been a busy day.

I've built a little application that has a subroutine at the heart that calculates Days In and Days Out for any given Musician ID.

At present you just pick a MusicianID from a combo box, then press the button to calculate his days. There are some cross checks but I haven't had time to take it step by step to make sure you will get the right answer. I'll leave that to you!

There's a button to write the sums back to the musicians file but is just a demo of the SQL code needed; you could make the form 'Bound' to the Musicians table then just copy the results int TextBoxes.

If you want to automate the whole process, you would need to create another recordset for your main Musicians table, then step through that passing the MusicianID the the routine I have written. You would then update your recordset with the calculated values.

My routine is like;-

 
Private Sub getDays(Musician As Long)
'a recordset called "rst" has been opened by the Form_Open event
'and is now available to all procedures on this form
Dim strCriteria As String 'string variable to hold the search criteria
Dim blnInOut 'Boolean to remember whether 'In' or 'Out'
Dim datDate 'Date of previous record
 
'initialize variables
lngCountOut = 0
lngCountIn = 0
lngPeriod = 0
datDate = 0
'set up the criteria for finding Musician passed into this function
strCriteria = "[MusId]=" & Musician
'move to the first record on the recordset for this Musician
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No data found for this Musician", vbExclamation, "No Data"
'data must have been found
Else 
'create a loop to check all entries for this Musician in the recordset
With rst
Do While Not rst.NoMatch 'loop until next Musician
If datDate = 0 Then 'on first record
'store this date
datDate = !ddate
 
'record total days to Today for cross checking
lngPeriod = DateDiff("d", datDate, Date)
 
'check whether 'In' or 'Out'
If Left(!descr, 1) = "D" Then
blnInOut = True
Else
blnInOut = False
End If
'nothing more to do so allow loop to move to Next record
Else
'compute number of days
If blnInOut = False Then
lngCountOut = lngCountOut + DateDiff("d", datDate, !ddate)
Else
lngCountIn = lngCountIn + DateDiff("d", datDate, !ddate)
End If
 
'now update variables from current record
'store the date
datDate = !ddate
 
'check whether 'In' or 'Out'
If Left(!descr, 1) = "D" Then
blnInOut = True
Else
blnInOut = False
End If
'nothing more to do so allow loop to move to Next record
End If
'now move to next record for this Musician
rst.FindNext strCriteria
 
'loop to calculate dates again
Loop
'now out of Loop so must have NoMatch and now on next Musician
'calculate additional days between last record and upto Today
If blnInOut = False Then
lngCountOut = lngCountOut + DateDiff("d", datDate, Date)
Else
lngCountIn = lngCountIn + DateDiff("d", datDate, Date)
End If
End With
End If
 
End Sub
 

I hope this helps.
I am away for a week now but I think this has broken the back of the promlem. If you have any difficulty I'm sure there are others to help.

Best of luck

S7

[unionhorse]

S7,

Thank you so much. Hopefully I will have had success when you check back in.

Gratefully,

UH

[sierra7]

Hi UH

There are a couple of things that I forgot to mention last night in my rush.

Firstly, I have avoided using the field names 'Date' and 'Desc' ! These are reserved words. 'Date' means 'Today' and 'Desc' means sort descending

You can get away with it some of the time, but sooner or later Access will 'sneak up behind yah' and give what Microsoft call an 'unexpected result' and you can't fathom out why. I've just changed them to 'DDate' and 'Descr'

The other point I did not mention is that the routine uses 'DAO' type recordsets that are set up when the form opens. First I declared the variables so they would be available to all procedures in the form;-

 
Option Compare Database
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngCountOut As Long     'count days Out
Dim lngCountIn As Long     'count days In
Dim lngPeriod As Long        'count from first record to Today for checking only
 

Then in the Form_Open event I opend the recordset

 
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
'query for selecting History data, ordered by Musician and then date of transaction
strSQL = "SELECT tblDuesHist.MusID, tblDuesHist.DDate, tblDuesHist.Descr " & _
            "FROM tblDuesHist ORDER BY tblDuesHist.MusID, tblDuesHist.DDate;"
 
Set db = CurrentDb
'now open a recorset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'move to the end of the recordset and then back to begining to make sure it's poulated
rst.MoveLast
rst.MoveFirst
'check that some data is found
If rst.EOF And rst.BOF Then
    MsgBox "No data found "
End If
 
End Sub
 

This should be OK in the demo database I sent but if you copy stuff into your own system you will have to 'open a Reference to DAO'

To do this you open a VB window (e.g. open a form in Design mode then click View > Code on the main tool bar), then click Tools > References and scroll down the list to find Microsoft DAO 3.6 Object Library, check it then close-up.

I will create a link to this for an Expert to monitor and who may want to advise how the code can be modified to ADO, which is the more upto date method of data handling.

That's about it

Best of luck

S7

[unionhorse]

Hi UH

There are a couple of things that I forgot to mention last night in my rush.

Firstly, I have avoided using the field names 'Date' and 'Desc' ! These are reserved words. 'Date' means 'Today' and 'Desc' means sort descending

You can get away with it some of the time, but sooner or later Access will 'sneak up behind yah' and give what Microsoft call an 'unexpected result' and you can't fathom out why. I've just changed them to 'DDate' and 'Descr'

The other point I did not mention is that the routine uses 'DAO' type recordsets that are set up when the form opens. First I declared the variables so they would be available to all procedures in the form;-

 
Option Compare Database
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngCountOut As Long     'count days Out
Dim lngCountIn As Long     'count days In
Dim lngPeriod As Long        'count from first record to Today for checking only
 

Then in the Form_Open event I opend the recordset

 
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
'query for selecting History data, ordered by Musician and then date of transaction
strSQL = "SELECT tblDuesHist.MusID, tblDuesHist.DDate, tblDuesHist.Descr " & _
            "FROM tblDuesHist ORDER BY tblDuesHist.MusID, tblDuesHist.DDate;"
 
Set db = CurrentDb
'now open a recorset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'move to the end of the recordset and then back to begining to make sure it's poulated
rst.MoveLast
rst.MoveFirst
'check that some data is found
If rst.EOF And rst.BOF Then
    MsgBox "No data found "
End If
 
End Sub
 

This should be OK in the demo database I sent but if you copy stuff into your own system you will have to 'open a Reference to DAO'

To do this you open a VB window (e.g. open a form in Design mode then click View > Code on the main tool bar), then click Tools > References and scroll down the list to find Microsoft DAO 3.6 Object Library, check it then close-up.

I will create a link to this for an Expert to monitor and who may want to advise how the code can be modified to ADO, which is the more upto date method of data handling.

That's about it

Best of luck

S7

Hello S7,

Call me crazy but I have been very stubborn about
staying with Access 97. I know eventually this might have to change but for us, 97 is more efficient with less bloat. The database is split with two frontends totaling around 7 megs and a backend around 45 megs. Our Lan has 6 users and things are working quite snappy. I covered your database to 97 and unchecked a missing dll. It is working great and I feel I can incorporate it into our system quite nicely.

The concept of a floating variable going from record to record was way beyond my brain cells. I think this is just what I need and will work great. You nailed it.

Thanks,

Unionhorse

[sierra7]

Hi Unionhorse

Glad to hear it.

If you are still on Access'97 then forget the bit about ADO in my last post. This only came in with 2000 and then you had to add that Reference bit to DAO to be backwardly compatible.

In fact you had better take all the 'DAO.' references out because it will probably confuse Access'97

I hope all goes well

S7

[Jim Doherty]

Subscribing to the thread for moderation purposes