How do I eliminate old data in a table?

[Jimcb8]

The first table is the CLIENT table:
ClientID
Lastname
Firstname

There is only one record per client in the CLIENT table.

The second table is VISITS table:
ClientID
DateVisited (mm/dd/yyyy format)

There are many records in the VISITS table, one for each time a client visit.

I want to eliminate a CLIENT record, if he has not visited in the last six months, for example.

How do I do this query? I am not sure how to get the last visit record for a Client and test the date visited.
Obviously, I am new to access and self taught. Any help would be greatly appreciated.

 

[Beetle]

First, MAKE A BACKUP, and test this on the backup copy in case you don't get the results you expect.

A query like this should do the trick;


DELETE *
FROM tblClients
WHERE NOT EXISTS
(SELECT ClientID, VisitDate FROM tblVisits
WHERE tblVisits.ClientID = tblClients.ClientID AND VisitDate > Date()-180);

You can open the query designer, switch to SQL view and just copy/paste the above if you want, you'll just need to make sure the table and field names are correct. Also, make sure you have Cascade Delete Related Records set for your relationship so the related records in tblVisits will be deleted as well.

 

[Jimcb8]

Thank you, thank you, so much for your help.
Am I reading this query correctly?
DELETE if there is no record at all in VISITS?is that what NOT EXISTS means?

or

This is what I'm not clear on:
If VISITS has for example 3 records that match Clients and today is 09/01/2012

The records in VISITS for this client has the following dates:

01/01/2012
08/20/2012
08/22/2012
We see that one of the records meets the criteria and the other 2 records do not , therefore the Client record is not selected.
To say it another way;
How does access know which is the last record entered in the VISITS table for this client?
Your explanation is appreciated so I understand more about access.

Thank you again for your patience with me.

Jim

PS I changed the DELETE to a SELECT so it would just list the records that met the criteria

 

[Beetle]

The example I posted is a query with a sub query. Here it is again in color to highlight the distinct parts of the query;

DELETE *
FROM tblClients
WHERE NOT EXISTS
(SELECT ClientID, VisitDate FROM tblVisits
WHERE tblVisits.ClientID = tblClients.ClientID AND VisitDate > Date()-180);

The part in red is the sub query. The sub query selects all records (ClientID and VisitDate) from tblVisits where the VisitDate is greater than today's date (returned by the Date() function) minus 180 days (6 months). For the purposes of this sub query, it doesn't matter if the Client also has other records in tblVisits that are older than that. The only thing that matters is whether they have a VisitDate within the last 6 months. If they do then their ClientID will be returned by the sub query. Essentially, this just gives us a list of ClientID's that can be used for comparison.

Now on to the main query (the DELETE portion). What it's going to do is delete any client record where the ClientID does NOT EXIST in the sub query. If their ClientID is not in the sub query, then they have not visited in the last 6 months. Deleting the record in the parent table (tblClients) will automatically delete the related records in the child table (tblVisits) as long as, as I said before, you have Cascade Delete enforced on your relationship.

You can find more on sub queries here.

 

[Galaxiom]

Many developers don't delete data but simply add a Yes/No field to indicate the client is active or not. Then they use this field in the criteria of queries as appropriate. If this field is indexed it is almost as good as those records not being in the table.

New developers often worry about the size of a table when it gets to several thousand records and assume it will slow down the performance. However Access can easily handle a million records if the fields are indexed appropriately. (I had one Access table with close to ten million records before I moved it to SQL Server where it now has over thirty million records.)

The capacity of a well designed database to retrieve records still amazes me.

 

[Jimcb8]

Thank you all for your help. I get it now