Solved: Purging records

[Tinbendr]

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.

[BrianMH]

I would say import the report into Access and then use a query to do it. That would be the most efficient.

[mohanvijay]

Try "DELETE" statement

[code]

DELETE FROM testtable WHERE month=x and hour=y

[code]

[Tinbendr]

OK, I'm finally back to this.

I decided to try a Delete query. It seems the simpliest and I can run it once a year.

But I can't get it to return any records.

What am I doing wrong?

[BrianMH]

change the second field to SOAP_table and the column to tso and then in the criteria type < [OHTime_table].[Time Last O/H]

When there are spaces in a table name you need to close them in as above.

[Tinbendr]

Now getting Data Mismatch error.

[Tinbendr]

No wait. Changed to ONLY "< [OHTime_table].[Time Last O/H]", but no records presented.

Hmm...

[BrianMH]

If the whole "< [OHTime_table].[Time Last O/H]" is in quotes it will evaluate as a string. Are both fields date fields and are both serial number fields the exact same data type?

Go to sql view and post the text displayed there.

[Tinbendr]

TSO and Time Last O/h are BOTH number fields E.g. 1234.5 (Hours of run time)

Serial numbers are both text (Alphanumeric.) ABC123456

SELECT SOAP_table.*
FROM OHTime_table INNER JOIN SOAP_table ON OHTime_table.[Serial number] = SOAP_table.[SerialNum]
WHERE (((SOAP_table.TSO)<[OHTime_table].[Time Last O/H]));

And No, I did not include the quotes in the Criteria.

[Tinbendr]

I've been looking at this TOO long.

You were right, the Serial numbers DON'T match. (One doesn't have the letter Prefix.

I'll have to edit the OHTime Excel sheet and import it again.

I'll let you know later.

[BrianMH]

As far as I can see that should work.

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.

[Tinbendr]

Quote:   Originally Posted by: BrianMH Sorry if I'm rambling on about stuff you already know.

Oh, Please continue.

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!)

Thanks for your help!

[BrianMH]

Your welcome. If the thread is solved please mark it as so.

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.

[Tinbendr]

Quote:   Originally Posted by: BrianMH 10MB was sluggish?

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.

Thanks again!

[BrianMH]

Check out autoforms. It helps at least get a basic design.