shaneferguson Posted December 12, 2005 Share Posted December 12, 2005 Hello, I'm currently creating a membership database. When updating a data for a member, the database creates a scrollable 'history' of all the changes that have taken place for that member. This is like a snapshot of what the member's info was when info was updated/changed. (This needs to be like this for the purposes of the database - the users need to have this history data available). I also have an auto-generating membership number, for when a new member is added. However, the problem i am experiencing, is when i update a member's info and the database creates a new 'history snapshot' - it gives the new history the next membership number, rather than using it's original one. Can anybody suggest a way that i can make the database use exsisting membership numbers when updating member info, and only use a new membership number when a new member is first added..? I have attached a copy of the database. I have put in two example members - The existing membership numbers for these are 1 and 2 (should be just 1), and 3 (should be membership number 2). Many thanks for your time. Kindest Regards, Shane. 121205.zip Link to comment Share on other sites More sharing options...
SlimJim Posted December 12, 2005 Share Posted December 12, 2005 Hi Shane The problem here is that you are auto-numbering the records even though some of the records refer to the same member. In other words the record numbering is going to clash with membership numbering. Obviously with small numbers this is manually adjustable but once you have large numbers of records it will get out of hand. I think what you need to do here is to have an Auto-entered serial number for the record ID and then a different style of auto-enter for membership numbering. For the updating of the membership number can I suggest: 1. Make a new table occurrence of the membership table, call it MembershipNumberControl. 2. Relate it to your existing membership table via record number using the cartesian join. In the relationship dialog sort the MembershipNumberControl records by Membership number Descending. This ensures that the first related record by this relationship has the highest membership number. 3. Assuming that History records are obtained by duplication such a record will already have a membership number so only new records will need on. So do an auto-enter on membership number If(Membership Number = "";MembershipNumberControl::Membership Number + 1;Membership Number) This produces a new membership number one higher than the previous highest but does nothing if there already is a membership number. I have altered your sample - attached. 121205.zip Link to comment Share on other sites More sharing options...
shaneferguson Posted December 13, 2005 Author Share Posted December 13, 2005 Many thanks for your reply. I'll have a look at your suggestions, and amended file straight away. Cheers, Shane :-) Link to comment Share on other sites More sharing options...
shaneferguson Posted December 13, 2005 Author Share Posted December 13, 2005 Hello, Just read your post, and had a look at the amended file that you produced. Fantastic - that's exactly what i was after. Many thanks - i appreciate it. Cheers, Shane. :thankyou: Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6709 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now