Difficult Query!

[pastormcnabb]

Hey Everyone,



I need to make a query/s in order to complete the below task, but I'm not quite sure how to do it!!

I have a booking system for a business in access which records data like, customer information, staff information, and appointments etc. I have a growing amount of customer data and I want to set up an archiving system. So, basically - I want to move any customers who have not had an appointment in the past 5 years out of the main customer table, and into an archving table.

I have tried to do this - but I just dont know how!!

Any ideas?

Thanks

Andrew

[NTC]

I would recommend you not do this. Instead, add a new yes/no field to your table named 'Archive'. then check those that you wish to archive.

in this way if an old customer returns - one simply can uncheck them.

you can filter this clutter out easily in your forms by adding the archive criteria to be 'yes' in the underlying record source queries.

in the db world we rarely actually move records from one table to another. we instead employ a technique such as this. however if you are intent on doing it - then you need query1 that results in the records you want to move. then query2 is an AppendQuery sourced on Q1 records appending to your new ArchiveTable. finally you need query3 a DeleteQuery sourced on Q1 records to delete records from the customer table.

hope this helps.

[Parsonshere]

I would insert a regular module in my visual basic, and paste the following into the module so that it looks like this:
Option Compare Database
Option Explicit
Sub archiveRecords()
With DoCmd
.SetWarnings False

.RunSQL ("INSERT INTO tblArchive ( fldDte ) " & _
"SELECT tblInfo.* " & _
"FROM tblInfo " & _
"WHERE (((tblInfo.fldDte)<DateAdd('m',-60,Date())));")

.RunSQL ("DELETE tblInfo.* " & _
"FROM tblInfo " & _
"WHERE (((tblInfo.fldDte)<DateAdd('m',-60,Date())));")

.SetWarnings True
End With
End Sub
Then you could attach the procedure to the ribbon, Quick Access Toolbar, or a form menu, or a command button, if you know how.
You may want to add an error procedure.

If you just want to create the two queries, substitute your table names for mine in the statements below, then:
1. paste one of them into the SQL View of the query and save.
2. do the same for the other query.
Note: Remember to always append first, otherwise you will lose the records.
INSERT INTO tblArchive ( fldDte )
SELECT tblInfo.*, tblInfo.fldDte
FROM tblInfo
WHERE (((tblInfo.fldDte)<DateAdd("m",-60,Date())));

DELETE tblInfo.*, tblInfo.fldDte
FROM tblInfo
WHERE (((tblInfo.fldDte)<DateAdd("m",-60,Date())));

The structure of my two tables are identical.
Guess you better name the 2nd table a different name in the second query.