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 Replies
ADVERTISEMENT
Oct 1, 2007
Our physical year starts 10/01. This is what I have for the criteria, but I still getting a error. Can anyone tell me where Iam going wrong.
FYear: Year([Award Dtd])-IIf([Award Dtd]< _ DateSerial(Year([Award dtd]),10,01),1,0)
View 14 Replies
View Related
Feb 22, 2005
Hi all. I have created a db for tracking different types of request (corrective action, preventive action, rework, scrap, etc). My problem is that the hourly overhead cost is different each year. (I use this cost to figure out actual cost of rework and scrap actions.) I know this is a simple solution but it is eluding me - I need to have this cost change each year but not affect the previous year's figures. Right now my formula is basic:
ReworkCost =[ReworkTotalTime]*69 ($69 is the 2005 overhead cost - this is the first year of use for the db)
I was toying around with using a table for cost alone and then using an If statement to pull the cost based on the year. Does anyone have a different idea on how to do this?
Thanks
Camille
View 3 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
Aug 12, 2004
Please advise how I can incorporate a time variable (in elapsed minutes) in a formula and have it update automatically every minute:
example: count the number of minutes elapsed from a point in time multiplied by a fixed rate (such as Gallons per minute) to produce a value of gpm over a period of time.
Any suggestion are welcome.
Stinhop
View 4 Replies
View Related
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
Apr 25, 2006
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
View 7 Replies
View Related
Apr 25, 2014
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
View 2 Replies
View Related
Jul 3, 2014
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
View 6 Replies
View Related
Jan 10, 2012
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year
y = Number of the day of the year (1 to 366) 'can this show three digits all the time?
hh = Hour in two digits (00 to 23)
nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I would prefer to see 120101304
View 4 Replies
View Related
Aug 31, 2004
Can you please help me with an “IF” statement. Here are the two formulas I want to use:
#1. ToDate Billing: DateDiff("d",[Start Date],Date())/30*20
#2. ToDate Billing: DateDiff("d",[Start Date],[End Date]/30*20
I want to use formula #1 (w/current date) if an end date is not entered. If an end date is entered, then #2 needs to be used.
I would appreciate any assistance anyone can offer.
THANKS!
View 2 Replies
View Related
Mar 14, 2006
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
View 3 Replies
View Related
Nov 26, 2004
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
View 6 Replies
View Related
Feb 15, 2005
Howdy,
I'm missing something because my calculations are off on a form. I have a form with a textbox labeled "txtStartupTemperature". The value I insert here will affect another value in a textbox labeled "txtStartupTCF". If my temperature value is <= 25, I will use one formula for my calculation. If my temperature value is >25 then I will use an alternate formula. Both formulas involve exponentials. Here's the code I developed for my public function:
Public Function TCF(ByVal sngValue As Single) As Single
Select Case sngValue
Case Is <= 25
TCF = Exp(3480 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Is > 25
TCF = Exp(2640 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Else
End Select
End Function
In my form's code I've inserted the following:
Private Sub txtStartupTemperature_AfterUpdate()
Me.txtStartupTCF = TCF(Me.txtStartupTemperature)
End Sub
I tested this code with a temperature value of 15. This should calculate out to be 0.67. Instead I'm getting 0.34. Are my formulas written correctly? If so, what else could be going on?
View 4 Replies
View Related
Oct 9, 2005
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
View 9 Replies
View Related
Jul 28, 2014
My database has a front end that uses forms to allow the user to navigate within it, each year we create a new database which is based on the previous year's database but with all the variable information removed.
Within the database each form has a caption that starts with '2014-15' then goes on to say the form name.
Most of my forms also have a yellow background, (last year it was pink).
Both these areas were set up so that each year our users can distinguish between our databases, as they may have last years and this years open at the same time.
What I would like if possible is to know whether I can get a user to change both these things from within a form. So something along the lines of a button that when clicked would check all form captions and if within the caption it finds 2014-15 then change it to 2015-16 (or whatever the user specifies) but keep the rest of the caption.
I would also want the same type of thing to happen with the colour so again within the background of the forms, if you find colour X then replace it with colour Y.
If this can't be done within a front end form, then could it be done behind the scenes, and if so how would I go about doing it?
View 2 Replies
View Related
Nov 12, 2014
I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
tblProject
Project: Myproject
Value: 1000
EndYear: 2014
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
View 3 Replies
View Related
Dec 1, 2006
hi
i wondered if anyone could help me with a formula i am trying to construct.
i have a report which is based on a query where the user types in the ID of the project they want to view.
here is a screen shot of the report
http://i132.photobucket.com/albums/q19/lost989/formula.jpg
i wondered whether it was possible to have a calcualtion to show how much was spent on different categories.
so there would need to be a formula to calculate all those records named as timber thier indivdual cost times the quantity of each. i need this for each category of items.
is this possible?
any help is appreciated
thanks
View 1 Replies
View Related
Mar 27, 2015
I want to be able to sum my net amount and other costs in my page footer but it is error why would this error in the page footer =Sum([net amount]) if I did that would it still give the total for more than one page on every page.
View 5 Replies
View Related
Apr 25, 2005
Hi,
I am currently a SQL DBA, and have been roped in to help a fellow staff member import some XML data into an access database. I am using Office 2003, and the Get External Data command. It successfully imports all of the data, but my colleague says that it is not acceptible because the data is being converted to UTF-8 encoding. He would like it to retain the same original encoding as the XML file. Is there any way I can do this?
Thanks.
View 1 Replies
View Related
May 2, 2008
I have got myself in a formula nightmare...I've written it so many different ways, that I forgot what exactly I was trying to do!
Here is my scenario:
A contract's original expiration date is mm/dd/yyyy [EndTerm]
Once it passes that date, it automatically renews for nn years [Renew]
Basing everything on the current date, I am trying to calculate that it has autorenewed x times [AutoRNo] .... whole number...no decimal places
I then need to calculate a "Next Expiration Date" which adds the number of years that have renewed to the [EndTerm]. So if a contract renewed for 3 years, and it renewed twice, the next expiration date would be 6 years after the orig expiration date [EndTerm].
View 1 Replies
View Related
Nov 10, 2012
This is a CSV file that comes through an Access specification. I need to be able to copy the items from the table to other tables but somehow apply a unique and sequential ID to the rows first, this is essential to the job. The text import spec applies a unique ID but save the spec, and call it using VBA, it doesn't do that.
Also, there seems to be a bug where using a macro to run the text import specification, (and this is after an update from Microsoft), that it doesn't recognize the specs anymore... after I deleted several of them.
View 8 Replies
View Related
Dec 15, 2004
Hi
I would like to count number of items witin a text field,
but breaking it down identifying the different items within the
text field.
e.g.
Got a field with fruits listed. Now I am identifying the fruit but want
to know how many rotten fruit there was for each specified fruit.
At the moment my query is by date and the fruit including the column
specifying if the fruit is rotten or not. But there is 3 options in the last
column. How can I count this last column to count how many of these
3 options there are for each fruit in my report?
Please let me know if this makes any sense or not otherwise I will try to explain
it a little bit better.
View 1 Replies
View Related
Oct 17, 2013
We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.
Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?
View 5 Replies
View Related
Jul 1, 2013
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
View 3 Replies
View Related
Jan 27, 2015
I need a formula to calculate date cycles.
Each person have an employment date on which a 3 year cycle is based.
So at the end of their 3 years the cycle needs to update to the next 3 year cycle.
For example
Person Employment date Cycle Start
1 01 Jan 2015 01 Jan 2015
2 01 Jan 2006 01 Jan 2012
In the case of Person 2 above the cycle would have started at 2006 - 2009 and then moved to 2009 - 2012 and then 2012 - 2015 and now 2015 - 2018
I have the following formula at the moment but need to make 100% I'm not missing something.
Also - would it be fine to run the update query each time for all persons or must it be only for the ones where the current date does not fall within the current 3 year cycle?
CycleStart = DateAdd("yyyy",Int(DateDiff("d",[EmpDate],Date())/365/3)*3,[EmpDate])
Would value your input on another way to do this as well.
View 3 Replies
View Related
