archiving by date

[miss031]

I have set up my database completely before I started looking into archiving.
I have many forms and reports, which I only want to display current records.
Do I need to append every table with a date field in order to filter all of
my queries to only show current and not archived records?

If so, how would I make the default value of the date field in all current
records being entered to be the date entered into a particular table.
Ex.: tbl.SaleDate:
NewSaleDate= 10/26/06

Before a new record is entered into tbl.SaleDate, the last date used must be
marked archive, so there would only be one curent record on this table at one
time.

 

[Jeff Boyce]

A couple approaches to "archiving in place" (this keeps a single table, with
no need to actually move any records to an "archive" table)...

First, if when the record was archived is irrelevant, a simple Yes/No field
can be used (?Archived = Yes). You would modify your queries underlying
your forms and reports to exclude the records where Archived=Yes.

A second way handles when it IS important to know when a record was
archived. In that instance, add a new field (DateArchived) of datatype
Date/Time. If there is no date/time (or the date/time is in the future
compared to today's date), the record is "active". If there is a date/time
value (today or earlier), the record is archived. Again, modify your
queries to exclude these rows.

You haven't described the underlying data structure, so it's a bit tough to
recommend WHERE you need to put these "archive" fields...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[miss031]

I don't know how I would go about adding a Yes/No to all of the records in 5
to 10 different tables, and check the boxes on all of those records every
week. We are dealing with probably 1,000 new records every week, whcih need
to be archived at the end of each week before any new records can be entered.
I prefer to use an archive date, and I would prefer it to be set up as I
mentioned before.

Please let me know what you would like to know about my underlying structure.

 

[Jeff Boyce]

I'm not there, so I have no idea what 5-10 different tables you mentioned,
nor what kind of data you might be storing in them.

It sounds like you are trying to do this directly in the tables, not via
forms. Access tables store data, Access forms display it for editing.

To add a checkbox to every record of a table, you'd change the table
definition.

Without a better idea of the domain (the topic) and the tables you already
have, I could only guess what you might need to do. More specific
descriptions will probably result in more specific suggestions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[miss031]

I don't know what else you could need to know. Yes I am aware that you use
tables for storage, and I would like to store my data properly.

I have a table for customers, onyl some of which will be current, and some
which will be archived.

I have a table of inventory, which, when sold, will be archived.

I have a table of how each of the customers paid for their purchases.

I have a table of the owners of the merchaindse, some of which will be
current, and some that will be archived.

I have a table of photos of the items for sale, which also will be archived
when they are sold.

Most importantly, I have a table of information about the current sale. In
this table, only one record will be current at a time, the date and location
of the sale, and this date is that which I want to use for archiving, so I
need it to be applied to all of the current records in the tables that I
mentioned above. Once the sale is over, I would like to archive it, and
start a new sale with a new date. Once a new sale is started, all records
entered will have the new sale date.

All that I need to know is: do I need to append all of these tables with the
new date field when a new sale is opened, or what?

I don't like the Yes/No idea, because I will eventually make a form that
allows the user to search archived records by date, becuase that is the most
used method of searching in our current (paper) system.

 

[Jeff Boyce]

See comments in-line below... (by the way, I am not trying to be difficult,
I am making sure I understand both WHAT and WHY you are doing this. I can
tell you how to drive nails with a chainsaw, but this would NOT be a good
idea...)

I don't know what else you could need to know. Yes I am aware that you use
tables for storage, and I would like to store my data properly.

I was pointing out that we can't see your tables and would be able to offer
more specific information if you provided more specific descriptions...

I have a table for customers, onyl some of which will be current, and some
which will be archived.

This implies that a "customer" can only be a customer one time (i.e., once
archived, no longer a customer or potential customer). Is this a match for
the real world situation you are dealing with?

I have a table of inventory, which, when sold, will be archived.

Why bother marking inventory as 'archived'? If your database can show that
it has been sold, you know that it is no longer available. No need to
(redundantly) store that information.

I have a table of how each of the customers paid for their purchases.

?Is this a list of payment methods that any customer might use, or a list of
customers and the payment method each customer used?

I have a table of the owners of the merchaindse, some of which will be
current, and some that will be archived.

?The owners or the merchandise -- which will be archived? And again, what
are you trying to achieve by "archiving" these? I ask, not to be difficult,
but to see if there might be an alternative that would accomplish what you
want (which I'm not clear on) without "archiving" all these pieces...

I have a table of photos of the items for sale, which also will be archived
when they are sold.

If you already know an item has been sold, there is no need to (redundantly)
mark the item's photo as archived.

Most importantly, I have a table of information about the current sale.

From your description, I'm not clear on whether "the current sale" is
specific to one item or to a lot, several items.

In this table, only one record will be current at a time, the date and location
of the sale, and this date is that which I want to use for archiving, so I
need it to be applied to all of the current records in the tables that I
mentioned above.

If you have a well-normalized relational database design, you will be able
to tell which "sale" is related to which item(s), customer(s), owner(s),
etc. The fact that a sale's date is in the past will be sufficient to let
you determine which (related) item(s), ... are, as you have described,
"archived".

Once the sale is over, I would like to archive it, and
start a new sale with a new date. Once a new sale is started, all records
entered will have the new sale date.

?"All records entered.."? In what table(s)? And again, a relational
database doesn't require redundant data entry of that sale date across
multiple tables.

All that I need to know is: do I need to append all of these tables with the
new date field when a new sale is opened, or what?

I realized you've stated "just tell me how to do what I'm trying to do". I
would be remiss if I did not understand the "why" of what you are trying to
do ... mainly because it may not even be necessary to do that! Spreading
(redundantly) a date/time field and the same date across multiple tables, as
you have described, in the long run will prove problematic.

I don't like the Yes/No idea, because I will eventually make a form that
allows the user to search archived records by date, becuase that is the most
used method of searching in our current (paper) system.

How you use Access' tables and data types to store information about your
domain/situation need not restrict how you use Access' forms and reports to
display that information. My offering of a Yes/No field focused on your
underlying need ... to know THAT something was archived, vs. knowing WHEN it
was.

Good luck

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[miss031]

I sincerely appreciate your help.

My problem is that I understand the coding, but the planning and
relationships is what i am having trouble with. I didn't do much initial
planning, i just started creating organically.

I realize that I am getting beyond the scope of message board questions, and
into the realm of consulting, so I think I am going to spend some time and
get some help with my planning before I do any more coding.

 

[Jeff Boyce]

I'll offer this observation in the way of closing...

In Access (and other relational databases), the data & relationships is
everything. If you don't have those set up well, you will be forever
"working around" the issues that pop up.

By all means, take the time to look into normalization before proceeding.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/