table transfers

[agorman]

Hi,

I wounder if anyone can help me with the following process.

I have 2 tables with exactlly the same fields: one table called 'membership' and the other table called 'archived'. Normally, when I want to archive an individual's details (when they resign, or do not re-new membership etc) I simply cut and past from one table to the other.

I have an 'Archived yes/no' box in both tables, and wondered if there was a way to automate this process - ie when the archived box is ticked the 'membership' table the record would be automatically moved to the 'archived' table (and when un-ticked in the 'archived' table, it would be moved back to the 'membership' table)

Thanks

Adrian

 

[doco]

Sounds like you need a Union Query. Some simple examples here

http://www.blueclaw-db.com/accessquerysql/union_combo_box_all.htm

 

[agorman]

Thanks for that Doco, but I am afriad it has gone right over my head. Is there an example of moving records from one table to another?

Regards

adrian

 

[georgedwilkinson]

Adrian,

You cannot do what you're describing (if I understand it correctly) with a single query. It will take 4.

Best solution is to use VBA or a macro to call the 4 queries to automate this.

To keep it simple, just write 4 queries to do the work. You should get the 4 queries working correctly before automating with VBA/Macro.

Two of the queries will be append queries and the other 2 will be delete queries. In all fairness to yourself you should look this up before you go any further.

The sql of the append queries will be something like:

insert into archived (primarykey, field1, field2, field3, [Archived yes/no box])
select primarykey, field1, field2, field3, [Archived yes/no box] from membership
where [Archived yes/no box] = True;

For the other append query, use the same sql but change the 2 table names.

The delete query will look something like this:

delete from membership
where [Archived yes/no box] = True and primarykey in
(select primarykey from archived);

For the second delete query, change the table names.

After you get this working correctly (in a copy of your database), post back and somebody will help you automate it.

Remember, append first, delete after. Also, all the code I have written is off the top of my head and based on absolutely no knowledge of your system. As such, you should not depend too heavily on it, depending instead on the general answer I've given you along with your personal knowledge of the system.

 

[boblarson]

Unless you have thousands and thousands of members (as a performance issue), I would just use the checkbox in the main table to make them active or inactive and not move them. Just base everything on a query that has active members only (all of the fields from the table and then with only those who have the Inactive checkbox not checked).

Would save a lot of hassle and then you can easily report out on all of them if you need to.

 

[georgedwilkinson]

Definitely. All of my tables have a "Valid" flag which is used instead of delete. But, it's up to you how you implement, especially if you have hundreds of thousands or more rows.

 

[agorman]

Hi Guys,

Thanks for all your help. I would not be having thousands of members needing to be archived, just the odd 1 or 2 now and again.

I have managed to do 2 queries so far:

An append query to move an individual record if the box is checked, to the members_archive table,

and another append query to move the record that is un-checked in the archive table, back to the origional table.

I was about to experiment with delete querries - but the idea of marking a record as invalid somehow seems much easier.

I would much rather be able to mark an individual record so that it does not show up in a members list printout, or label printing, or other queries run on that table. - in effect archiving that record if I understand you correctly.

How do I make a record in my members table active or inactive ?

Can I simply 'Mark' a record as inactive, or do I have to exclude the record (if checked) in each query I run?

Regards again

Adrian

 

[boblarson]

I told you how to do it. All you do is have the field InActive and then you create a single query with all of the table fields and use the criteria of False on the InActive field. Use that query in all other queries, etc. in place of the regular table. That way you only have to build one query to pull the active users and you don't have to do it in each query.

 

[agorman]

Thanks Bob,

I will have a go

 

[agorman]

Thankyou George, Bob and Doco for your help.

I managed to get it working in the end by having a query with the check box named 'archive' set to 'no' which doesn't show the members when the check box is selected on the form. - Much easier than what I was attempting to do - although I have learned about append and delete queries - I even found where to find the sql code in a query.

I have 2 books ''How to do everything with Access 2003' and 'Forms Reports and Queries', and although they are good, sometimes they are not that clear.

Thanks to all of you for your help

Regards

Adrian

 

[khawar]

Worked out a little sample for you to manage archived and unarchived members



Khawar