Archiving Data 5

[Only1Abhi]

Hi ppl.
I have 3 tables and I want archives of them plz.

my tables are:

tblCustomer
tblCustomerContact
tblTransaction

tblCustomer has a primary key "Customer ID" and foreign keys of this field in the other 2 tables. There is also a Transaction ID for each transaction the customer carries out.

If a certain customer doesn't make a transaction within (say 6 months) then I would like their details from tblCustomer, tblCustomerContact and tbLTransaction to go to 3 other tables called:

tblArchiveCustomer
tbLArchiveCustomerContact
tblArchiveTransaction.

these 3 new tables all have the same fields and data types to their corrisponding table.

Does any1 know how to do this? Or is there an easier solution?

Many thanks in advance.

 

[danvlas]

And what is the problem? Just use some append queries and you're set. Append first from the parent table, then from the child, so that the referential integrity doesn't kick you out.

But...if your IDs are autonumbers, DO NOT compact the database before entering a new record in each table. Otherwise, if you happened to archive and delete the last record, that number will be re-allocated and you don't want that...

Good luck


Daniel Vlas
Systems Consultant

 

[Garridon]

This Microsoft article tells you how:

http://office.microsoft.com/assistance/2002/articles/acPeriodicallyArchive.aspx

They don't tell you it in the article, but you'll also need to set the warnings to false before you run the queries and set them back to true after you run them. Otherwise Access will ask you if you want to append the records and then ask you if you want to delete them.

Your criteria will probably be something like <Date()-120. I put mine in the OnClose event procedure of a form. Make sure test the archiving very carefully before you implement it! Once that delete query runs, you can't get the records back.

 

[JeremyNYC]

Abhi,

Why do you want to do this? Do you have so much data that it's necessary? My first option for archiving is always to create a date field called ArchiveDate. Then, I just put in the date it was archived. All my queries to pull non-archived data only pull records where that field is null. I can pull records that were archived in a certain period. And I can pull all records or only archived records. It won't necessarily work for your situation, but it's a much easier fix, and it may end up doing exactly what you need.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Only1Abhi]

thanks for the replies danvlas, Garridon and Jeremy.

I'll reply back to tell you how I got on!
And yes, I do have many many records!!!

And Daniel,
Do u mean NEVER EVER compact the database or just before entering a new record. Can u plz explain to me again what this might do?

Many thanks ppl!

Regards,
Abhi

 

[Only1Abhi]

In my append query, when I link tblCustomer to tblArchiveCustomer,

What criteria should I enter in the CustomerID field (Primary Key & Autonumber in tblCustomer) if a CustomerID has not done a TransactionID (from tblTransaction) for 6 months?

i.e. What should be the criteria?

Many thanks!
Abhi!

 

[danvlas]

Abhi...if you read my post once again you'll see it has to do with AutoNumber.
If you have an autonumber set as primary key and delete any other record except the last one and you compact the db, nothing happens, everything is OK. But suppose you move the last record to the archive, delete it from the 'live' database and compact the database, then the disaster is there: the number is allocated once again, and when you'll try archiving that record you'll get the dreadful message 'Duplicate value in index or primary key'.
Compact the db on a regular basis, but never immediately after archiving. Wait until a new record is created so that the 'archived' number is not generated again, and you're free as a bird.
This has only to do with auto-incrementing AutoNumbers (which are used on a large scale, whether i like it or not...)


Daniel Vlas
Systems Consultant

 

[Only1Abhi]

so I understand that If I have my primary key as an Autonumber (which I DO), then I must not compact my database straight after archiveing it!

But what I want it, any customer that has not made a transaction (i.e. any CustomerID not linked with a TransactionID) WITHIN 6 MONTHS is transferred to the corrsiponding archive table and deleted after 6 months MORE.

Can u plz guide me on what to type as the criteria in the append query and what field should I put it on?

Many thanks,
Abhi

 

[danvlas]

Create one query based on tblTransaction. Include the field CustomerID and TransactionDate. Make it a Groups/Totals query (View-Totals). In the Totals row for customerID, select Group By. In the totals row for TransactionDate, select Where. Then, the condition is:
>=DateAdd(&quot;m&quot;, -6, Date())

This query will return all customers that had transactions within the last 6 months.

Create a new query using the find Unmatched Query wizard, based on tblCustomers and this query. It will return all customers that had no transactions. Include all fields from Customers table and then turn it into an append query.

That should work...

Good luck
Daniel Vlas
Systems Consultant

 

[Only1Abhi]

many thanks daniel.
i'll give that a go!

 

[Only1Abhi]

Hi Daniel.
Sorry for the long time in replying.
Actually, I had many problems.
But I found a different way of tackling this problem.

Here is what I've done:

Step 1
I identified those customers that haven't made a transaction in the last three months by making a query with CustomerID (from tblCustomer) and the &quot;max&quot; function of the field Date (from tblTransaction). The criteria I entered into the Date field is &quot;<Now()-90&quot;

Step 2
I used this query to feed a query on each of my 3 tables to identify the records by CustomerID (i.e. linked this query's CustomerID field to the customerID field of tblCustomer, then made another 2 queries that are linked to CustomerID field of the query to the CustomerID fields of tblCustomerContact and tblTransaction)
Then I turned each of these queries into Append Queries, to their appropriate archive tables.

Step 3
Did the same thing but made them as delete queries on my three main tables.

So a total of seven queries.

Now, Will this work Daniel? DO u think?
And also, I have created a form with the button Archive Records. What should be the code (or macro) behind this button to carry out the process of archiving?

2 more questions bro.
How do I recover data from the Archive table if I need it?
I also want to delete records in the archive tables that are NOT taken out within six months. Many thanks.

Help really, really, appreciated!!!

Best Regards,
Abhi

 

[Only1Abhi]

Hi again Daniel. My little solution works well except that the delete queries don't WORK.

The error message I get when trying to run these queries are:

&quot;Specify the table containing the records you want to delete.&quot;

Where can I specify what table I want this to be deleted from?

Many thanks in advance!

Best Regards,
Abhi

 

[danvlas]

To make a Delete query, you need to drag the * from the table to the query grid, as that's how Access knows what table to delete from. All other fields are used as criteria.

Or...switch to SQL View and you'll need to have something like:

Delete * From [TableName] Where...

Good luck


Daniel Vlas
Systems Consultant

 

[Only1Abhi]

thanks for the reply Daniel.

Tried that. Didn't work.

Also tried changing the CustomerID field to be from tblCustomer instead of the query.

That also didn't help.

I am fedup with this archive problem. Would really appreciate it if any1 could come up with an answer.

Here's the new error message I get when I try to run the query:
'Could not delete from specified tables.'

I also looked up their help function for this, but it's no help!

Many thanks,
Regards,
Abhi

 

[danvlas]

Could you post the SQL of the query? You may have some joins that prevent you from deleting data.

A picture says more than one thousand words...



Daniel Vlas
Systems Consultant

 

[Only1Abhi]

Hi Daniel.
Here's the text in SQL View.

INSERT INTO tblArchiveCustomer ( CustomerID, Forename, Surname, Gender, HomeTelNo, WorkTelNo, MobileNo )
SELECT qryCurrentTransaction.CustomerID, tblCustomer.Forename, tblCustomer.Surname, tblCustomer.Gender, tblCustomer.HomeTelNo, tblCustomer.WorkTelNo, tblCustomer.MobileNo
FROM qryCurrentTransaction LEFT JOIN tblCustomer ON qryCurrentTransaction.CustomerID = tblCustomer.CustomerID;


Plz try to find an alternative than using SQL, I mean a normal design view solution. Many thanks for everything you have done and anything you can do.

Nice to see good ppl like you out there!
U're defintly the top programmer this month for me!!
Keep up the good work!

Best Regards,
Abhi!

 

[Only1Abhi]

sorry, I pasted the wrong query's SQL.

Here's the correct &quot;qryDeleteCustomer&quot; Delete Query:

DELETE tblCustomer.*, tblCustomer.CustomerID, tblCustomer.Forename, tblCustomer.Surname, tblCustomer.Gender, tblCustomer.HomeTelNo, tblCustomer.WorkTelNo, tblCustomer.MobileNo
FROM qryCurrentTransaction INNER JOIN tblCustomer ON qryCurrentTransaction.CustomerID = tblCustomer.CustomerID;

 

[danvlas]

Hmmm...you can't delete records from a query...but through a query from a table...

This should work:

Delete * From [tblCustomer] Where [CustomerID] Not In (Select [CustomerID] From [tblTransaction] Where [TransactionDate] > DateAdd(&quot;m&quot;, -6, Date()));

The idea is:
Get all customers that had transactions within the last 6 months - the In (select...) part
Delete all customers that are not in the list.

You could use the longer path-one intermediate query:
Create a query (qryCustomersWithOrders):
Select [CustomerID] From [tblTransaction] Where [TransactionDate] > DateAdd(&quot;m&quot;, -6, Date())

The delete query:
Delete * From tblCustomers Left Join qryCustomersWithOrders On tblCustomers.CustomerID = qryCustomersWithOrders.CustomerID Where qryCustomersWithOrders.CustomerID Is Null)


Thanx for appreciation...but there are many guys better than me here...


Good luck

Daniel Vlas
Systems Consultant

 

[Only1Abhi]

thanx Daniel.
That worked.
But because I've entered this code in SQL view, do I really need the Append query &quot;qryAppendtblCustomer&quot; that goes with this Delete query when we do it in normal design view (QBE)?

What about the query I created to identify records that were more than 6 months old?
(
That query is called &quot;qryCurrentTransactions&quot; and has the fields:

CustomerID (from tblCustomer)
DateofTransaction (from tblTransaction) - Criteria: <Date()-90
)

So do I need this query and the append query?
(I'm too scared to delete it).

Many thanks.
(I really do think u're a good programmer, whether or not there are better ones out there).

Best Regards,
Abhishek

 

[danvlas]

Actually, the append query copies the records from the live table to the archive table. Records remain intact in the live database until you run the delete query.

If you delete the append wuery, how are you going to archive customers?


Daniel Vlas
Systems Consultant