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 Replies


ADVERTISEMENT

Track Changes Of Raw Table Data Information?

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

Help W/table Structure - Keep Historical Data - "Revisions" On Many-side Tables

Jun 1, 2005

Hope the thread title wasn't too confusing.

I have a database that tracks emissions from painting. Bear with me since this is going to be a long post.
:o

Some background info.
- a paint can consists of many parts mixed in a specific ratio.
- a part cosists of many chemicals
- a part may be used is many different paints

Here is how I have the existing database structured now. I’ve simplified it somewhat.

tblPaint
PaintID (PK)
PaintName - String
PaintDensity - Double
PaintVOCContent - Double

tblPart
PartID (PK)
PartName - String
PartDensity - Double
PartVOCContent - Double

tblRatio
RatioID (PK)
PaintID (FK)
PartID (FK)
Ratio - Integer

tblChemicalWt
ChemicalWtID (PK)
PartID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)

tblChemical
ChemicalID (PK)
strChemicalNumber - Long
strChemicalName - String

tblUsage
UsageID (PK)
PaintID (FK)
UsageDate - Date
UsageAmount - Double

PK = Primary Key (Autonumber)
FK = Foreign Key (Autonumber)

The Density or VOC Content (VOC = Volatile Organic Compound) for a paint can either be given OR it can be calculated by the mix ratio of parts and their respective Density or VOC Content values. One or the other must be complete.

What I did not account for was that there may be changes due to the paint manufacturer revising their paint composition, such as;
the parts that make up a paint may change
chemical make-up of a part changes (can be a change in Weight Percentages or the addition or deletion of a chemical).
ratio in which parts are mixed for a paint changes
Density/VOC Content values may change for a Paint or Part

The problem is that I cannot simply change the existing records as the emissions are calculated using all the data from each table and emissions need to be calculated using the paint/part/ratio/chemical weight percent info that was valid at the time of usage.

Another thing is that the Paint Name will not change, it’ll always be something like “BrandX Acrylic Blue”.

The person entering usage data only knows how much of what paint was used for a given day.

The person who enters paint usage has nothing to with entering the chemical make-up for parts and information for the paints and vice versa.

At any rate, my new draft table design is as follows. Two of the tables (tblChemical & tblUsage) will remain the same.

tblPaint
PaintID (PK)
PaintName - String

tblPaintVersion
PaintVersionID (PK)
PaintID (FK)
PaintDensity - Double
PaintVOCContent - Double
PaintVersionDateIN - Date
PaintVersionDateOUT - Date

tblPart
PartID (PK)
PartName - String

tblPartVersion
PartVersionID (PK)
PartID (FK)
PartDensity - Double
PartVOCContent - Double
PartVersionDateIN - Date
PartVersionDateOUT - Date

tblChemicalWt
ChemicalWtID (PK)
PartVersionID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)

I might be able to do away with tblRatioVersion and just have one table to store the mix ratios. It should be the case that a change in mix ratios (either a change in mix ratios and/or what parts make up a paint) means a change in the Paint Density & VOC Content. But I am presenting both versions of the Ratio tables here for completeness.

Version 1
tblRatioVersion
RatioVersionID (PK)
PaintVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date

tblRatio
RatioID (PK)
RatioVersionID (FK)
PartVersionID (FK)
Ratio - Integer

Version 2
tblRatio
RatioID (PK)
PaintVersionID (FK)
PartVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
Ratio - Integer

I plan on having the DateOUT fields be populated automatically to match the DateIN for the new version. That way I can use “BETWEEN DateIN and DateOUT” to select the appropriate info for calculating emissions. The idea came from an old thread I started (http://www.access-programmers.co.uk/forums/showthread.php?t=31677&highlight=historical+data). I think this is the way to go, but with all the relationships going on, I'm having a hard time wrapping my head around it all. Am hoping someone here can help me with this.

Anyone see any problems with the new table design?
Anyone know a better way?
:confused:

Some potential issues that I see
If only the Density/VOC Content changes for a Paint, then the old set of records in tblRatio must be duplicated.
If only the Density/VOC Content changes for a Part, then the old set of records in tblRatio & tblChemicalWt must be duplicated.

Thanks for reading this post all the way to the end!
:D

EDIT: Thought about it some more.
A new version of a Part, should trigger a new version of Mix Ratios which in turn should trigger a new version of a paint.
Part --> Ratio --> Paint
Ratio --> Paint

Also, a change in a Part must trigger a New Paint version for ALL Paints that currently use it!
:eek:

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

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

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

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

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

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

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

Data Protection Vs Historical Data Help!!!

Jan 2, 2007

I need help

I created a tblcustomer/ tbljobs database for a charitable handyman service to record customer's details their multiple jobs and handyman. This worked fine until new reporting system was requested. and data protection issues were raised.

In order to differentiate between current active customers and old inactive customers in the database I used to flag in/active customers and this was okay.

Now I have been asked to remove personal identifying information from old customer records but still allow the customer id,sex,age,joindate,local authority and job types,dates,handyman,timetaken etc. to be analysed for regular reports.

I am wondering about using a history table updated by query that would keep all non identifiable active and inactive customer/job records used for reports seperate from the customer table used by the receptionist to book jobs and find customer info.

I could then use the history table to create reports on service use etc.

Can anybody tell me how to set this up. I have tried several ways but run into trouble when a deceased client is deleted from the active customer table I cannot get the history table to hold on to the info.

Paul the handyman

View 2 Replies View Related

General :: Track Changes In A Field With The Date?

Jul 7, 2014

I want a field in my table (called 'ContactTotal') that tracks the total number of times the date was changed in the field 'LastContacted' - through either table or a form, but it should exclude any changes made on the same day.

I'd also like a second field (called 'Popularity') that does the same thing, but multiplies any changes made in the last 3 months by 3, the last 3-6 months by 2, the last 12 months by 1 and anything before that by 0.

View 14 Replies View Related

Track/log Changes To Live Data

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

Tables :: Keeping Track Of Data?

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

Modules & VBA :: Keep Track Of When Form Data Has Changed

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

General :: Data Model To Specify And Track Proficiency / Currency

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

Queries :: Track Monthly Data For Several Thousand Products

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

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

Reports :: Track Data Changes - Highlight Modified Values

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

Report Which Keeps Track Of Data Changes Made In DB Form Fields

Dec 13, 2014

How to generate either a form or report able to show me the history of data changes made by users in the fields of Access Objects. I'm more interested in Forms, since the users will use Forms to change data in the db.

Let me give an example:

My db has, for instance, a form named Frm1 and a form named Frm2. Both were built including several fields such as text boxes, combo boxes, etc.

So, if the user Paul first changes the field Color of the record Id 235 in the Frm1 from Green to Yellow, and then, down the road George changes the field Size of the record Id 14 in the Frm2 from Big to Small, I need to know what changes were made, by who, and when changes happened. Also, it is important to know if a record Id is added or excluded.

My final intent is to have a report/form that shows some sort of user activity log which I think could be like this:

Date--------Time----------User-----Form------RecId------Action------Field-----From-----------To
Dec12,14---15:12:35-----Paul-----Frm1--------235------Changed---Color----Green-----------Yellow
Dec18,14---07:05:19-----George--Frm2---------14------Changed---Size-----Big--------------Small
Dec22,14---09:23:59-----Sean----Frm2--------116------Deleted
Dec23,14---11:07:03-----Paul-----Frm2--------321------Added
etc...

By sorting this output by date, form and field it will easy to check/audit for user processing mistakes.

I know enough to build tables, queries, forms and reports, but don't know how to structure such thing. What kind of form event should I setup?

View 2 Replies View Related

Normalize My Table - Track Price Levels

Aug 22, 2005

i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.

i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.

i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.

i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.

All Help will be appreciated.

I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.

thanks,

sam

View 7 Replies View Related

General :: Keep Track Of All Permits Per Year Per User In Same Table

Dec 3, 2012

We have a db to register permits that count for one calendar year. This since 2012. For next year, permits must be renewed. How can we keep track of all permits per year per user in the same table ? We would like to produce annual statistics as well ? Easy solution would have been to copy the existing table for the year 2012 and paste as a new table for year 2013 but we don't know yet who will renew his permit ...

View 3 Replies View Related

Subtract Number Range While Keeping Track In A Table / Form

Mar 31, 2014

I have two tables (one is a query)

Table 1 (query based)

EMP_ID
Prev_Emp_ID
EMP_ID_DDSK

332-123
1
500

332-133
1
501

332-144
0

332-156
1
502

332-654
1
503

332-456
1
504

332-967
0

Table 2

Res_Numbers
Num_Of_Employees

500
10

505 - after 5 numbers are placed
Will be added for next hire

My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)

Example: If field "Prev_Emp_ID" = false

Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".

Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".

View 4 Replies View Related

Making Form Open With Data From A Table Based On Date And Time In Table?

Sep 12, 2014

How to make a form open with data from a table based on a date and time in the/a table?

View 7 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

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







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