Is there a way to calculate accrued interest or an amortization schedule in MS Access? I know it's easy in Excel but I have a need to do it in Access.
View RepliesI'm working on a database where I enter a series of assets and their costs, and I'm trying to build a query that will generate an amortization schedule for each asset.I'm using an amort methodology that amortizes a certain amount each period based on a calculation. I've got a query that generates the amount that would be amortized based on that calculation.
My problem is in the last period of amortization before the asset is fully amortized. In that last period, the amort methodology may generate an amount to be amortized that would be greater than the total value of the asset. In such cases, I want the amort for that period to be the total value of the asset less the cumulative amort up to this period.
So, for example, I may have an asset that is worth $225. My amort calculation generates an amort amount of $50 per period. For the first four periods, the amort would be $50. But in the final period, only $25 would remain unamortized, and in that instance I want the final amort amount to be $25 instead of $50.
[AssetName], [MonthStartDate] (i.e. the first day of the month for the period of amortization), [BusUnit], [RawMonthAmort]. [RawMonthAmort] has an expression that performs the calculation that determines how much to amortize. I've tested this part and it all works (except for during the final month of amortization, as noted above).
I tried including another layer of conditionals in the [RawMonthAmort] expression that would calculate a cumulative total amort and check that against the total asset value (by bus. unit) and give the difference if the cumulative total amort exceeded the asset value, but I couldn't figure out how to make that work.
I have 6 machines that run jobs Monday thru Friday but I will focus on doing one machine to avoid confusion.
The machine will be running 23 hours a day Monday thru Thursday and 18 hours on Friday and is shut down on Saturday and Sunday. Monday morning at 5am (start of the work week) Job A starts. The job will run for 30 hours. Then Job B will run for 160 hours. I need to know when Job B will start and end. Although it would be nice to know the time of day, just knowing the date would be good enough.
As I said I have 6 machines and each one is running anywhere from 7 to 10 jobs. My end result could be nothing more than a report, but a datasheet form would be ideal. On Mondays, new jobs are added and schedule is updated based on actual productivity from the prior week.
I wana ask about calculation hours for schedule table. lets say how to calculate based on what i select from my schedule table for :
no class: how many hrs
lesson i got class: how many hours.. for example: 1+0.5= 1.5 hrs
total of hours: which is based on "no class" + " lesson i got class" for example: 1+1+0.5= 2.5 hrs
I have a table called schedule maintenance.
In that table I have a "date" field
Is it possible that if " todays" date = a date in the field date a e-mail can be sent to me to remind me there is a scheduled maintenance to be done.
I need to make a report that shows a work schedule for 10 employees. I need it to print in a monthyly calendar format with horizontal lines to show start and end date and time. I need it to show that there is or is not any overlap for that machine."
It has been a while since I have created a DB and I get some of this okay. So far I think I need three tables. One is the machines. Two is the product schedule (how long it takes to produce X number of products). The last would be work orders and that pretty much covers the data.
I think most of the data can be maniputated by queries and that is even done on the report. The report as we all know is the reason for a database. If it was just about the data it would be simpler to do a spreadsheet. It could get fancy and make a pivot table. That output can be a calendar of sorts.
I have been tasked at my work to create a database for tracking time off from work. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date 2 – Date7
I now have a form. This form has the 7, fields Date1, Date2 – Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,
Date3 = [Date1]+2 and so on until I have all 7 fields showing date from Sunday – Saturday.
These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2….. fields to show their respective Work Week codes by writing very simple “If Then” statements . So here is where I AM STUCK…When I am showing this week view how do I get D1 –D7 to identify what date they are supposed to be in reference to Date1 – Date7 then compare themselves to the table “Time Requests” to see if they have a match and then set the value of D1, D2 …. to the “Time code” in that table. D1 – D7 need to auto populate and do this for about 50 employees.
Here is a screen shot of what I have so far and where I am stuck: [URL] ....
Hi i would like to know if it is possible to schedule a a task in access, at the moment i refresh all my excel report by using a button on a form in access and would like ot know if i can do this automatically
View 7 Replies View RelatedI am trying to create a database to schedule customer visits. I would like to enter a beginning date and a frequency (IE every week or every two weeks) and then be able to print a schedule by entering a date range. Very similar to how MS Outlook calendar performs. Any suggestions appreciated. Thanks Chad
View 2 Replies View RelatedI have a report in MS Access. It needs to be converted to pdf and sent to a list of people as mail through MS Outlook at 12:00 Noon on Monday and Thursday. How can I achieve this. I don't have much knowledge regarding macro programming. Any relevant links.
View 4 Replies View RelatedI need to create a database use access that the access can update the database based on a schedule, like daily.For example, in the table there are several columns, and each column stands for a meter point, and in real world, each meter will have a reading every 5 mins. Now the database consists of all the readings till yesterday 11:55pm, by right, tomorrow for example at 8am, the database will update the readings till tonight 11:55pm, today's readings will be placed right after below yesterday 11:55pm row in the same table. The data source will be generated in CSV file every day 8am and put into a specific folder. What I need to do is to update them into this database.
View 8 Replies View RelatedI am trying to have the following variables input:
Starting balance
APR (compounded daily)
Amount of monthly withdrawel.
Term
I want to compute the ending balance.
Can anyone help please?
Thanks in advance.
I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.
Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?
If this can only be done in VBA, any reference I may start with?
I'm trying to create a compound interest form using loops and the following formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
I'm not sure whats wrong with my code and it's not working!
i have 3x time fields in a table i.e time received, time started, time completed i need to accurately caculate the time between time received and time completed. whenever the time goes past 00:00 it seems to add 24 hrs to my calculated time is there a macro or code that i can use to rectify this problem?
View 6 Replies View RelatedI have an birthday report for the current month (august) and would like to make a button on the report that can calculate next month (september) and shows birthdays for that month. how to calculate next month?
View 7 Replies View RelatedI have a database to monitor incoming emails from our contractor & my superior wants it to have control like the following situations:
1. If no respond yet, to calculate today's date minus received date
2. If responded, to calculate respond date minus received date
If i use the following expression, it does not relate to if the emails have no respond yet or have been responded.
IIf(IsNull([Reply Date]),DateDiff("d",[Due Date], Now()))
Here is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT
EmployeeID - Primary Key - Number (not an autonumber because they are county assigned)
LastName - Text
FirstName - Text
EmailAddress - Text
JobTitle - Text
MobilePhoneNumber - Text
Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
Currently using a lot of Power Pivot Tables which in part calculate "averages" - but when the data comes form a Query that contains a calculated field using the iff() funtion such as: Outcome: iff(Status = "P", 1,0) we end up with a lot of zeros that create a problem in calcuating a true average. In the following example 2,3,0,1,0,0 the average is "1" but if the zeros were blank the average would be "2" because the blank cells would not be counted.
Is ther a way to have something like: Outcome: iff(status = "P",1,"blank") but what I want is ether 1 or a blank cell, NOT TEXT. If we use "" for the false outcome we still have cells that look blank but are in fact text cells and are counted in calculating average.
I have created a database that records the scores of each player etc and puts them in league table like structure.
Within this database which I would like to extend I would like to know if its possible to have a query to calculate a league table for the teams if home team overal score is greater than away team home team get 2 points for win 1 for draw no points of loss and same if away team won.I also want to be able to change it to a web database and add club address details like contact list to it.
i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.
what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]
am i going about this the wrong way of is my pricipal correct but my expression wrong?
I am trying to write a function in an access 2007 query that will calculate the date range for each of my records based on the month-ending date and the In-transit days assigned to each record (excluding any weekend days) (i.e. if the in-transit days number is 5 & the month ending date is 5/26/12 then my date range would be 5/22/12-5/25/12). I want the funtion to calculate the range, I cannot use parameters as each record will have a different range based on it's in-transit days .
View 1 Replies View RelatedIs it possible to set an access database to take a depreciation value of lets say 10.00, and subtract this from the current value every month?
So far my database has a form where you can input the depreciable life, purchase price, current value and total depreciation. However the date and amount of depreciation has to be added manually into a data table for the text boxs to calculate how much it has depreciated and the current value.
I'm trying to show the modal value of a column in a query that only has text in, not numbers. I want to present this in a text box in a report.
My report already has a query as its record source that only shows data for a given year and given month, these are chosen using parameters created in the query. Thus all I need to figure out is how to show the modal value for the column "Categories" on the report somehow.
Here are my parameters:
Query: [Formula Query]
Column: [Categories]
I have this table
Year AvgOfValue
2005 109.061690295772
2009 106.801581389669
I have to calculate the percentage of change of the values. I have very basic knowledge. How can I make a query to calculate the percentage of change?
What I'm looking to do is calculate the success rate (%) of compliance rates with Quality Control paperwork. Essentially, each record has 12 "check box" fields representing the different QC sheets that are submitted each day.
QCflavourChange
QCfillerOperator
QCblowMoulding
QCtorqueTest
QCnetContents
QClabeller
QCpacker
QCpalletiser
RMpreform
RMclosure
RMlabel
RMcarton
I have been able to find the code for counting the success rate of a specific type of QC sheet across multiple days worth of testing, eg:
=Sum(IIf([RMpreform]=True,1,0))])
But I have been unable to determine how I can count the total of all the successful fields listed above, across a single record.