Working and Archive Table vs Archive Field

[tagteam]

What is the recommended way to handle data that needs monthly processing. Have a working table and an Archive table and once all the monthly work is done move it to the archive table or to just have a field that you mark a line item as archived.

This application will be an invoice application so it will have a header table and a detail table. Each month I will create the monthly invoices in the header table, then I will create all the detail records and then run an update query to put each headerID in the appropriate detail.headerID field.

Having a working table is nice because then I can see all the outstanding items, but you can also do that with a filter.



Does anyone have any recommendations based on real world examples of where one works better than the other?

Thanks,

[isladogs]

Have a working table and an Archive table and once all the monthly work is done move it to the archive table or to just have a field that you mark a line item as archived.

Definitely the latter...it will significantly reduce your workload .... though personally I would wait longer than a month before marking anything as archived.

Many years ago I decided to archive all records in one of my commercial apps for schools to a separate table at the end of each academic year.
Doing that meant I then needed to create additional queries & reports whenever I needed to view the archived data.
In addition, if any of the table designs were later modified with additional fields, I had to replicate that in the archived tables and modify the queries/code used to accommodate those changes.
That decision caused me many hundreds of hours of additional work.

Please don't make the same mistake that I did.

[Micron]

General accepted practice is to not move data around for 'archiving' purposes. Flag it as complete somehow - a date field is the best approach IMO. If date is null, it is a current record. If not, not only is it archived/complete/fini - you know when that happened. Your forms recordsources are then queries (which they should always be) which return only the applicable records. Multiple tables as you describe take up more overhead (file size) than a single table with all of the records. None of that even considers the possibility that a record might need to be un-archived, making this a pita.

This

run an update query to put each headerID in the appropriate detail.headerID field.

doesn't sound right. The subform detail records you create on a form should automatically link to the invoice header. Methinks you are doing something you shouldn't, like working directly in tables.

[tagteam]

Hey Micron, maybe I didnt explain that very well. I am actually using your advice from another thread. The form is based on a query (using the record source button and not a separate saved query.) I have an append query that takes all the customers creates a header record with the general invoice info for each customer. Then I have an append query that takes all the details for each of those customers and adds all the line items to a details table. Then once those details have been created I update each detail line for each customer with the InvoiceID for that customer from the header table. So yes, when the form opens it automatically links the header info with the correct detail info.

isladogs, I do have a database that I work with that someone else created and yes the two tables cause me some problems but I ususally use a union query to solve that and for the most part it is not too bad. I do kind of like having a working table that only has my monthly data because if a user got in there and messed up the records somehow all the history data would be fine.

However, I do tend to agree with you both that it is better to put some restrictions on forms etc and try to keep it all in one table. Micron, thanks for the advice about the date field. I probably would have put a check box to show it was archived and a date field to show when but I see what you mean in that they are not both needed.

Thanks, I will go with the one table and add a date field for the archive flag.

QUESTION - one question I do still have is if I do the archive with a date field should I also do one in the header table or just the detail table. Seems like I would need it in both if I wanted a report from just one of the tables of just archived things.

[Micron]

Re: your question:

Not if your form/report query filters out header records that have an archive date? In English

SELECT all header records and fields FROM header table WHERE ArchiveDate Is Not Null, equal join header records on detail records and show details too.

A set of header records that are thus filtered to those that are not archived would then only pull in detail records that are related to the un-archived header records. Try to not think about relating records to anything other than the fields that should relate them to one another - i.e. PK in header to FK in details.

I'll post just this for now and review the rest of your post soon. Time to turn on game 2 of the World Series!