Particular Field Value Changes Every Month - How To Keep Historical Information

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 Replies


ADVERTISEMENT

How To Link A Field To Another Depending On Information Filled In One Field

May 13, 2013

I have a couple of fields in Access. To explain I will show here the fields I have and their datatype

ID: Autonummer

Amendment: Text
Basis info: Yes/No
Sex: Yes/No
Period: Yes/No
Salary:Yes/No
Shift:Yes/No

Now, in the amendment field, I've inserted a combo box, with 3 options in it. Let's say option A,B and C...The thing I want access to do, is, when option C is selected, I want fields Period, Salary and Shift to be automatically "Yes".But if option A or B is selected, the the user must choose what the other fields are going to be either Yes or No

View 8 Replies View Related

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 3 Replies View Related

Historical Vs Current Data

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

Historical Table And Query

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

Reports :: Connecting Historical Data

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

Tables :: Lock Historical Entries?

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

Dealing With Historical Data On A Form?

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

Using Date Table To Track Historical Data

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

General :: Ability To Report Historical Changes To Database

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

Tables :: Most Efficient Way To Store Historical Data

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

Queries :: How To Set Up Trimester Query Instead Of Quarter For Historical Data

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

Calculation (Formula) On Report Will Change Every Year - Preserve Historical Date

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

Multiple Field Unique Information Db?

Aug 14, 2006

Hi, I'm crating this db:

Two tables, in one there are Items and services, in the other purchases.

1st table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text

2ndt table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text
Client - Text
Puchase Date - Date

How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.

barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.

Please Attach your DB if you have time to look into this issue.

View 6 Replies View Related

Updating Information Into A Field Automativcally ?

Jul 30, 2006

Hi Folks

I’m a newbie here, so as usual it’s always followed by a plea for help.

I’m From Scotland (live in Engerland)

I used to dabble with access in 95 and I can see it’s changed quite a bit.

I need a logging system for keeping details of faults that we have passed to various departments/

When we send an email I want the guys in my team to update the database with what ever they have sent. (This willbe a cut and paste action)

I want the database to have each entry with the following format


=============================
Updated by Jimmy at 19:33 on 27/7/06

I email John the following information:-

BLAH BLAH BLAH
BLAH BLAH BLAH


=============================

So everything in red above , is what will be pasted by the NEW Update buton, It will put the username , date and time of the update in between some other charaters (============) so that when we print the record as a report we can easily see all the updates as they will be between the dotted lines etc

So they are just update the same field all the time, appending data to the top of the record.

So maybe when the database starts it will ask the user his name, so that if makes any updates, it will use this name that he entered in. The date and time ect are the date and time, at the time of the new entry.

I made an example database and you can see a screenshot below:-

http://putfile.com/pic.php?pic=main/7/21006484313.gif&s=f10

My problem is, that I unsure how to complete this task?

Can anyone help me with this?

I’m no good at programming, so it needs to be simple for this simple user :D

Many thanks for any assistance or pointers you can give me

Ive also attached a sample databse.

Many thanks folks

Jimmy

View 4 Replies View Related

Blank Field No Information Is Displayed

Sep 11, 2006

I know this is a simple question. I searched but could not find what I was looking for. Or it is handled in differently than what I am thinking.
I have a field where I enter a dollar amount. But if I do not enter a dollar amount the field displays $0.00. How can I make this field blank if no dollar amount is entered.

Thanks
For any help I can get

View 2 Replies View Related

Queries :: Getting Information From One Field To Two Fields

Dec 20, 2014

I have a table that contains Investigator1,Investigator2 and other fields

And another table that contains PID, Rank_Name_Family and other fields

I am making a query in that query i want to get the names of Investigator1 and Investigator2

Investigator1 and Investigator2 is the PID

for example

Investigator1 = 1
Investigator2 = 2

PID = 1 Rank_Name_Family= Roy Jalbout
PID = 2 Rank_Name_Family= Rony Jalbout

i tried this two solutions but the result is error

Solution 1

Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator2])

Solution 2

Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator2])

View 3 Replies View Related

Default Information For Existing Field

Feb 6, 2007

We have created a program with Access and we have all our records in it. We created a field and we need to find a way to have all our existing records use default information to automatically populate that field.

View 1 Replies View Related

General :: Automatically Add Information After Field Exceeds?

Sep 20, 2012

I have a field "Permit_Date_Expired" field automatically is created by adding one year to the "Permit_Date_Issued" field.

when the "Permit_Date_Expired" field exceeds TODAYS() date I want it to automatically change the field "Permit_Holder" to say Expired is this possible?

View 5 Replies View Related

Queries :: Getting A Total For Specific Information In A Field

Jul 29, 2013

I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild.

View 6 Replies View Related

Tables :: Linking Field To A Folder Of Information

Dec 18, 2013

I have an historical database table that contains information relating to soldiers. Additionally I have many photographs, and other documents appertaining to these individual soldiers stored in folders on my PC hard drive and I would like ideally, to be able to have a link from my database to these other items, so that when I open a a soldiers record I am able to click on a link that will take me to a folder containing the photographs, letters and further documents relating to him.

However this is where I am at a loss as to how to proceed, and how I could have this facility, I run Access 2007 by the way.

View 4 Replies View Related

Modules & VBA :: Information Message For Duplicate Field

Jun 8, 2015

How can I create an alert message to tell the user that there are an existing work order number in the table? I don't want it to restrict the entry, just inform the user that there is an existing work order number, do they want to continue.

Btw, the code will be attached to a combo box (cbowonum).

View 8 Replies View Related

Forms :: Pictures Show After Information In A Field

Jun 29, 2014

My idea is to have a field in my table that have ratings for example - 1,2,3,4,5,6,7,8,9,10. How can i make one star.jpg image to appear when I've entered rating 1, two stars to appear when I've entered rating 2, 3 stars for rating 3 and etc.... Is that even possible? If it isn't is there any other way i can make this?

View 14 Replies View Related

Month And Date Only Field

Jun 29, 2005

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

View 5 Replies View Related

Month And Date Only Field

Jun 29, 2005

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

View 1 Replies View Related

General :: Timestamp Information When Certain Field Changed By User

Feb 19, 2013

I have a form that i want to some how in a text box post a time stamp anytime information in a certain field is changed by another user.

The time stamp shows on the table only when the table is saved or imported.

Is there a vba code i can attach to a text box that will show me anytime an entry is made?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved