Tables :: Appending Changed Records To Archive Table?
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 Replies
ADVERTISEMENT
Oct 16, 2012
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
View 2 Replies
View Related
Sep 11, 2005
Hi,
can someone please help me with this problem.
currently im working on a student management system with MS ACCESS. Im stuck on the create_assessment_form that allows you to query previous assessment results or to create a new assessment. It has a subform that shows the assessment results of all the students relating to a selected assessment_ID.
The query of the results are working properly,
But I need everyone's help for the creating new assessment part.....
When I create a new assessment, I have to choose a course_ID the assessment is relating to.
>>>Question<<<
How do I make it so that it will generate a list of assessment_results record for each of the student relating to the new assessment?
maybe my explaination is a bit messy. Please let me know if you do not understand my question. But I really need an answer to this question urgently!!!
Thanks in advance...
View 3 Replies
View Related
Sep 8, 2014
I'm making a database for tracking purchase orders for contract workers in my department. Business rules dictate that a PO is created for each unique Contractor-Project instance.
I pulled a report (.csv format) of weekly timesheets from the company's project management database system. Some of the fields include: EmpUserName, EmpID, ProjectID, ProjectName, WeekEndDate, Hours. I linked to the file in my database and ran a query on it without WeekEndDate so that I get the total hours each contractor has worked for each project they've billed to. I created a table called tblContractorProject and appended the records to it. I'm going to be pulling this report each week so I can see if any contractors start billing to any new projects so I can create a purchase order for it. Is there a way to write an append query that can recognize if the EmpID and ProjectID combination is new and doesn't already exist in the table?
View 1 Replies
View Related
Mar 17, 2015
I have a linked ODBC table in my database that contains data like this -
Code:
Task Serial CrewAmount
00_INSTALL ENGINE INTO PEDESTAL STAND 707308AS30.2
00_INSTALL ENGINE INTO PEDESTAL STAND 707308OMB0.2
01_BORESCOPE INSPECTION 706496AS114.24
01_BORESCOPE INSPECTION 706496AS223.24
[Code] .....
What I would like to do is remove the duplicate values for the Task field, but show how much time each Crew has spent on each Task per Serial. The end result would look like this -
Code:
Task SerialAS1AS2AS3OMAOMB
01_BORESCOPE INSPECTION 70649614.2423.2428.78
00_INSTALL ENGINE INTO PEDESTAL STAND 7073080.20.2
01_INSTALL OIL TANK 7073085.67
[Code] ....
What is the best way to achieve this result? I've played around with Append queries, union queries etc, but nothing seems to give me the result I'm after.
View 3 Replies
View Related
Feb 6, 2014
I have a table in MS Access 2010 that is a link to an external data file in .csv format. I assumed that it was not possible to append data to a linked table until recently. I appended 3 records to the linked table and discovered that the 3 records were appended to the table and the external data file it was linked to.
What am I missing ?
View 8 Replies
View Related
Dec 8, 2012
I have 2 tables as below
Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010
Table 2RID
StartM
EndM
[code]...
Now I need to create 3rd table based on above two tables
for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1
View 5 Replies
View Related
Jan 4, 2015
I Have made a change to a field in my tables. it was was based on ethnic background and originally i had just created the field but had not added in the options ( via adding it into the row sources).
So now the tables field have been updated but unfortunately on the form it has not updated into the dropdown i had created containing the options..
View 9 Replies
View Related
May 31, 2013
I am assisting my employer by combining two databases into one. Both databases have the same field "structure" but the data differs. When creating my append query.
View 3 Replies
View Related
Mar 17, 2013
I currently have an unnormalized table that consists of individuals (employee profiles) which currently has a field for attachments. I am using the "attachments" for photographs of each employee however I dont want to maintain photographs for former employees, or if I do not forever. I was told to normalize the table by removing the "attachment" field and placing it in a new table with referene to the employeeID.
Since I currently have something like 200+ records where I want to relocate the employee picture, i tried to create a make table query off the origional table and only referencing the employeeid and "attachment" to the new table however I keep getting an error when running the query. The error indicates something to the affect of having multiple values.
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.
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
Jul 5, 2005
hi
i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached
any ideas, as some will know i'm a newbie
View 1 Replies
View Related
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
Aug 2, 2005
I have been trying to figure out how to do this and the transferdatabase function wouldn't do what I needed. I have a table that has a bunch of records, each being a request for cad work somebody wants me to do. I change the status of each one to pending, working, or complete. Anyway I want to only keep the completed task records in the database for 90 days. After that the records will be exported to an archive database and purged from the working database. The problem I face is not getting the records I need “I used date stamps ;)” but getting the records I filtered to append the archive database table and not overwrite it. I made backups ;) of my db and am testing on those and cant figure this out. Below is the code I used to export. Like I said it overides the table rather than append...
DoCmd.TransferDatabase acExport, "Microsoft Access", _
"X:Archive.mdb", acTable, "tRequest", _
"tRequest"
MsgBox "its done"
Any ideas, pointers, stern words and a link to a post I missed, anything to help is appreciated. :o
Thanks,
Eric
View 1 Replies
View Related
May 11, 2006
I would appreciate any tips as to how I can archive records where the yes no field named "Resolved" is ticked. These records may need to be retrieved (as opposed to deleted). Any deletion would be made at some time later as part of a manual database maintenance process. Unfortuantely the database is 200 Kb larger than permitted to upload however, I can forward a zipped copy if required. Kind regards. Bernard
View 6 Replies
View Related
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 9 Replies
View Related
Jan 8, 2014
I have a Database in which I would like to archive data once it is now longer actively being used. I have VBA code that does everything i need it to, however I would like to name the new archive table related to the dates in the data being archived. Below is the code I am using to archive and delete the information from the main table.
Code:
Dim strSQLCreate As String
Dim strSQLDelete As String
Dim strArchiveTableName As String
[Code].....
I know it will be in the "strArchiveTableName" variable, I am just not sure how to pull the dates out of the data being moved. The TestData table holds a date and time for each test, and the earliest and latest dates is what i want to append to the table name.
For example: Currently "tblTestData_A102Archive"What I want "tblTestData_A102_1/1/2012-1/1/2013"
View 3 Replies
View Related
Jul 31, 2006
I have created a form in Access updating the files each month but I need to keep the previous month data as historical record . how can I creat and archiving function / command so that the files that have been updated are not lost and will be kept for future use? thank you for your help
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
Aug 24, 2006
Hi,
I'm trying to populate a read-only field on a form with the time and date the record was last changed.
This is to acheive seeing a list of records that have been updated this week for example.
I have this at the moment:-
Private Sub Form_AfterUpdate()
Last_Updated.Value = Now()
End Sub
When I've updated a record it won't save and when I try to move onto the next record it does nothing, the button clicks but no response.
Does anybody have any idea where I'm going wrong or have any suggestions?
Would appreciate any help.
Thanks,
Dan
View 3 Replies
View Related
Dec 10, 2012
I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were created in Excel and imported in Access.
I would like to do the following (see table below): 1. create a form that would allow me to view all the data per school in datasheet view and also allow me to enter new data annually; 2. create a new table where this information can be stored
.
YEAREMIS NOSCHOOLNO WROTENO PASSEDPASS %2009109964Bhuqwini4012302010109964Bhuqwini6126432011109964Bhuqwini6422342012109964Bhuqwini
View 4 Replies
View Related
Dec 10, 2012
I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were imported from Excel. I would like to do the following: create a form that would allow me to view the all the data per school in datasheet view and also allow me to enter new data annually.
View 2 Replies
View Related
Dec 6, 2005
I have a 2 databases exactly the same I need to append the records from one of them to the other. The only tables that need appending are "Transactions" & "Details"...that's it.
Transaction Tbl:
TransactionID (PK) AutoNumer
TransDate...
Detail Tbl:
DetailID (FK to TransID)
DetailDate...
The problem is how do I combine them because TransactionID is an Autonum?
If I append one Transaction table to the other one (without the autonum of course), a NEW autonum is assigned then I can't link the DetailID's when I append the detail tables together.
What to do?
I only have 44 total transactions to worry about so it's mostly curiousity.
View 4 Replies
View Related
Nov 1, 2005
I have three tables, table 1 'ServerTest' contains 5 records which should not change, table 2 'Region' has 40+ records.
using an append quary all records are combined into table 3 'Junction1'
my problem...
the Region table will change over time and new records will be added.
how do I append the new records only to the junction1 table?
this is my append quary;
INSERT INTO Junction1 ( RegionID, TestID, TestNumber, TestDescription )
SELECT Region.RegionID, ServerTest.TestID, ServerTest.TestNumber, ServerTest.TestDescription
FROM Region, ServerTest;
thanks :)
Murlen
View 4 Replies
View Related
Dec 17, 2006
Hi Guys
this is a query I'm running, which I think is quite simple....why does it not bring anything across?
INSERT INTO tblRunItems ( SiteId, RunId )
SELECT tblsites.siteId, forms.frmRuns.runID
FROM tblSites INNER JOIN (tblRuns INNER JOIN tblRunItems ON tblRuns.runID=tblRunItems.RunId) ON tblSites.SiteId=tblRunItems.SiteId
WHERE ((tblSites.CollectionDay)=forms.frmRuns.runDay) And ((tblSites.CollectionZone)=forms.frmRuns.runZone);
Could someone please help me on this....
View 2 Replies
View Related
