Saving Historical Copies Of DB
Sep 14, 2006
Hiya,
I realise this could well go against almost every DB rule in the book, but figured I would ask it anyway!
I have a database, which pulls all it's data from other databases - some in SQL, some in Oracle, and some from other Access DBs.
It then combines it all, performs dozens of queries on it, and allows me to produce necessary reports on it - all fine.
I have been asked to make it save historical copies of all the data it uses. The reason for this is the Financial Services Authority, who insist that the checks we are doing on this data is all stored, so that if an auditor arrives tomorrow, and asks me to prove the data from 3 months ago was processed correctly, I have to be able to come up with that 3 month old data.
I thought the easiest thing to do would be to use a series of make-table queries to move all the tables data to an external database, which can then be archived.
Does anyone have a way of allowing me to save the entire database, as at NOW - to another database?
I would need to make all the tables LOCAL, rather than linked?
Thanks! (and sorry for the unnecessarily long post!)
View Replies
ADVERTISEMENT
Mar 6, 2007
Could someone point me in the right direction on how to statically store current pricing for a product in an invoice database, whereby future price changes would not change pricing on past/previously created invoices...?
View 6 Replies
View Related
Oct 14, 2005
I am a basically a beginner with access so please bear with me.
I have set up a database that measures productivity results for a call center. I am measuring the data by person, manager and queue. I have everything worked out except this one problem.
I have assigned individuals to a specific manager and a specific queue.
Periodically, individuals will move from one manager to another or from one queue to another. I need to know how to set up a table and queury that will allow me to indicate specific dates an individual worked for a specific manager or specific queue.
The table is currently:
Agent
Manager
Queue
Any help would be greatly appreciated.
View 4 Replies
View Related
Nov 2, 2014
I have the following tables
1. t_Employee. It consists of the following fields:
EmployeeID
Name
Job Title
Contract Start Date
Contract End Date
2. t_Login. It has the ff fields:
UserID
UserName
Password
3. t_AuditTrail w/ the ff fields (this will used for historical data for Job title, Contract Start Date, Contract End Date, etc.):
AuditTrailID
TableID (in this case t_Employee)
FieldName (JobTitle)
RecordID (EmployeeID)
OldValue
NewValue
ChangeDate (date edited)
ChangeBy (UserName)
I've already set up t_AuditTrail by putting several (& separate) After Update Data Macros.
Now, I have a form for t_Employee. It has a button that would open a report. This report contains the Job Title history of an employee.
The report is based on a query w/ the ff SQL:
Code:
SELECT t_AuditTrail.atTableID, t_AuditTrail.atFieldName, t_AuditTrail.atRecordID, t_AuditTrail.atOldValue, t_AuditTrail.atNewValue
FROM t_AuditTrail
WHERE (((t_AuditTrail.atTableID)="t_Employee") AND ((t_AuditTrail.atFieldName)="eJobTitleID"));
So the report only shows historical data for Job Title. Which means that Job Title from t_AuditTrail is not related to Contract Start Date or Contract End Date.
Problem(s)/Question(s):I want my report to show the Job Title History and the corresponding contract start date and contract end date (not the date a record was edited). When an employee changes a job title, his/her contract dates change.However, when i start to make a report based on quesries q_AuditTrail_JobTitle and q_AuditTrail_ContractStartDate and q_AuditTrail_ContractEndDate, Access tells me that they are not connected so it cannot make a report. How do I go about this? How do I let user see the Job Title relative to its contract start and end dates?
View 1 Replies
View Related
Oct 1, 2012
I have a table of Dealers. Each dealer has a REP. I want to CHANGE the rep of the Dealer going forward but RETAIN the historical.
View 4 Replies
View Related
Feb 26, 2014
I have a database with student information that contains tables about their dissertation and graduation information. There is a field "academic year" noting their graduation year. I have a form for data entry that my data entry person likes to use in datasheet view. The form is based on a query that contains only current academic year records. When a new academic year arrives, I plan to create a new query for the form to feed from. i.e., "hiding" past academic year records on the form in datasheet view.
View 1 Replies
View Related
Nov 12, 2014
I have 2 tables.
Table A contains a list of Projects that evolve over time. Example:
Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.1 12/22/13
2 Name 2 Comment 2.1 12/20/13
3 Name 3 Comment 3.1 12/02/13
Now, let's say that Table A changes over time - just with the Comment portion. Example:
Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.2 01/20/14
2 Name 2 Comment 2.2 02/14/14
3 Name 3 Comment 3.2 01/02/14
Obviously, I would use an Update query to override the previous information.
But let's say that I want to preserve the previous information for historical use? How would I set this up?
View 2 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
Jun 28, 2012
Selecting the "General" group as this involves SQL Server Stored Procedures (SP) and VBA code and Reports and and and...
Client has requested exception type reporting noting when a price in a Bill of Materials (BOM) changes.
I am thinking to solve this with the following steps:
1) EXEC SP to run "this week's" BOM reports, automated, figure out how to print to PDF or something
2) EXEC SP to run "this week vs last week" exception report. A giant nasty:
Code:
SELECT cols....
FROM [xyz]
LEFT JOIN [histxyz] ON [xyz].[partnumber] = [xyzhist].[partnumber]
WHERE [xyz].[cola] <> [histxyz].[cola]
OR [xyz].[colb] <> [histxyz].[colb]
OR etc...
through each of the fieleds that are hooked up to change tracking. Run that SP once, then use that temp table to generate customized reports based on parts per product which had a change.
3) Update weekly state snapshot of all parts remembering this week's state... transfer data from [xyz] to [xyzhist], so TRUNCATE then INSERT commands.
Seems slow and monotonous, the snapshotting "shell game" aspect... perhaps I may wrap that all into a transfer SP and allow the data to stay right on the server as it moves tables.
View 2 Replies
View Related
Mar 11, 2013
I'm thinking of 2 different ways, but not sure how Access will handle them.
1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).
This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).
2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)
I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?
View 14 Replies
View Related
Nov 12, 2013
How to set up a trimester query instead of a quarter? DO I need to do it in VBA or can I do it as a criteria?
I am trying to query historical data into previous year trimesters. Jan-Apr, May-Aug, and Sept-Dec.
View 2 Replies
View Related
Oct 15, 2007
I have a form in Access with a command button that prints a receipt (which is actually created as a report). Anyway, I want 2 copies of this receipt to print when the print receipt button is clicked. Please take a look at my code and tell me what I would need to be able to accomplish this. Thanks.
rivate Sub PrintRec_Click()
On Error GoTo Err_PrintRec_Click
Dim rstTrans As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim curCount As Currency
rstTrans.Open "dbo_tbl_Transactions", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If IsNull(Me.TempTransNumID.value) Then
'this is new record
rstTrans.AddNew
Else
'to stay on the record that was just inserted for editing
rstTrans.Find ("TransNumID=" + Str$(Me.TempTransNumID))
End If
rstTrans!TransDate = Me.TransDate
rstTrans!CustomerName = Me.CustomerName
rstTrans!VehType = Me.VehType
rstTrans!TktType = Me.TktType
rstTrans!Auth_By = Me.AuthBy
rstTrans!Quantity = Me.Quantity
rstTrans!SHtkt1 = Me.SHtkt1
rstTrans!SHtkt2 = Me.SHtkt2
rstTrans!HRtkt1 = Me.HRtkt1
rstTrans!HRtkt2 = Me.HRtkt2
rstTrans!TransPayAmt = Me.TransPayAmt
rstTrans!PaymentType = Me.txtPaymentType
rstTrans!PaymentMethod = Me.cboPaymentMethod
rstTrans!CheckNum = Me.CheckNum
rstTrans!TransReceiptMemo = Me.TransReceiptMemo
rstTrans!TransEntryTime = Now()
rstTrans!TransEntryUserID = appUser
If Me.cboPaymentMethod = "Check" And IsNull(CheckNum) Then 'Check number not entered
MsgBox "You must enter a check no.", vbCritical, "Check Number Verification"
CheckNum.SetFocus
Exit Sub
End If
rstTrans.Update
'this was a new record so update the form value of TransNumID for edit
If IsNull(rstTrans!TransNumID.value) <> True Then
Me.TempTransNumID = rstTrans!TransNumID.value
End If
whereClause = "NewQryShuttleHandiRideReceipt.TransNumID" & " = " & rstTrans!TransNumID
//////here is where I'm printing the receipt
DoCmd.OpenReport "RptShuttle HandiRide Receipt", acViewNormal, , whereClause
rstTrans.Close
Set rstTrans = Nothing
Me.cmdAddRec.Enabled = True
Exit_PrintRec_Click:
MsgBox "Record Successfully Saved! Printing Receipt."
Exit Sub
Err_PrintRec_Click:
MsgBox Err.Description
Resume Exit_PrintRec_Click
End Sub
View 2 Replies
View Related
Jul 17, 2006
Hi,
I finished a db for a client to help her keep track of the classes she teaches, her students and other related info. The db has several tables and one main form with subforms. I read that it was a good rule of thumb to design the fewest posible forms to make the navigation centrilized and so I did. (I also spent some time on making it look less Access like, more of a stand alone app and wish to preserve it)
Up to this point the client was using Outlook to store all the contact info plus anything else she could jam into various "notes" fields. Now she wishes to have some simillar futures in the db to the ones outlook offered, one of them:
being able to open multiple contacts (records) in new windows. So basically she wants to open many instances of the main form so she can jump between the records without closing the previous one. My question is: what is the nicest (cleanest from the point of db design) way of giving her such functionality? I thought about giving her an option to open the new record (student) in a tab, but I'm not sure how to acomplish that. Another way would be to copy the main form several times and open those as she clicks on "open in new window", but I don't think it's a good solution, becsue: e.g. How many copies do I create? I'd have to go over all the vba in each copy and adjust it so it works properly with the copy, plus all the vba in each subform... :eek:
Any thoughts would be greatly appreciated on how to tackle this. Also what are the consequences of having several instances of the same form open (editing same record by mistake, etc..)
Thank you very much,
Mariusz
View 4 Replies
View Related
Oct 21, 2015
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
View 1 Replies
View Related
Jul 8, 2007
I want to print 3 copies of a report named Invoice with different remark i.e. Customer Copy, Office Copy, Auditor Copy. I want to print all 3 copies with a single print command.
Can any one help me.
Sample database is in attachmant.
View 7 Replies
View Related
Nov 28, 2007
I tried the search and while I found some things related to my issue, I couldn't really come to a conclusion on my issue.
I have all the databases on the network set to compact on close. This is resulting in a new copy of the compacted database with the generic "db1.mdb" file name every time the db is compacted. It also does not compact the correct one.
So, basically...it is copying the db, compacting it, but not deleting the old one and renaming the new one.
If I copy the database to my hard drive, it compacts, deletes, and renames sucessfully.
The only thing I can think of at this point is there's some issue with the server. But this is happening on multiple servers.
View 14 Replies
View Related
Sep 19, 2006
I grabbed a mdb template for customers/workorders from MS. I am trying to modify the "workorder labor" form in design view to have the first field be a manually filled in date, followed by start time, then finish time.
I got the boxes to show up.
The problem is that when I enter a date all the fields in that column are filled with the same information.
I tried variations of using a mask and format. I tried it as a text box from scratch and copying an existing one.
Any suggestions would be greatly appreciated.
Nett
View 1 Replies
View Related
Jun 5, 2014
I have a simple database at the moment which I would like to put onto two laptops. The laptops would be used for entering information when out on a job (no network or email connection).
What I would want to do is have a database on a desktop in the office so when the laptops come back into the office they can update the office database with the information on the laptop database.
View 2 Replies
View Related
Mar 15, 2007
If I want to distribute copies of a database and have one copy as the master and syncronise data; can i use briefcase or is there a better way?:o
View 1 Replies
View Related
Jul 11, 2007
I have to make a new copy of a db starting with a blank db and importing all the objects into the blank. (Corruptions issues).
When a blank db is made, and the objects imported into it, the security permissions do not copy over. The only way I know how to get the security permissions back is to redo them in the new copy. It's a big db. Lots of time. Ugh.
Is there any way to copy and paste the security permissions from one db to another?
Thanks
View 1 Replies
View Related
Mar 21, 2008
Ruralguy suggested that i made a new thread - so I'm taking his advice (H)
I've got another problem, well not problem..i want to make it more user-friendly.
Database= 21238 (Look for yourself see what i mean)
(rename to .RAR if it doesn't open)
Basically, when the user adds a book, they can add it easily..
the only problem is, when ..just say i want to add a book, and I have 10 copies of this book, I have to add the book first and the go into the ADD_BOOK_COPY form and then select the book which i want to add more copies of and click add - i repeat this for the total amount of copies that i have.
So if i had 10 copies of 1 book, i add, and then go into a seperate form and then click this button 9 more extra times.
Which, can cause headahces!
So basically, i'm thinking of implementing a new way to do this,
i was thinking about..
When i got to add a book, there's a List/Combo box with 1-20 into
So i can add the book, and then select how many copies, click 'add' and this will add 20 copies of that bookm firstly by adding it to the 'book' table and then adding 20 copies to the book_copy table..
Is there anyone who would like to help with this? :' )
Cheers btw! :)
View 7 Replies
View Related
Jun 18, 2015
I have an Access based CRM system that was built for me in 1998. Amazingly it is still pretty effective. However, I would like to make a small adjustment in the programming..
Once we've added the details for an order we press continue and the screen closes and one copy of the acknowledgement of order form. I simply want it to print three copies!
I believe this is the coding part of the command that is effected.
Rem Print Report
DoCmd.OpenReport "Order Acknowledgement"
Rem Close Form
DoCmd.Close acForm, "Booking Entry"
View 2 Replies
View Related
Sep 24, 2013
Our access database keep track of children attending an after-school music programme.
Each week we print registers and give them to the class teachers for them to mark who is coming. The registers are produced as a report, grouped by School then by Class.
We run 3 times a week so each week I need to print off 3 copies of the registers for each class.
Is there a way to print multiple copies of each group in a report? This would save me quite a bit of time each week.
I'm using Access 2013...
View 5 Replies
View Related
Jan 7, 2015
In Access 2007 is it possible to alter a macro so I can print 2 copies of a report. I have created a simple macro which opens up a report based on a value in a data entry form. I want to automatically print 2 copies of the report. Is this possible....
View 1 Replies
View Related
Nov 1, 2011
I am trying to print 5 copies of the report via the button on the form.
I'm sure I have the code correct, however I only prints 1 copy instead of 5.
View 1 Replies
View Related
Mar 20, 2013
I am working with an already intact database along with a form. One of the buttions in the form, when pressed, prints two copies of a P.O. One copy says original on the bottom and the other says PX Copy and my boss wants the PX Copy to stop printing. How do I get it to stop?
View 3 Replies
View Related
