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 RepliesI 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?
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 RelatedI 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'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?
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.
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
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:
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!)
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.
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 RelatedI 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?
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.
I have a task that will require me to subtract the current time from the previous time as long as the records fall within the same Extract Count. For example:
I have 8 transactions associated with ExtractCount #2 and 8 start/end & processing times. I need to subtract each row's time from the previous row's time. I have a conceptual idea of how to do it but need real help to figure out how to do this using a query.
Thanks,
Phoenix81
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?
What's up everyone, I'm new around here and to db design as well...
I'm posting a really rough data model for the first real database I've been putting together.
I'm still in the early stages, and the concepts behind db design are still pretty foreign to me, so there are bound to be many errors I hope to iron out ASAP.
One specific question:
On all of the data models I have referenced, the "OrderDetails" table has no primary key, can someone help me to understand this? Or explain how it is wrong... Thanks
I was hoping some of the experts and advanced users around here could take a look at this and help steer me in the right direction.
Thanks!!!!
I'm a bit new at all this but am trying to produce a query that filters on dates within the current week. Is there any way of doing this? I now how to do it for the day but at the moment I'm having to manually input 2 dates to get data in between them. Is there a command that will automatically detect the week and pull up dates within that?
View 8 Replies View RelatedI'm trying to make a button on a form that pulls data from a table in a database on a network share to the current open database (with the form).
Database on network share info:DB location: est.com est estdb.accdb
Table in DB: Table1
The current open database has the exact same table:Table2.In the following VBA line I get Runtime Error 3126: Invalid using of bracketing in name '' (The quotes at the end are 2x ')
Code:
DoCmd.RunSQL "INSERT INTO Table2 SELECT [ est.com est estdb.accdb].Table1.* FROM [ est.com est estdb.accdb].Table1"
I tried everything with changing the [, " and even the path name of the external DB as variant of type string. Nothing seems to work.
I have a Form> [Management]
I have a SubForm> [Management_History]
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
I have a query that shows data from January to March (which is the current month). is there a possibility to exclude the current month
The query has the following fields
SegmentDate
PCC
AgencyName
SegmentData
I have a report that shows all visit dates and the rep that did the visit. How do I only show the most current data. I use an append query to add records to a specific table. The report pulls from that table.
View 1 Replies View RelatedHey all,
I am working on converting someones Paradox Database to Access and making some modifications.
I have a table with people in it and they are linked to a number of interests. But i am struggling to create a query so that on each page of the form for the person i can call the interests and display them on the form as a list...
If anyone could point me in the right direction that woul dbe appreciated!
Give me mysql and a bit of php anyday!! hehe
I am using Access 2007 but i have used access before so i should be able to work my way through instructions for 2003.
thanks
RF
I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?
View 14 Replies View RelatedI want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
View 4 Replies View RelatedI have a report that I am trying to show data for the current year, but with a record count.
So, for example: I have 3-OVI, 3-Conduct Unbecoming, and 3-Did not transport for the current year.
When I run my report, it will show the above data for the current year, but in this format;
OVI-1
OVI-1
OVI-1
Conduct Unbecoming-1
and so on.
What I would like is;
OVI-3
Conduct Unbecoming-3
Did not transport-3
Total-9
If I take out the date code for the current year in my query, then I get the desired results but I get all data.
If I leave the date code in, then I get the data for the current year but I get the first example above.
I am extremely new to Access. I have my database up and running ok(ish) and would like to know if in my data entry form, I can have the "Date Entered in Database" box display the current date that does not change from day to day. I know you can use the "Now()" function but won't this just change everyday?
View 3 Replies View Related