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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
archiving by date
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. |
#2
|
|||
|
|||
archiving by date
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "miss031" wrote in message ... 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. |
#3
|
|||
|
|||
archiving by date
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" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "miss031" wrote in message ... 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. |
#4
|
|||
|
|||
archiving by date
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "miss031" wrote in message ... 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. |
#5
|
|||
|
|||
archiving by date
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" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "miss031" wrote in message ... 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. |
#6
|
|||
|
|||
archiving by date
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...) "miss031" wrote in message ... 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ |
#7
|
|||
|
|||
archiving by date
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. |
#8
|
|||
|
|||
archiving by date
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "miss031" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|