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
11  2351 
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. - 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.)
- 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'.
- 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 ?
- 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
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.- 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.)
- 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'.
- 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 ?
- 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
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
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
S7,
Thank you so much. Hopefully I will have had success when you check back in.
Gratefully,
UH
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
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
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
Subscribing to the thread for moderation purposes
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ken Post |
last post by:
Hello-
I'm fairly new to writing SQL statements and would greatly appreciate
some help on this one.
I'm working on a project for a non-profit that I volunteer for. Part
of the database...
|
by: Paul Keegstra |
last post by:
Hi,
I am currently working on an asp.net 2.0 web site that is a replacement of a
classic asp web site. The current web site uses a Commerce Server 2002
database for storing user information. ...
|
by: Balaji |
last post by:
Hi All,
Can I use more than one membership provider for a given website? I
understand only one of them could be default one. If yes, then how to
programmatically access the other membership...
|
by: ryan.mclean |
last post by:
Hello everyone,
I am wondering, can the membership provider be changed at runtime?
Perhaps the connectionStringName?
I would like to use a different database based on the server the site
is...
|
by: chris |
last post by:
I've been thinking about adding to my asp.net 2.0 web project (ecommerce) the
use of the membership and login control capability. I've read a lot but still
don't quite understand the practical use....
|
by: =?Utf-8?B?Q2hyaXMgQ2Fw?= |
last post by:
I have been having some trouble with implementing a custom Membership
Provider. We have a custom data store and business logic that pulls user
information. I need some level of functionality...
|
by: stj911 |
last post by:
http://counterpunch.org/rahni04072007.html
Test Tube Zealots: The American Chemical Society Terminates the
Membership of Chemists from Iran
By DAVID N. RAHNI
The American Chemical Society...
|
by: Glenn |
last post by:
My current classic-ASP site has users, projects, roles and the 2.0
membership looks like a perfect fit, but I'm having trouble finding
examples of how to have users that belong to different...
|
by: =?Utf-8?B?ZVByaW50?= |
last post by:
Asp.Net v2.0
I have created a web application and I am using it from a single website and
database. The web application has different ‘portals’ – each independent and
I am using the...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
