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.
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
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:
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?
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".
I am trying to create a warehouse database in acees 2007 and 2010 that can track goods that come in.i have two types of goods Specials which is not on my current stock list and Current stock of which i have a excel sheet of around 32000+ items.I have 2 warehouse to put the stock in with over 100 locations.I am trying to track the stock that comes in by saying it is Special or Current stock if Special and i enter the data it should add it to my Stock sheet if current I can choose it from the Current Stock list; allocate it to a location .
Lastly i need to be able to move the stock either from one location to another in the warehouses or to a customer on a orderI have created a few tables and tried to link it as best i can after reading 100's of posts and access for dummies
tblProduct ProductPK-Autonumber ProductCode - Text Description - Text Dept - Text
tblLocation LocationPK - AutoNumber Location - Text
lnktblPtoductLocation LinkPK -Autonumber ProductFK - Number(long integer) LocationFK - Number(Long Integer) Qty - Number PoNumber - Text DateIn - DateTime
i set this up as a many to many relation and that is as far as i got.when i try and enter a location for a product to test it say i cant update location field and things like i cant enter data on the one side of the many to many relation.
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
I have a table where in 286 different fields have to be updated by the users. ( Nature of project And i have 10 fields which are coming as a input from another table, so i use Append and Update query to add the new datas and update the datas in case of any changes in the input from another table. ( to avoid manual corrections)
Now coming to my real problem, all the 286 fields have to be updated by the user's are check box type, now what is the solution since access will not accept more than 255 fields?
Firstly, I want to admit that my knowledge about data base construction is fairly basic. I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country. In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields. What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations. The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project? I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables. Many thanks in advance. Niels
I have been working on a a table design, but I am failing to create the correct relationships.My main table is the tblIncident with the three remaining tables being linked via a one to many relationship. I am using an auto number as my primary key, then linking that number to a FK in each table.
The end goal is to create one form with a varying sample of combo boxes, check boxes and data entry fields from each table. I did not plan to create a subform for each table, but maybe that is required?
I am starting out creating a training database to track training needs and expiring training etc.I'm just looking for some tips as to how to structure the tables and relationships..I have an employee table, department table and training type table.However im wondering how i can set up requirments and then match these to check if the person is trained up to date on all required skills?
I need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.
I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.I uploaded an empty database with the structure.
Cascade levels
I. Facility Type II. Item by [I] III. Component by [II]
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...?
Is it possible please to extract a tables structure and data into SQL in Access. I know how to do this in phpmyadmin (an internet based DBMS) but not so sure in Access.
What I want to do is to create an identical version of the table in SQL Server, so therefore require the structure and data in SQL format if possible.
So I'm trying to manipulate Access to create a Directory for my church. I'm trying to get a report to show the church staff, which I was able to do, but I was wondering, is it possible to get the records to show side by side instead of one on top of the other?
I included a picture of the design view showing what I would like to see. Excuse the way the numbers are written, it's hard to write with a mouse.
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?
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.
I need to modify my customer table to cater for multiple names at one address.
Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.
Looking for efficient table structure for this. Let's say I have a list of contractors who will get paid a certain amount of money each week. The amount of money changes each week by measuring the amount of work done, giving it a price and calculating it. The fields are these:
[pk]WeeklyWorkID WorkID Quantity UnitPrice Total=Qty*UnitPrice (not stored in this table) EmployeeID WeekID
So far, it's all good, but we wish to pay the contractors in an unusual way. Let's say he makes $16,000 of work this week, and we want to pay him using cash AND/OR either one or multiple debit cards. For example:
We can only deposit $7,500 max in each card. But it's even worse... two or more contractors can SHARE the card and it's not always the same card. Sounds pretty funky, but it's what the company wants to do. So, what would you recommend me in this case?
I'm guessing I'm gonna need these tables: tbWeeks to hold the week number, tbCards to store the cards' info, and an extra table to make a many to many relationship between the cards and the employees.
I have split my database, the data is in a DB on the server and the forms, reports, etc is on the client desktop. My question is "Is there an advantage to having all of my combo box queries (Lookups) on the server side (defined in the table as a combo lookup) or should I put the all on the form so that they reside in client side DB.
Form with three sections. I have three queries selecting different set of set in a table, I would like to show all three in a form side by side. How can I do this? I use form wizard bit it only uses one query as a source.
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.
hi everybody, great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.
client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.
i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.
i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.
my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?
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.
I want to Delete only FK on the many side first and the record on the one side by one click of a button. I wrote some code which sometimes works and sometimes it does not!!
I wonder if any one have a better idea or doing this please?
Private Sub Delete_Click()
Dim db As DAO.Database, rs As DAO.Recordset Dim n As Integer, i As Integer Dim vStart As Integer Dim vEnd As Integer Dim vSite As Integer Dim vRCCID As Integer
vSite = Forms![frmSite].Form![SiteID] vRCCID = Forms![frmSite]![Roads Construction Consent].Form![RCCID] vStart = Me.PhaseStart - 1 vEnd = Me.PhaseEnd + 1
Set db = CurrentDb Set rs = db.OpenRecordset("tblPhase") rs.MoveLast n = rs.RecordCount rs.MoveFirst If n > 0 Then For i = 1 To n If rs![SiteID] = vSite Then If rs![PhaseNumber] > vStart And rs![PhaseNumber] < vEnd Then rs.Edit rs![RCCID] = Null rs.Update
End If End If rs.MoveNext Next i End If rs.Close db.Close Set db = Nothing Set rs = Nothing
'/////////////////////////////////////////////
DoCmd.RunSQL "DELETE RCCID FROM tblRCC WHERE RCCID = " & vRCCID & ""
'////////////////////////////////////////////// End Sub
tblRCC is the one side of the relationship and tblPhase is the many side.
I have been wondering about how I'm going to keep track of revisions for one of my databases.
Currently, I have the database split into a Back End (BE) and Front End (FE). In the work environment, the BE sits on the server, whilst all other 4 machines get a local copy of the FE. All table data in stored in the BE and linked to the FE.
I have recently got my act together and started documenting all the revisions I make to the BE and FE of the database. To do so, I created two tables with the following fields:
RevID (PK Auto increment), RevDate RevNumber RevisionNotes TimeDateStamp
I called them tblRevisions_FE, tblRevisions_BE. I placed both tables in the BE.
Whenever I make changes to BE / FE, I record it in the appropriate table.
However, because I often work on the FE of the database off site, I don't have access to the BE table (tblRevisions_FE) to record the new changes for the FE.
One alternative I thought of was just recording the revisions in a text document, and asking the client to manually record these in the tblRevisions_FE (via a FE form).
I did have tblRevisions_FE located in the FE, but when each db user got a copy of the FE, there would also be 4 copies of the table floating around.
Does anyone have any suggestions about how to manage documentation of FE revisions in this situation?
