Spilt the master table into four (1 Viewer)

Hello,
I'm working on recreating my church's records in access and was originally wanting to take people from the Visitors table and automatically move the to the Members Table (and same with Deceased & Moved), but after some searching found that's not really do able. So now my question is, can I have a master table that has everybody (visitors/members/deceased/moved) and then split that into different tables while keeping the main table (that way I can go into the main table and reassign the person)?

Thanks in advance for the help.

P.S.
If anyone has figured out how to do the original plan, that would be nice too!

I would create a field giving the choices you have outlined in a dropdown then use Make Table Queries to make the new tables from this field as the criteria.

I would always as why though as you can create individual queries or a parameter query to provide the results you want in 1 query.

I want it this way so that it cleans it up a bit and I can still use all my other queries/forms/etc without the clutter of those not needed. (Ex. for the Sunday School Attendance form, I don't need the deceased and moved members.) I'm trying to make it as simple to use as possible, most of the church staff doesn't have a lot of experience with computers or even Access.

If I set it up that way and then later go in and update people (this person moved away, this person moved back, etc) would it just add them to the tables or would it create a new table?

Thank you for your help!

I agree generally with Trevor. If you have a clear set of specifications of what you want to accomplish, so much the better. I suggest you make up some sample questions/queries, even reports, of what you want your new structure to "answer/produce". The more details you have for this output, the easier it will be to see if your structures meet your needs.

Get a list of requirements, before getting too deeply involved in Access.

You may want to research super/subtypes (google and youtube) for other techniques that may or may not apply to your situation.

It's good info to help with decisions eg too complex/overkill for my situation etc.; that's what I'm talking about....

http://www.learndatamodeling.com/dm_super_type.php
http://stackoverflow.com/questions/749891/subtyping-database-tables
http://sqlmag.com/t-sql/sql-design-supertypes-and-subtypes

Again, create some specs so you'll know what the goal is.

Good luck with your project.

The forms etc should use queries as its source so you can just filter based on the criteria you select or add in each query or as mentioned use a parameter query to just filter as necessary.

If you add a search form with the parameters in it only needs to use one source query.

Also jdraw has made some excellent suggestions. Having a clear specification would be the ideal way to go.

Also remember you have stated not everyone at church is good with PC's so if you make as simple as possible, one search from and one button to activate the filter then its simple for your team to use. Why complicate it with multiple options when it isn't necessary...

I know how they want everything, I'm just trying to figure it out. My church is currently using Alpha 4 (an MDOS) program, and since I'm the computer whiz they hired me to recreate this system in Access. (I tried looking up more modern day programs, but my pastor is set on me recreating.) I'm learning Access as I go. I know what I learned in BCIS, but that was the very basics of Access.

Thank you so much for your help! I will try to narrow down what is required so I can find the best route to go.

I hope the following does not sound too melodramatic, but managing a dbs for others is a serious undertaking.

you really do have to persuade the pastor that you should be allowed to complete this the right way.

users engage experts to build databases in the same way they hire plumbers to repair pipes, and hire mechanics to fix cars. It's no good hiring an expert and then telling them how to do the job.

I appreciate you are learning on the job, but if you don't do it right, you will have enormous problems later. You really don't want multiple tables for various classes of church member. You just want a single table for the members, and a field (or maybe multiple fields) to indicate their status. You will want other tables, too, but not for different classes of church member.

Does your comment about "looking up more modern programmes" indicate some doubt about Access? There should be absolutely no worries on that score. However, Access is quite different to a spreadsheet, and even an expert excel user will take time to understand Access.

The problem with Access (any database) is that because they are so powerful, you actually design databases to limit what users can do, and prevent them doing many things. (in the same way as heavily protecting a spreadsheet), and therefore as DBS designer you get called on to design/add facilities for things users "could do themselves" with enough knowledge, but which they are in general not allowed to do because a) they do not have the requisite knowledge and b) the system is therefore designed to not allow them to do those things.

It is very easy to catastrophically damage a database without meaning to, and without being aware. It's akin to a spreadsheet user casually inserting rows and columns without considering the affect on spreadsheet formulas, and so on - but even more dangerous in many respects.

Even DBS designers acknowledge this. A designer will only very rarely work directly with a data table - and will generally use the same form as the users, for the same reasons. You can add validation and integrity procedures to forms in ways you just cannot do with the data directly

Because of all this, the resident Access expert will get involved in lots of support issues, and therefore needs to build the database correctly in the first place to avoid excessive amounts of support. Seriously, if you do not feel confident with the level of commitment required, it would be better to avoid getting involved early on.

Sorry if this sounds over dramatic, and good luck.

Thank you for that. I really appreciate the information. I love learning how programs work and how to manipulate them. I looked up "modern programs" not because I was weary of using Access, but because I was weary of my skills with Access. I feel by time I get this all done and set up, I'll have restarted the whole thing just to clean up the mess I made and only use what I need. That's usually how I work when I learn. I start with one project continue with it, playing, adjusting, manipulating, etc, until I get it where I want it, then start from scratch only keeping what is needed (and keeping the original for reference and testing).