Formula Problem: Different Year = Different Overhead Cost

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 Replies

Fiscal Year Formula But With A Error

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

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 1 Replies View Related

Number Fields In Text To Number Field, Formula For Fiscal Year

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

Convert Formula Into Formula Result

Apr 6, 2007

Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks

View 7 Replies View Related

HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values

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

Sum Of Current Year Minus The Year Of A Date In Past?

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

Percentage Of Year Employed - Prior Year

Aug 1, 2005

I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.

This is being done in a query and we're using Access 2000.

Any help would be greatly appreciated.

Thanks,

View 6 Replies View Related

Queries :: Determine Date Given Day Of Year And Year

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

Year And Date - Show Day Of Year As Three Numbers

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

Using Query Formula Information To Create Another Formula In A Seperate Query

Nov 20, 2006

I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!

View 2 Replies View Related

Total Cost

Apr 3, 2007

Well at the moment i'm still in the design stages, but i made a database with test fields to see how it would work out best.

Basically i have to have a form with a drop down list for each type of computer componant and when selected the total price for all the items selected so far needs to show up at the bottom of the form.

Or when a button is pressed the price could be updated, that is fine too!

The way i was planning to do it was having an orders page, in which each field was a lookup to a difference table, one for each componant
Processor; ProcID, Proc name, Proc cost

Anyone have any ideas on the easiest way to load the total value for the items selected? Would be much appreciated! Thanks!

View 12 Replies View Related

Total Cost Help

Dec 8, 2004

I need to create a report that shows profit/loss totals for a vehicle that I have listed in a table named "Vehicle Table". I have four columns: Cost, Shipping, Promotional expense, Repair, that I need to total and then subtract from the sales price to show the profit. Then, divide the profit by different percentages to show what I owe each salesman. I need to keep the different columns seperate and be able to increase the dollar amounts in each column as a new invoice is received for each vehicle.
For example, I have a $25.00 wash job in the "Repair column" and then I have to install a new set of tires for $450.00. The total in the "Repair Column" should now be $475.00. I can not make it add to the current balance in each of the certain columns. Would someone be so kind and help?

Jeff

View 4 Replies View Related

Add 1 Year To Year Part Of Date

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

Total Cost Displayed

Jan 28, 2008

Hi,

I have a order system which allows customers to place items on. When an item is selected and a quantity has been entered and add button is selected, this adds the item into a listbox with all the details.
Each item that is selected a quantity is selected, this would then work out a total price and this total would be displayd within the list box too.
i want to add all the totals up for that order and have it displayed within a text box.

for example:

product ID, Name, quantiy, item price, totalcost
P0001 Top 5 1.00 5.00
P0002 Top 4 2.00 8.00
P0003 Top 3 1.00 3.00

i then have a text box where i want to display the overall total for these items within. hope this helps. im really stuck on this one!!!

View 4 Replies View Related

Tracking Share Of Cost

Nov 8, 2006

Hello fellow developers.

I need a query to help me track share of cost for clients. Anyone have any ideas? This is what I am working with. sorry it didn't paste very well. My problem is that my query keeps subtracking the share of cost and I want it to stop subtracking the share of cost once the share of cost has been met. HOpe that makes sense. Thanks in advance for your help in this matter. Angelflower.

BeginDOSEndDOSInvoiceIDProcessedTotalDaysRateTotalAmountMCostShareAmountPayable
10/1/200610/24/20065Resid10-06Yes24$47.00$1,128.00280.16$847.84
10/1/200610/24/20065Resid10-06Yes24$64.70$1,552.80280.16$1,272.64
10/25/200610/25/20065Resid10-06Yes1$45.29$45.29280.16($234.87)
10/26/200610/31/20065Resid10-06Yes6$47.00$282.00280.16$1.84
10/26/200610/31/20065Resid10-06Yes6$64.70$388.20280.16$108.04

View 1 Replies View Related

Getting The Right Cost For Purchased Itens

Sep 25, 2007

hello guys,

hope you can help cause this is turning me crazy.
it's an inventory issue. i have two tables:

1) StockIN: ProductID, PurchaseDate, PricePurchased
2) StockOUT: ProductID, OrderedDate, QtyOrdered

Throught 01 year, I can make 10 purchases of pens with different prices.

I need to run a query of an individual department's consumption of pens in the month of June for example. I can get the right qty, no problem. But for $$$ purposes, I needed the query to return the most recent PricePurchased as compared to the OrderedDate.

Any ideas??

Thanks in advance!
Tiago.

View 1 Replies View Related

Calculating Total Cost

Dec 7, 2004

I am currently making a database project for a school assignment.

I have based the project idea on a local hotel, which consits of a diffrent price dependant on the date booked.

So i have set up a table called "prices" which consits of the following fields:

PriceID
RoomID *
Price
Start Date
End Date

the RoomID is the foreign key, as it is linked (one-to-many) with my Room's table, which consists of the following fields:

RoomID *
Room Number
.. (unecessary fields for the problem)

The room ID is also linked to the bookings table (again one-to-many) with the bookings table consisting of:

BookingID
CustomerID
RoomID
Start Date
End Date
... (uneccessary fields)

what i am hoping to do is (i assume) make a query that will calculate the cost the customer has to pay. So it will look at the start and end date booked in the bookings table and comapre it to the dates in prices table, and find out the cost from this....

any help on going about this would be greatly appreciated, and i hope i have supplied sufficient information (i'll try attach a graphical image of the relationships if that is helpful?)

thanks

michael

View 2 Replies View Related

[Tables] Deduct VAT Off Cost?

Aug 3, 2007

Hi, I was wondering if there was an easy way to deduct VAT from ALL my records as I entered them with VAT, and now my boss is saying he wants them without VAT.

Is there an easy way to do this!? As there are thousands of records that I need to calculate the cost without VAT!!

Thanks.

View 2 Replies View Related

Assign A Cost/price Value Toodrop Down Box

Jan 17, 2008

Hi all,

Might be in the wrong place but didnt know it it needed to be in query, form or vb sorry admin.

I need help, what i need is a way of assigning a price too a drop down menu.

Basically its a Form with a number of drop down menus where you can select different varibles colour, finish for a certain product, but theres no method to generate a total cost of selecting different variables.

Do you know of a soloution becuase ive exhausted my capacity!!

Thanks

View 5 Replies View Related

Updating Total Cost In A Table

May 17, 2007

I have a table called: Invoice which has Invoice No (as the PK), Invoice Date, Total Cost.

I also have another table called Task: which has Task No (as the PK) , Task Type, No of hours, Charge Amount.

The user enters the Details of Task and Invoice in a Form. I have a field called Total Cost of my Form which should Calculate =([No of hours]*[ChargeRate]).

The problem I have is the total cost on the form is not being updated on the Invoice Table with The Total Cost.

Can anyone help please

View 3 Replies View Related

Calculating Line-Item Cost In Table

Oct 11, 2006

I am having trouble calculating a field in a table and on a form.
I have the following Tables
CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person.
ORDER: Customer No, Order No, Order-Date, Delivery-Date.
ORDER LINE: Order No, Product No, Quantity Ordered,
line-item-cost
PRODUCT: Product No, Description, Price, Product Image.

I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form.
I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work.
Does anyone know how to do this?
Thanks

View 1 Replies View Related

Calculating Line-Item Cost In Form

Oct 11, 2006

View 1 Replies View Related

Queries :: Find Cost At Time Of Order?

Jan 27, 2014

I have stored the costs in my database with a date they apply from so I have a series of costs which I can then refer back to using the order date to see what cost I should use at any one time. My table structure is as follows:

Code:

tblExtruderCosts
ExtruderID PK - identifies the extruder
FinishID FK - identifies the finish
Cost - the cost for that finish from that extruder
DateFrom - the date the cost applies from
tblExtHead
ID PK
ExtruderID FK
FinishID FK
Orderdate

View 5 Replies View Related

Queries :: How To Create Total Cost Field

Oct 27, 2014

I have a table called Price list, and in it I have two fields, one called Service and The other called cost. The services are listed and their matching prices are listed beside.

In another table called appointments, when making an appointment, I have linked the information so that I can choose from a dropdown from the price list table, under Service type.

I need to be able to create a receipt for the appointment. How can I do that in a query format?

View 1 Replies View Related

Forms :: Get Part Value From Postcode To Create Cost

Mar 26, 2014

I have a form which has a number of fields and 2 of them are called [Postcode] and [Cost].

The cost field is defaulted to "£75.00"

What I am trying to is after the user enters the postcode it looks at the first and second value and if they enter a postcode in Scotland like below it changes the cost field accordingly.

The changes will only effect postcodes in Scotland if that makes sense as we have 2 different pricing areas for these

examples of some postcodes
AB1 1AU £125.00
EH1 1RT £85.00
G1 1AT £85.00
IV5 1ER £125.00
ML1 1RT £85.00
and so on

so any postcode containing the following would update

AB = 125
EH = 85
G = 85
IV = 125
ML = 85
DD = 85
FK = 125
etc etc

all other postcodes in the uk will show the default value of £75.00

View 9 Replies View Related