Tables :: Keep Track Of Checking In And Out - Where Archive Data Stored
Oct 3, 2014
In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?
Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?
View Replies
ADVERTISEMENT
Sep 2, 2004
How can I remove (delete) a page from a table (the entire row that has that specific data) and have it go into an archive? I don't want to completely delete the information, but I don't want to have it in my current table and have to have all kinds of filters and other criteria to view in my queries and reports.
View 1 Replies
View Related
Apr 3, 2007
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
View 4 Replies
View Related
Apr 3, 2007
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
Edit/Delete Message Reply With Quote Quick reply to this message
JJKramer
View Public Profile
Send a private message to JJKramer
Find all posts by JJKramer
Add JJKramer to Your Buddy List
Sponsored Links
View 1 Replies
View Related
Apr 24, 2013
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
View 7 Replies
View Related
Nov 18, 2014
I have a database with numerous tables. I have set up relationships between all tables (they are all pretty one-to-many relationships). I then created forms in which to enter and view data. Data entry is fine; however, when I go into my tables, I realize that it is not storing the ID# for related tables in the main table.
For example - I have a table called "ContractTypes" with a field for ID#. That table is linked to the main table called "Contracts. In the form, I have the ContractType as a lookup field, I select the one I want for this entry, but it is not storing that ContractTypeID into the Contracts table. Also, when I go back into the form to enter a new record, the data on the subforms is from the previous record and not the record I'm currently working on. In other words, it does not appear to be linking the ID#s from the different tables.
View 5 Replies
View Related
Oct 12, 2005
I have created an Access 2000 Db, and have gone through the motions of splitting the Db via the Splitting Tool. Since I have done this split, I have added forms, queries, and reports to the Front End. I have left the Back end table structures alone.
This is where my problem comes into play. To enter data into the Db, I use the forms. After splitting the Db, I assumed that the data would be stored in the tables in the BE. An issue had just presented itself and I went into the BE table structure to add a field to a couple of the tables, and when I opened the BE table in the datasheet view, I noticed that there were no records. I then became curious because when I run my reports and queries, the information is present. So I opened the FE tables and what do you know, all the data I have input since the split is housed in the FE tables.
I already attempted to Update the Table Links, but that did not solve the problem, and the data is still in the FE.
Can someone, anyone please help or offer some suggestions on how to resolve this dilemna.
Thank you in advance,
DtF
View 3 Replies
View Related
Aug 21, 2014
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
View 1 Replies
View Related
Sep 26, 2014
I need to archive some data in a table with a checkbox on a form. I have tenants table & property tables. Sometimes tenants move out & new move in but i don't want to delete information from old tenant. But i can't have duplicate property id's assigned to different tenants.
So I made a checkbox on the tenants form that sets the tenant to active or inactive with a status column in the tenant table. how do i delete only the property ID so i don't have duplicates when the new tenant moves in?
View 1 Replies
View Related
Oct 30, 2014
i'm in the process of creating an Access database to import data in text files and then export the data as fixed width text files, this is now working fine
My next step is to be able to setup an "Archive" Table which will hold all the data i have imported across multiple import proceudres for the last 30 days, i then want to be able to "De-Dupe" any files i import against this to ensure i never load duplicate data.
View 1 Replies
View Related
May 13, 2013
All; using Access 2010. I need to have some kind of way to track changes based on different field name.I have a client table with field data like Fname, Lname, SSN, Address, Zip, Phone, DateOfBirth, Current Rate, TotalRate, Status and Importdte. I have an import into the database every month and I update this table with data that is new based on the SSN with an append query. The import also has the names of those that exist in the client table until their status changes. In addition to appending new data every month; I would like to append any changes the existing client has. For ex; If an existing client has an address change, I want to append this data to the table but keep the existing address and have a Change date equal to the Importdte. In essence; I want to build a kind of history table that when I pull up a client; I can have something like a tab on a form with the changes and when it happened.
View 6 Replies
View Related
Sep 10, 2012
I'm trying to design a billing and invoicing database and can't seem to figure out how to link the tables and track the payments made by customers. How can I proceed to track the payments and make payments applicable to certain line items or invoices? If the line item hasn't been paid, it needs to show up on the next invoice and be included in the total due.
View 14 Replies
View Related
Sep 19, 2013
I am new in Access 2010 and need table design schema. I am creating a database to track budget changes within our organizations.
1. We have 3 division (West, East and Central).
2. Each division will have 5 Organizations (Office, Campus, Operations, IT and HR)
3. Each Organizations will have multiple sub-organizations
4. The budget start with the sub-organizations - which is break down by cost center and sub cost center.
This is the design I came up.
tblFiscalYear
tblDivision
tblOrganizations
tblSubOrganizations
tblCostCenter
tblsubcostcenter (budget table)
tblDivision --1 to Many --> tblOrganizations
tblOrganizations --1 to Many --> tblsuborganizations
tblsuborganizations --1 to many --> tblCostCenter
tblCostCenter --1 to many --> tblsubcostcenter
Is there a better way to design this?
View 4 Replies
View Related
Mar 11, 2008
I have created a database that I am happy with, and I am about to receive information to populate it with. I know I am about to be asked to about security and logging of changes made to the data, which I know nothing about!!
As a result before I get into this part of my project I would like to know myself what is and what is not possible!!
Currently I have 2 tables and 3 forms, one of which is a sub form........ All changes will be done via the forms, adding new records, editing existing records or even deleting obsolete records.
•Is it possible to record any changes made to the live data?
oFor this to happen I presume that I need to setup users and access rights?
•What type of information can be saved if any?
oWhat form would this be saved in and where (a temp table?)?
While typing this a change of plan – if it is possible I am going to start building it regardless, as it will enhance my database.
Thanks
View 5 Replies
View Related
Apr 9, 2013
We have an application which is used in a couple of offices and on a number of terminals. The data is stored in a back-end database on the server on each site.The databases are getting to the stage that we cannot roll out changes to the back end table layouts relatively efficiently. What I want to do through vba if possible is basically have a blank local version of each table and say:
check tab_1 on linked_backend, if tab_1 layout <> tab_1_local layout then update the back end layout to mirror that of the local layout.
We cannot overwrite the data, but on occasion, we need to add an extra field to a central table as new bits of the application are developed.I've tried a few searches here, and am probably using the wrong phrases, but not getting any results.
View 4 Replies
View Related
Dec 19, 2012
Im working in MS Access 2003.Im creating an audit trail for users actions in a form, to monitor and keep track of what fields were updated/entered/removed from the database, these actions are essentially done from command buttons (and the audit trail, is coded on the button click)
However, I have a few search screens, that return a subform listing results. These datasheets returned are effectivly like opening the raw table and hence are editable, (permission to change is granted as they are admin only searches, yes, they can change the raw table)
How can I track the change to a raw table field, ie, if someone updates the field SURNAME from 'Smth' to 'Smith', is there something that triggers this (obviously if it was in an interface textbox, you could audit this) is their a field change. The only way i can suggest it make a recordset of the intial results, then compare that to the results in the table as they leve and compare the two.
View 7 Replies
View Related
Feb 12, 2008
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
View 2 Replies
View Related
Feb 12, 2008
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
View 1 Replies
View Related
Feb 21, 2014
I am trying to make a database that will keep track of invoice backlogs for different locations within a company.
Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:
Code:
Location Backlog
00001 84
00002 53
00003 26
Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file.
View 6 Replies
View Related
Dec 19, 2013
I am attempting to develop a DB to track hardware assets, e.g., servers, switches, storage devices, etc... I am at the very beginning of the development/design process. I haven't gone beyond the paper stage yet. I am trying to figure out how I would set up the main Asset table so that when I put in a particular vendor, the model field will list models only for that specific vendor. i.e., when I put in IBM in the Vendor field I will only see IBM models in a drop down for the Vendor_Model field.
I'm pretty sure this will involve a Lookup field. But, how do I restrict the lookup of models. Would that be with a query?
View 2 Replies
View Related
Dec 7, 2012
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
View 1 Replies
View Related
Dec 26, 2007
I am re-designing a database for 2008 and trying to eliminate my Make Table Queries as I have found them to be somewhat consistant over the last year, particularily when the users do not open the database on a given date. It seems there should be a simple way to accomplish what I want but I am struggling and need some assistance.
I have attached a sample of a few tables from my database, Open Cases, Closed Cases, and Date Today. The Open and Closed tables change daily due to a Corporate download and contain several date fields which have different meanings. As new cases are opened, they go on the open table, and as an open case is closed, it moves to the closed table. The tbl_Date Today is pre-populated with dates of working days only. I have a query called "Count Of Shelf Comb" that counts the number of open cases as of today, which in truth is for all activity through the previous business day. What I want is to have a query that will show each date on the tbl_date today as well has what the total count of open cases was for that date......a permanent history of the amounts.
How can I accomplish this without using a "Make Table Query".
View 2 Replies
View Related
Aug 16, 2014
I want to keep track of when data in a form was last changed.I only need to track this when the form is closed. not every time a record is changed.
View 5 Replies
View Related
Apr 13, 2014
I am looking for a data model to define and track proficiency/currency.
Example:
You earn a licence to do X. You need to renew your licence every 2 years or so. But also, to maintain currency, you need to do what the licence gives you the right to do often enough, or else you need to do a test.
Specifically, you need to do X a specified number of times N within, say, last 3 months at any one time to maintain currency. If you have lost your currency, you need to do a test/check. So at all times, you need to have N times on record within last 3 months, or a test.
In addition, to do X legally, you also need to document that you have done X at least 10 hours within, say, last 6 months, or that you have another test within those last 6 months. This is independet of the previous requirement (concerning the number of times you did X).
Both requirements must be fulfilled at all times for you to do X legally.
So, any suitable model out there? Essentially, something capable of defining and tracking currency for various ratings and license privileges of pilots would do just fine.
View 2 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
Jan 22, 2015
I'm building a report which should highlight/change text color in field values in a modified table which have changed relative to field values in an original table. I'm using the same conditional formatting on all my fields: e.g. on the field named [Primary Instructor] in the report on the modified table, I have "Value <> Reports![F2015original]![Primary Instructor]", where I reference an identical report on the original table. Both are loaded.
My problem: it's reading all values as changed/different, even when both visual inspection and copy/paste indicate that the values are identical. Thus my report highlights all values for the field, rather than only those which have changed. The reports have identical non-conditional formatting and data type.
View 3 Replies
View Related
