By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Membership History

P: 8
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
Feb 6 '08 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 446
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
Feb 6 '08 #2

P: 8
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
Feb 6 '08 #3

Expert 100+
P: 446
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
Feb 7 '08 #4

Expert 100+
P: 446
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;-
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub getDays(Musician As Long)
  3. 'a recordset called "rst" has been opened by the Form_Open event
  4. 'and is now available to all procedures on this form
  5. Dim strCriteria As String 'string variable to hold the search criteria
  6. Dim blnInOut 'Boolean to remember whether 'In' or 'Out'
  7. Dim datDate 'Date of previous record
  8.  
  9. 'initialize variables
  10. lngCountOut = 0
  11. lngCountIn = 0
  12. lngPeriod = 0
  13. datDate = 0
  14. 'set up the criteria for finding Musician passed into this function
  15. strCriteria = "[MusId]=" & Musician
  16. 'move to the first record on the recordset for this Musician
  17. rst.FindFirst strCriteria
  18. If rst.NoMatch Then
  19. MsgBox "No data found for this Musician", vbExclamation, "No Data"
  20. 'data must have been found
  21. Else 
  22. 'create a loop to check all entries for this Musician in the recordset
  23. With rst
  24. Do While Not rst.NoMatch 'loop until next Musician
  25. If datDate = 0 Then 'on first record
  26. 'store this date
  27. datDate = !ddate
  28.  
  29. 'record total days to Today for cross checking
  30. lngPeriod = DateDiff("d", datDate, Date)
  31.  
  32. 'check whether 'In' or 'Out'
  33. If Left(!descr, 1) = "D" Then
  34. blnInOut = True
  35. Else
  36. blnInOut = False
  37. End If
  38. 'nothing more to do so allow loop to move to Next record
  39. Else
  40. 'compute number of days
  41. If blnInOut = False Then
  42. lngCountOut = lngCountOut + DateDiff("d", datDate, !ddate)
  43. Else
  44. lngCountIn = lngCountIn + DateDiff("d", datDate, !ddate)
  45. End If
  46.  
  47. 'now update variables from current record
  48. 'store the date
  49. datDate = !ddate
  50.  
  51. 'check whether 'In' or 'Out'
  52. If Left(!descr, 1) = "D" Then
  53. blnInOut = True
  54. Else
  55. blnInOut = False
  56. End If
  57. 'nothing more to do so allow loop to move to Next record
  58. End If
  59. 'now move to next record for this Musician
  60. rst.FindNext strCriteria
  61.  
  62. 'loop to calculate dates again
  63. Loop
  64. 'now out of Loop so must have NoMatch and now on next Musician
  65. 'calculate additional days between last record and upto Today
  66. If blnInOut = False Then
  67. lngCountOut = lngCountOut + DateDiff("d", datDate, Date)
  68. Else
  69. lngCountIn = lngCountIn + DateDiff("d", datDate, Date)
  70. End If
  71. End With
  72. End If
  73.  
  74. End Sub
  75.  
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
Attached Files
File Type: zip Dues.zip (24.1 KB, 39 views)
Feb 7 '08 #5

P: 8
S7,

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

Gratefully,

UH
Feb 7 '08 #6

Expert 100+
P: 446
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;-
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Dim db As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim lngCountOut As Long     'count days Out
  6. Dim lngCountIn As Long     'count days In
  7. Dim lngPeriod As Long        'count from first record to Today for checking only
  8.  
Then in the Form_Open event I opend the recordset
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Open(Cancel As Integer)
  3. Dim strSQL As String
  4. 'query for selecting History data, ordered by Musician and then date of transaction
  5. strSQL = "SELECT tblDuesHist.MusID, tblDuesHist.DDate, tblDuesHist.Descr " & _
  6.             "FROM tblDuesHist ORDER BY tblDuesHist.MusID, tblDuesHist.DDate;"
  7.  
  8. Set db = CurrentDb
  9. 'now open a recorset
  10. Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  11. 'move to the end of the recordset and then back to begining to make sure it's poulated
  12. rst.MoveLast
  13. rst.MoveFirst
  14. 'check that some data is found
  15. If rst.EOF And rst.BOF Then
  16.     MsgBox "No data found "
  17. End If
  18.  
  19. End Sub
  20.  

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
Feb 8 '08 #7

P: 8
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;-
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Dim db As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim lngCountOut As Long     'count days Out
  6. Dim lngCountIn As Long     'count days In
  7. Dim lngPeriod As Long        'count from first record to Today for checking only
  8.  
Then in the Form_Open event I opend the recordset
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Open(Cancel As Integer)
  3. Dim strSQL As String
  4. 'query for selecting History data, ordered by Musician and then date of transaction
  5. strSQL = "SELECT tblDuesHist.MusID, tblDuesHist.DDate, tblDuesHist.Descr " & _
  6.             "FROM tblDuesHist ORDER BY tblDuesHist.MusID, tblDuesHist.DDate;"
  7.  
  8. Set db = CurrentDb
  9. 'now open a recorset
  10. Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  11. 'move to the end of the recordset and then back to begining to make sure it's poulated
  12. rst.MoveLast
  13. rst.MoveFirst
  14. 'check that some data is found
  15. If rst.EOF And rst.BOF Then
  16.     MsgBox "No data found "
  17. End If
  18.  
  19. End Sub
  20.  

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
Feb 8 '08 #8

Expert 100+
P: 446
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
Feb 8 '08 #9

Jim Doherty
Expert 100+
P: 897
Subscribing to the thread for moderation purposes
Feb 8 '08 #10

P: 8
Subscribing to the thread for moderation purposes
Dear Sierra7 & Jim,

I have incorporated S7's code into our database and it is working great. Each Members account is automatically updated with a membership history when selected.

Thanks again. This is an excellent forum

Unionhorse
Feb 12 '08 #11

Jim Doherty
Expert 100+
P: 897
Dear Sierra7 & Jim,

I have incorporated S7's code into our database and it is working great. Each Members account is automatically updated with a membership history when selected.

Thanks again. This is an excellent forum

Unionhorse

For once in my miserable life I havent advised on anything here LOL and still get a thankyou. Nice one Sierra :) and nice to see you think the site rocks union. Thats what I thought too when I first came on.

Jim
Feb 12 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.