Auto Deletion & Addition of data to several Tables

[jcbhydro]

Good Evening,

In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.

The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.

It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.

The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..

My problem is; I am hesitant to start any experiments until the protocol is clearer in my mind.

Any suggestions would be extremely helpful.

jcbhydro

 

[MarkK]

Generally if you move data table to table in a database, you have a design flaw.

Data that differs by status is most easily distinguished by the value of a field, not the table the row is in. Imagine having a Person table with fields like IsForeigner, IsMember, IsPendingMember, and so on. If a foreigner becomes a member, you change the values of fields, you don't move the row.

Rule of thumb: Don't move data, design tables that completely describe your data, then edit those descriptions in situ.

 

[jcbhydro]

Thank you for your comment, but you are missing the point.

I have no intention of cluttering up the main table with prospective members most of whom will probably never become real members.

I will continue to manipulate the data manually, although I cannot believe that it is impossible to automate. Procedures of this type were certainly possible with dBase3 and Lotus Approach.

Jcbhydro

 

[MarkK]

Lol, yeah, one of us missed the point. All the best,

 

[jdraw]

I really don't think Mark missed your point. He, and I agree, is telling you that moving data from table to table is not what relational database is about.

I have no intention of cluttering up the main table with prospective members most of whom will probably never become real members.

But your "business processes" deal with prospective members and real members. Your database should support your business. Step back and conceptualize a "MemberEntity" or some term that makes sense to you (Mark used Person) that includes all of the members whether foreign, prospective, real....... Then some sort of status flag that identifies the members' status whether real,prospective, alive,dead..(again whatever makes sense in your business).

A change in the member's status is handled with some indicator, not by moving records from table to table. I'm not saying you can't do that, it seems you did it with dbase3 and/or lotus approach. What I am suggesting is, it is not good design.

I will continue to manipulate the data manually,

this is also not a strategy if you plan to automate.

You may get some more insight using super and sub type concepts described here.

Good luck.

 

[jcbhydro]

Many thanks for your comments.
I recognize precisely what you are advocating, but as I said, I have no wish to clutter up the main table of the database with records, many of which will never have significance.

Even if I abandon the proposal to transfer data into a 3rd table, I am still of the opinion that, on entering [MemberName] data into the [Mail List] table it should be a relatively simple matter to automate the deletion of the record having equivalent data from the [Foreigner] Table.

I assume this could be achieved by a macro, triggered by a button on the Entry form. Or would there be a way of doing it with a query?

Regards,

jcbhydro

 

[CJ_London]

I agree with both Markk and JDraw, however if you really want to go this route, I would google 'archiving data' or similar since the process is similar

To get you started, here is a link

http://www.databasedev.co.uk/automate_ms_access_archive.html

The routine is pretty straightforward but doesn't cover other things to consider such as rollback if one of the transactions fails.

 

[gemma-the-husky]

Cluttering up is not an issue.

The database will handle many more records than you will ever need without any speed deterioration.

If you were bothered you could use the single table idea, and delete/purge the lapsed applications after a period.

It's far easier than moving stuff from table to table.

 

[jdetterline]

Thank you for the answer CJ_London. I have a similar need.

-And it's not a matter of clutter as far as speed, it's a matter of separating things so that breezing through a table is easier.

Your link is much appreciated.