If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Solved: Purging records

I keep records for about two hundred pieces of equipment. They are tracked by hours. Reports are created every 20 hours. I have created a database that stores the report results using Excel as a frontend.

Once the equipment goes in for overhaul, the previous results are no longer needed. I need to purge these records when they are less than the last overhaul time.

I get an Excel report once a month that has all the last overhaul times.

What would you recommend as the best logic to deal with these records?

I ‘m thinking about looping through the entire database, once a month, using the overhaul times report to delete all the obsolete data.

Just to clarify by type I don't necessarily mean just what is in a field but its specific type in the table definition. 123456 is a number but it can be stored in a text field or a memo field or one of many number subtypes. In access those have to match exactly to be able to link them. Otherwise you get type mismatch. Same with dates. They can be stored as dates or as strings that look like dates. In the case of ISO format they can also be stored as a number 20120521 for instance.

Sorry if I'm rambling on about stuff you already know. I just wanted to make sure you did, it would help you debug.

-----------------------------------------The more you learn about something the more you know you have much to learn.

I'm quite fluent in Word/Excel VBA, but Access seems to escape me. Probably because I don't TAKE the time to learn it. And that's a shame really, because I have several Excel databases that really should be moved to Access. Alas, one more thing on my Todo List.

After changing the serial number and re-importing, I was met with SUCCESS!!!!!!!

Went from 10 MB, down to 3MB. It had really become sluggish. (Yes, I did work on a backup!)

Excel is great at what it is designed for, I love it. Excel is not designed to be a database though. You will save so much time and frustration by switching to Access to use as a database. The short time it takes you to learn it will be more than made free by not trying to use a tool that isn't designed as a database as a database. I should know. I have had to design spreadsheets that work like a database too many times because a manager refuses to have access installed on a few machines. It is just a huge headache.

10MB was sluggish? That is surprising. I have databases of 600MB+ that work fine. Try adding some indexes to your tables to speed up queries. Are you running it over a network? Are you running a front end with a separate back end? There are many ways to increase efficiency.

-----------------------------------------The more you learn about something the more you know you have much to learn.

Only on the Excel end as I was testing every record for < TSO. Once I purged the records, then removed the test, it was very fast. < 10 secs for everything. New Sheet, Sheet name change, added header, footer, edit margins, and then all the records.

I was toying with the idea of moving the whole thing to Access, but I get frustrated in the Form editor because I don't know my way around yet.

This success did make me get out my Access book and read a couple of chapters! There might be hope for me yet.