Tables :: Lock Historical Entries?
Oct 1, 2012I 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 Replies
ADVERTISEMENT
Tables :: Most Efficient Way To Store Historical Data
Mar 11, 2013I'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?
Creating Two Tables - Old Entries And Weekly New Entries?
Mar 13, 2014I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID
Name
1
Adam
2
Ben
This week I have three new entries New entries
ID
Name
3
Charles
4
Richard
So when I run the same query next week I will get something like this.
Old Entries
ID
Name
1
Adam
2
Ben
3
Charles
4
Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
Help W/table Structure - Keep Historical Data - "Revisions" On Many-side Tables
Jun 1, 2005Hope 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:
Tables :: Lock View Of Tables And Queries?
Sep 24, 2013I want to lock the view of certain tables and queries so that users could not perform any change at their own. Is there any way in VB to protect it with password....
View 1 Replies View RelatedHow To Lock Tables
Oct 13, 2004hi,
i have created a table with 2 records.i want to lock the record such that nothing can be done with the existing records but allows to add new records.wat should i do??
thank u
How To Lock Tables
Aug 30, 2007hello to all
i want to lock the tables and queries of my database in access so the users cant modify any information
Tables :: Any Way To Lock A Single Record From Editing?
Dec 19, 2013I use the first record of my table as a blank when the form is opened. Users are supposed to use a drop down to navigate to a record for updating. Some users have populated fields into my blank that i've had to go in and delete.
I'm wondering if there is a way to prevent that first record from being edited.
Lock And Un Lock A Text Field Through A CMD Button
Jun 13, 2005I need to lock and unlock a a few text fields with a CMd Button.
and i do know about the allowedits thing, but i only need to apply this to a few fields on my form.
can some one throw me a clue?
Tables :: Lock Record From Erroneous Data Entry Or Editing?
Apr 12, 2013Is it possible to have a 2 step confirmation process.
I have a yes/no control box to confirm that the record is OK or correct.
Is it possible to have a second process where by I can lock the record from erroneous data entry or editing? Like a "Post" in accounting.
Tables :: Lock Single Record In A Table To Be Read Only On Permanent Basis
Dec 29, 2014I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
View 5 Replies View RelatedFind Matching Entries In Other Tables?
Dec 10, 2007Im using Access 2000.
Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.
So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)
I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?
My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.
Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.
I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.
Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:
Tables :: Create One To Many Table Entries?
May 8, 2013I'm new to MS Access, indeed database design as a whole.
I have been tasked with creating an issues logging database and am having some issues...
I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.
Tables :: Multiple Entries In One Field?
Feb 19, 2013Let's say if I am building a database for a library and in a table the book titles are the primary key and there is a field "authors" to record the authors' names.
But sometimes a book may have several writers and if I type all of them into one field, Access only recognise as one person. So how can I format/set up so that when I use query or filter, each one of names can be identified?
If multiple entries are not feasible, how else should I design my tables?
Tables :: Missing NumberID 8 In Table With 22 Entries
Dec 30, 2012I have a table with 22 entries and some how numberID 8 is missing . How can I get the numbers back in order without redoing the whole thing?
View 2 Replies View RelatedTables :: Total Of Entries In Date Field
Jan 29, 2014I have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.
Is it the case that date records cannot be totaled, in which case, why not all?
Tables :: Posting Multi-entries From Drop Down
Nov 1, 2012I have a field in which I wish to post several contacts. The contacts are listed in a table and populate the specific field via a drop down. In this specific field I want to select "mary" "bob" and "fred" (all three to display) however only one is displayed.....
View 3 Replies View RelatedQueries :: Show Entries From 2 Tables Not In Third Table
Jun 22, 2013I have 3 tables;
tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process
tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID
When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!
So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.
I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).
Saving Historical Copies Of DB
Sep 14, 2006Hiya,
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!)
Historical Vs Current Data
Mar 6, 2007Could 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 RelatedHistorical Table And Query
Oct 14, 2005I 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.
Tables :: Database With Multiple Entries For A Single Date?
May 17, 2013We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.
Here's an example of what I mean above:
Date Location Type Qty
5/16 1 A 10
5/16 1 B 1
5/17 1 A 1,000
5/17 1 B 100
5/17 1 C 1
5/17 1 D 11
I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.
Tables :: Linked Table - Adding Entries To Each Row Of Data
Jul 2, 2014I have a table that is linked to a survey and pulls data from the limesurvey backend.
The user would like to add a local field, so they can add entries to each row of data.
Is that possible ?
Reports :: Connecting Historical Data
Nov 2, 2014I 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?
Dealing With Historical Data On A Form?
Feb 26, 2014I 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 RelatedParticular Field Value Changes Every Month - How To Keep Historical Information
Nov 12, 2014I 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?