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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
Mar 2, 2007
Advanced apologies for the long question.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
Can anyone figure out what I'm doing wrong?
Many thanks in advance
View 3 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Jan 26, 2006
Hi,
I have a field in one table that needs the sum from fields in another table.
How do I go about doing that?
Thanks,
Trena
View 1 Replies
View Related
Sep 23, 2007
Dear Access Expert
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
View 6 Replies
View Related
Apr 25, 2014
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
View 3 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
May 31, 2005
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
Thank You
View 1 Replies
View Related
Mar 14, 2006
Hey guys I have a problem on creating the append-query and the new table
I want to join couple different fields from 2 COMPLETELY different table with TOTALLY DIFFERENT fields.
>>What I have right now are the following:
Table 1
Fields: meta_theme, theme, sub_theme, root, count
Table 2
Fields: meta_AG, AG, AG_description
>>What I want at the end:
A new Table 3 that contains the following information AND is a form, which when you open will have :
1.meta_theme: automatically populated with the records from table 1
2.theme: automatically populated with the records from table 2
3.AG: This column is to be filled when someone run the form, and it will be a combo box with value from the records in Table 2-AG field
4. Comments - completely new
>>could you guys help? My brain is totally in disfunction mode... :(
View 1 Replies
View Related
Jul 31, 2007
I have come across a snag in my access project I am currently undertaking and need some advise on what should be done next. . .
In a nutshell, this project's primary purpose is to perform a search on a table of information based on many fields and generate a report based on the records which match the search. I have the search form working (similar to this one here) http://allenbrowne.com/ser-62.html
It displays the results from the table based on the search criteria. The form is great for viewing the results which match search criteria; however, I am having trouble doing anything else with the data (perform operations on the data, send the results to the table, etc. . .). How should I go about moving this data to a table?:confused:
View 3 Replies
View Related
