best way to archive records?

I am creating a membership database for a church.  Individual 
information is in three basic tables:

tblFamily
FamLastName (for entire family)
Address

tblIndividual
FirstName
MiddleName
LastName (takes care of hyphenated married names, or different names for 
children, etc.)
BirthDate
MarriageDate

tblMembership
JoinDate
JoinManner
TerminationDate
TerminationManner
and more...

Once a person leaves the membership of the church I need to "archive" 
the information.  It needs to be kept for our permanent records.  What 
is the best way to do this?  My two thoughts would be:

1) set up duplicate tables and move the "archive" information to it

or

2) Create an active/not active field in the tblIndividual and keep all 
the information, both active and archived in the original set of tables.

Is one way or the other better or more efficient?  Is there some other 
way that I haven't thought of that would work better?

Thank you for your help and input!

Kathy R.
0
Kathy
2/11/2010 3:59:29 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

3 Replies
550 Views

Similar Articles

[PageSpeed] 14

My preference is to add a TerminationDate field.  That way, I don't have to 
find other tables or other databases to pull up historical information.

By the way, your tblMembership doesn't appear to include "members".  Do your 
"and more..." fields include either a [FamilyID] foreign key or a 
[IndividualID] foreign key?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Kathy R." <wild_rose@graffiti.net> wrote in message 
news:nPVcn.71287$s%.12830@newsfe18.iad...
>I am creating a membership database for a church.  Individual information 
>is in three basic tables:
>
> tblFamily
> FamLastName (for entire family)
> Address
>
> tblIndividual
> FirstName
> MiddleName
> LastName (takes care of hyphenated married names, or different names for 
> children, etc.)
> BirthDate
> MarriageDate
>
> tblMembership
> JoinDate
> JoinManner
> TerminationDate
> TerminationManner
> and more...
>
> Once a person leaves the membership of the church I need to "archive" the 
> information.  It needs to be kept for our permanent records.  What is the 
> best way to do this?  My two thoughts would be:
>
> 1) set up duplicate tables and move the "archive" information to it
>
> or
>
> 2) Create an active/not active field in the tblIndividual and keep all the 
> information, both active and archived in the original set of tables.
>
> Is one way or the other better or more efficient?  Is there some other way 
> that I haven't thought of that would work better?
>
> Thank you for your help and input!
>
> Kathy R. 


0
Jeff
2/11/2010 4:44:16 PM
2.  However I like Jeff's idea of making it a Date field as that would answer 
two question: If they are inactive and when did that happen. 

1. That's almost always a bad idea including moving the records to another 
database. 

If you change the table structure of the 'active' table, but forget to do 
the 'inactive' table, the records might not move correctly. 

Speaking of moving, it becomes a 2 step process of appending to the inactive 
table then deleting from the active. If someone becomes active again, you 
have to do things in reverse.

If you need membership info from both tables, you'll need a Union query 
which are somewhat cumbersome and slow.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Kathy R." wrote:

> I am creating a membership database for a church.  Individual 
> information is in three basic tables:
> 
> tblFamily
> FamLastName (for entire family)
> Address
> 
> tblIndividual
> FirstName
> MiddleName
> LastName (takes care of hyphenated married names, or different names for 
> children, etc.)
> BirthDate
> MarriageDate
> 
> tblMembership
> JoinDate
> JoinManner
> TerminationDate
> TerminationManner
> and more...
> 
> Once a person leaves the membership of the church I need to "archive" 
> the information.  It needs to be kept for our permanent records.  What 
> is the best way to do this?  My two thoughts would be:
> 
> 1) set up duplicate tables and move the "archive" information to it
> 
> or
> 
> 2) Create an active/not active field in the tblIndividual and keep all 
> the information, both active and archived in the original set of tables.
> 
> Is one way or the other better or more efficient?  Is there some other 
> way that I haven't thought of that would work better?
> 
> Thank you for your help and input!
> 
> Kathy R.
> .
> 
0
Utf
2/11/2010 7:36:02 PM
Thank you gentlemen!  Sometimes the best answer is staring you (or me) 
right in the face.  I already have the "TerminationDate" in the 
membership table so I can use that to show whether they are active/inactive.

And yes, Jeff, the "and more" includes foreign keys for both the 
FamilyID and the IndividualID.  Thanks for checking though.  As I said, 
sometimes it's the simplest things that I miss.

Kathy R.

Kathy R. wrote:
> I am creating a membership database for a church.  Individual 
> information is in three basic tables:
> 
> tblFamily
> FamLastName (for entire family)
> Address
> 
> tblIndividual
> FirstName
> MiddleName
> LastName (takes care of hyphenated married names, or different names for 
> children, etc.)
> BirthDate
> MarriageDate
> 
> tblMembership
> JoinDate
> JoinManner
> TerminationDate
> TerminationManner
> and more...
> 
> Once a person leaves the membership of the church I need to "archive" 
> the information.  It needs to be kept for our permanent records.  What 
> is the best way to do this?  My two thoughts would be:
> 
> 1) set up duplicate tables and move the "archive" information to it
> 
> or
> 
> 2) Create an active/not active field in the tblIndividual and keep all 
> the information, both active and archived in the original set of tables.
> 
> Is one way or the other better or more efficient?  Is there some other 
> way that I haven't thought of that would work better?
> 
> Thank you for your help and input!
> 
> Kathy R.
0
Kathy
2/16/2010 8:17:13 PM
Reply:

Similar Artilces:

Best to have all one's styles in "Normal Template" ?
I assume one can save styles to other than one's Normal Template. Do the experts recommend also saving any added styles to the Normal Template so that one Template contains all of one's styles? Or perhaps, if it is advisable to have one template have them all, it is best to have that repository be a template other than the Normal Template? Thanks for any advice. -- Norm Hi Norm: I recommend having different templates for the different types of documents that you create. In them, I suggest keeping the built-in styles defined for each document type. I rar...

Append New Record Via A Form?
Have never used Append Queries and therefore lack the knowledge of the same. I have a sample table "Students" with fields like: 1. ID (AutoNumber) 2. Name (Text) 3. Nationality (Lookup with Row Source being Selection from the table "Countries") 4. Date of Birth (Date) Now I want to create a form which would not be showing the records already in the database but simply present a one page layout with the 4 controls for each of the fields and a button which upon being clicked shall add the data entered in the same to the Table "Students". Please c ...

can't delete record
I am trying to use a command button on a form to delete the current record displayed on the form. The record has several related fields in other tables, but I believe I have successfully deleted all related records with delete queries. When I run the code DoCmd.RunCommand acCmdDeleteRecord the confirm delete message comes up, and after clicking through it the form goes blank, appearing like a successful deletion. No error messages. However the record is still in the table. In table view there isn’t any problem deleting the record in question i.e. no related records. I’ve tried using a w...

print a record
I have a multi user application and I would like the user to be able to print a report anytime after they have submitted the data. The user inputs data on a form. The user may need to print a report of that data a week later. Is there anyway for the user to view and/or print just the record they submitted? Shari The line of code for a command button on the form with the record to be printed: DoCmd.OpenReport "ReportName", acViewPreviw, , "IDField = " & Me.txtIDField where ReoportName is the report's name, IDField matches the ID field name and txtIDField ...

Is there a way to delete multiple lines at the same time beside u.
Please let me know if there is other ways to delete multiple lines at the same time without using the Ctl key. Thank you. Hi Cherry are the lines adjacent to one another? if so just drag down the row numbers and then right mouse click & choose delete .. .if they're not adjacent and you don't want to use the Ctrl key to select them, how are you going to identify which rows you want to delete? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "cherry" <cherry@discussions.microsoft.com> wrote in message news:4BD...

Combo Box
Greetings, I had an issue of how to populate my combo box with unique values only, after some research I discovered collections and how they dont allow duplicate values and I could simply skip the error to achieve the results I wanted. However how can I modify the code to sort the values in the collection (Unique) in alphabetical order? I've seen some complex looking loops based on the old bubble sort routines that I wrote many many moons ago, but wondering what other peoples solutions would be? Any assistance would be appreciated. Cheers Rob 'Populate combo box with unique...

find affected tables when creating a new record in CRM frontend
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! On Nov 9, 12:12=A0pm, VistaUser123 <sunmapleleaf...@yahoo.com> wrote: > How can I find out what tables which new record has been added on when I > enter data through a form in CRM front end? I try to find way to map form= in > the CRM frontend to the tables at the CRM database at backend? It is CRM = 3.0 > > Thank in...

Ole objects in records/tables
I have a question every time I tried to go to word and powerpoint and says type a prodect key I typed from the back of my labtop but it keeps saying it wrong.So what do I do? WHAT??????? "Kevin Ramos" <kevin's email> wrote in message news:%23X$8BdeQIHA.2268@TK2MSFTNGP02.phx.gbl... >I have a question every time I tried to go to word and powerpoint and says >type a prodect key I typed from the back of my labtop but it keeps saying >it wrong.So what do I do? ...

Unique records in Excel
Hi does anyone know how to ensure Unique data when making entries in an Excel spreadsheet ? Say Part Numbers or account numbers etc Thanks :confused: -- gilsondg ------------------------------------------------------------------------ gilsondg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15878 View this thread: http://www.excelforum.com/showthread.php?threadid=273641 There's a method using Data Validation and COUNTIF. Debra Dalgleish's website has an example of how to set this up: http://www.contextures.com/xlDataVal07.html HTH Jason Atla...

What graphic file format prints best out of Excel?
I'm having trouble with my graphics printing out clearing from Excel. I've imported some logos onto a spreadsheet and it look nice on the screen but prints very fuzzy. Any suggestions? Thanks! Maybe Printer Setup............set for "best quality" Vaya con Dios, Chuck, CABGx3 "MCmomma" wrote: > I'm having trouble with my graphics printing out clearing from Excel. I've > imported some logos onto a spreadsheet and it look nice on the screen but > prints very fuzzy. Any suggestions? Thanks! ...

Works Way too slow
Money uses 100% of my CPU time to do anything. I tried to archive transactions and that didn't help. Any suggestions? In microsoft.public.money, Wblackbu wrote: >Money uses 100% of my CPU time to do anything. I tried to archive >transactions and that didn't help. Any suggestions? The archive file is a copy of your Money file at the time that you did the archive. You could copy that to a new *.mny filename and open it. Use that as your Money file. It won't help the speed, but it can restore your lost transactions. See FAQ available at http://umpmfaq.info for other inform...

I cannot get my pivotchart to filter data the way I want
On the x-axis I have 1440 data points in a drop down box in a pivotchart. I want to select 60 data points to view at a time, right now I have to individually select all sixty all datapoints to view. Help please. ...

Maintenace Interval/Online defragmentation- Best Practices
We are running Exchange 2003 SP1, We have one storage group with 5 databases under that storage group. What is the best practices when it comes to scheduling the maintenance interval for the databases? Right now, we are running it everyday from 1 AM till 7 AM on all databases. I think this is default. Is it better to just do one database per day? Our backups are running at this time also. Any suggestions or comments? On Fri, 25 Feb 2005 05:41:05 -0800, "Sabo, Eric" <SaboEric@discussions.microsoft.com> wrote: >We are running Exchange 2003 SP1, We have one stora...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Find Record in Subform
I currently have a find record command button on the main form. When I use the main form find button it only searches fields in the main form. I would like the ability to search ALL the records in a subform using a find record command button. Currently when I put a find record button on the subform it only searches the record currenly displayed. Is there any way to seach (for example) ALL the Titles in that subform table? On Nov 5, 2:43 pm, Reb <RebKayGom...@gmail.com> wrote: > I currently have a find record command button on the main form. When I > use the main form find button...

My WMP library contains wav files best way to convert to mp3
I'm loading my Vinyl albums into WMP as WAV files. I've completed 100 and now have a very large file (48G). How can I convert the WAV files to MP3 without having to recreate my WMP library. i've split all the tracks and renamed them and added art work. I've converted the wav files to MP3 but when I drag the converted files into my WPM library I get a red X next to track that is associated with that file. If I do nothing will WMP automatically convert the files when I sync a device to the WMP? WMP supports transcoding When syncing, the files will be translate...

Maurice Lacroix Masterpiece Phase De Lune Ladies Watch MP6066-SD501-17E, Best Wristwatch World
Maurice Lacroix Masterpiece Phase De Lune Ladies Watch MP6066- SD501-17E, Best Wristwatch World Click Here To Website : http://www.watchebay.net/Maurice-Lacroix-Masterpiece-Phase-De-Lune-Ladies-Watch-MP6066-SD501-17E.html Wristwatch World: http://www.watchebay.net/ Maurice Lacroix Masterpiece Phase De Lune Ladies Watch MP6066- SD501-17E Information : Brand : Maurice Lacroix Watches ( http://www.watchebay.net/Maurice-Lacroix-Watches.html ) Gender : Ladies Code : MP6066-SD501-17E Also Called : MP6378SS001290 Case Material : Stainless Steel with 50 Diamond...

Prefill Records in Form
I have a table with the 3 columns attached to a form 1 - Experience 2 - Yes/No 3 - Role I want to prefill the experience column with 15 entries for the user to complete when they open the form. Is there a way to do this in Access? Joe, Are you saying you want to provide the user with 15 choices from which to opick one to go in Experience? If so, create a table with the 15 choices and then place a combobox based on that table in the form with Experience as the record source. Stalin wrote: >I have a table with the 3 columns attached to a form > >1 - Experience > >2 - Yes/N...

Best way to record 401k transactions?
My company uses Vanguard for our 401k. When I download into Microsoft Money, I only see my account holdings. Does anyone know an easy way to insert transactions other than manually inputting each one? Here's the info from Vanguard: "Microsoft has chosen to implement 401(k) and similar employer-sponsored retirement accounts by providing only the account's holdings in the Microsoft Money account. When you download and import an OFX file for an employer plan account from Vanguard, Money will update your holdings using "Add Shares" or "Remove Shares" transac...

Unable to archive on outlook 2002
Hi all... I am on outlook 2002. I have tried NUMEROUS times to manually archive old email. Here is what I do: create the pst file first, then within outlook I pull down on file, then archive. I put in the date range I want and let it go on its way. (I use date less than 12/31/2002 - which should get all of 2002). It only does its thing for a few seconds. NO emails are archived what so ever. I have tried numerous times.. and at a loss Any thoughts or suggestions??? Check the Modified date on the items that aren't being archived as you think they should. That's the date...

Using an unbound textbox next to each record in form, with all records showing?
I am working with an inventory system, and would like to have a form that shows all products and their current inventory levels, and next to each of them have a textbox for entering in new inventory levels. Is this possible somehow? I can't seem to so it with continuous forms. Is it possible to have run-time created textboxes? Any ideas would be greatly appreciated. -- justme0010 justme, I'm not sure how the setup you described would be used in an inventory system. You'll have to give us more information as to what data you have, and how you plan to calculate invento...

Restoring Archive PST file
I'm using Outlook 2007. I recently reinstalled Windows on my computer and I imported my old outlook PST file to restore my folders and email. However, there is also an Outlook Archive.pst file that is not being accessed. What is the best way to handle this? Should I just import it? Or is there a way to simply 'attach' it to my existing set up. You should never transfer Outlook data by importing it. Just open any data file that has data you want in the current installation. Since you already archived this data, why do you think you now want it back in your new profile? T...

Use combo to display record on subform?
I am fine with using a combo to find a record on a form, and display all the details about the chosen record in a number of controls. But the very same design doesn't work if it is all on a subform on a tab page. I can select the value in the combo (the name, for example) but the address data doesn't pull through into the address controls. How can I achieve this? Many thanks CW CW, CW wrote: > But the very same design doesn't work ... What "very same design" is that? Can you give a bit more detail about what and how you are doing this please? -- Steve Schapel...

Auto Archive #2
Auto Archive has requested to archive my Outlook folders, I click OK but when checking the Archive section I see no e-mail. The policies are to delete only items older than 12 mos. and the archive.pst file is in the proper place as listed. It is importsnt that these messages be available. Thanks Have you checked to make sure the messages are actually being removed from your primary Outlook file? If they're not, then AutoArchive isn't doing anything to them at all. Check the Modified date on the items that aren't being archived as you think they should. That's the date Ou...

IMF Archive messages.
I am trying to find a program that will go through the Archived messages and delete messages with certain content. Over the weekend we were hit with about 5000 spam (no joke) and it would have been nice to have a program that I could have just put some of the offending words in a file and it would get rid of those messages. I have some good messages that are getting an SCL of 9 that I am tryting to get to come through with out my intervention. but until then we have to look for them. Anyone have something like this? For the good messages (and also for the bad) where you want to all...