Queries :: Generate Amortization Schedule For Each Asset

Jul 19, 2013

I'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.

View Replies


ADVERTISEMENT

General :: Way To Calculate Accrued Interest Or Amortization Schedule In MS Access?

Aug 1, 2013

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

Asset ID Table

Oct 18, 2007

I have decided to futher develop / normalize a db I have been working on for well over a year now (keep promising to post the updated version when complete!!).

I currently have a number of tables that may or may not require an Asset ID.

i.e.
tblComputers
tblSoftware
tblHardware

I have seperate tables:
tblComputerAssetID
tblSoftwareAssetID
tblHarwareAssetID

Can any one advise a better approach? If I have only 1 table tblAssetID, then I am unable to enforce referential integrity as values must exist in all related tables.
Thanks,
Phil.

PS: There is more but I will post back with further questions when I resolve the above!!

View 4 Replies View Related

Asset ID Table / Tables

Mar 13, 2007

Hi guys. I have been attempting to add an asset tracking function to my database. I have a table 'tblAssetIdList' which holds all the available asset ID's. Asset ID's can be allocated to either Computers from tblComputers, Software from tblSoftware and finally Hardware from tblHardware.

I initially created additional tables to hold details of the allocations: tblComputerAssetIdAllocation, tblHardwareAssetIdAllocation & tblSoftwareAssetIdAllocation. I then created queries from the above tables and then forms from the queries to allow for data input.

The one flaw (quite obvious really) is that asset ID's should be unique. Although unique Asset ID's are required in the individual tables I am able to allocated the same Asset ID to the different tables. (i.e. A20222 can be allocated to only one computer in tblComputers but can also be allocated to both Hardware & Software once).

I then tried creating a single table tblAssetIdAllocation with fields:

ID
AssetID
ComputerID
SofwareID
HardwareID

These linked to the appropiate tables. However if I allocated an asset ID for computers then the same asset id was then allocated to hardware & software.

I must be close (only taken 2 days to get it wrong!! :) )
Help Appreciated,
Phil.

View 5 Replies View Related

Assigning Asset To Employees

Jul 26, 2005

Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)

I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)

any help is very much appreciated.... thanks :)

View 1 Replies View Related

Auto Populate Asset Number Without Having To Enter It?

Apr 14, 2014

my asset numbers look like this MIT000001DT, MIT000002DT, etc is there a way to auto populate my asset number without having o enter it. I was thinking it would look like this $$$000001$$ + 1= (new asset number)?

View 10 Replies View Related

Reports :: Generate Report From More Than Two Queries

Sep 10, 2013

I have to compile a report from more than two queries. All queries have a field common between them.

E.g.
query1 gives name, address and contact number of all the 30 students in a class
query2 gives semester wise marks obtained by each student
query3 gives performance in sports of each student.

All the three queries have name of the student common in them.

Now I wish to generate a report card which should be as under:

1. The record of query1, query2 and query3 corresponding to a particular student should come together.
2. The record of query1, query2 and query3 corresponding to the next students should come after that and so on.....

I tried using a sub-report but it ends up displaying all the records of query1 first and then all the records of query2 and so on...

View 7 Replies View Related

Modules & VBA :: Asset Database - Special Characters In DLOOKUP Expression

Jul 29, 2013

I have an asset database I am designing to manage our computer inventory and assets.

I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...

My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -

Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.

My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.

Here is my expression -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub

Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.

View 12 Replies View Related

Forms :: Asset Tracking When Items With Unique ID Transferred From One City To Another

Aug 6, 2014

I have a task of managing hardware assets like desktop, monitor, HDD, routers etc. Each item is marked with a unique ID.

These items could be transferred from one city to another city; so need to track that too.

1. There will be two tables

One (say AssetDetail) will have the following fields
RecordID
City
AssetID
Description of the asset (desktop/monitor etc)
ProductSerialNumber of the asset

Second (say AssetTransfer) will have the additional fields
NewCity
AssetTransferredDate

2. I only want to open a single form :

a) where on the basis of either AssetID or the ProductSerialNumber, I could open a particular record and edit it.

i) if the asset is transferred from city A to city B; then the "City" field value in AssetDetail table should change to "city B"(New City field value).

ii) At the same time, each asset transfer record should get appended to the AssetTransfer table even if the table has an existing record with the same AssetID or ProductSerialNumber.

3. Also need an option to make a new entry to the AssetDetail table from the above mentioned single form.

View 4 Replies View Related

Queries :: Generate A Query From A Table That Has No Data

May 19, 2015

I currently have tables that are in the database but they require the user to import the data from an excel file. This works fine however due to the data being imported I only require certain columns from the import, this is where I would use a query.

The data of the import will always have the same column headings. Firstname, lastname, usernumber etc..Unfortunately i'm not quite sure how I could get a script to generate a query of the selected columns after an import is completed.

View 8 Replies View Related

Queries :: Using Combo Box Case To Run Query And Generate Report

Jun 30, 2015

I am create a database to track leave of my team.I have create the tracker and few reports, however now I want to create a report by applying filters in 2 places, for associate and for leave type or for associate and for joint leave or for associate and for Informed.I have SQL code which does not work. how can I select the other paramater with associate name using combox box in form.

Code:

SELECT Leave_Records.[Absent Date], Leave_Records.[Associate Name], Leave_Records.[Absent Day], Leave_Records.[Leave Availed], Leave_Records.[Joint Leave], Leave_Records.[Informed TL], Leave_Records.[Leave Type], Leave_Records.[Leave Applied], Leave_Records.Comments
FROM Leave_Records
WHERE (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Leave Type])=Forms!Report_Form!Leave_Type)) Or
(((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Joint Leave])=Forms!Report_Form!Joint_Leave)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Informed TL])=Forms!Report_Form!Informed));

View 1 Replies View Related

Queries :: Sort Crosstab Query Columns And Generate Report

Jul 28, 2015

This is a query, report and vba question. I'm using Ms Access 2007.

TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate

I created a select query to join the info that I need.

SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))

I have 2 crosstab queries.

CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))

I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).

SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)

It gives me this:

However, I want it like this:

Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.

View 8 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Queries :: Run Query Multiple Times (different Data Parameter) To Generate Single Dataset

Mar 30, 2015

I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.

I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format

The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)

So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.

How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)

The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.

View 3 Replies View Related

Queries :: Passing Filtered Subform To Query To Generate Multi-page Reports?

May 24, 2013

I have a filtered form with a sub form displaying only non-printed invoices.

The sub-form has a check-box (which is how the form is filtered) to only show unchecked (not yet printed) invoices.

All i want to do is print only the invoices visible in the sub-form.

I've setup the report to link to a query and the report is all setup, i just can't get the query to function how i want it to!

I've got this SQL code for the Query.

Code:
SELECT tblOrderForm.OrderNo, *
FROM tblOrderForm INNER JOIN tblOrderDetail ON tblOrderForm.OrderNo = tblOrderDetail.OrderNo
WHERE (((tblOrderForm.OrderNo)=[Forms]![Export Orders]![ExportSub].[form].[OrderNo]));

However this only shows the field with focus. I would like it to display ALL data in the subform.

View 1 Replies View Related

Schedule?

Feb 22, 2007

Hey thereI'm currently doing an ICT project for some school work, in which we all have to make access databases. My database is based around a fictional company that provide driving lessons. I've set most of the tables up including clients employees, bookings etc, but i'm wondering if its possible to implement a schedule within access?I would like to know if its possible for say if someone booked 2 hours of lesson, 2 blocks within the schedule table would be filled, or similar to a calender function, e.g. each day has 6 openings within it, and as these become booked the next day is filled.i've searched around a bit and can't really find anything but i'm very new to access so any advice would be much appreciated, or just being told that it isnt possible to implement such a schedule or calender would also be useful as then i wont be wasting time looking :PHope i've posted this in the right place, sorry if i haven't.Thanks in advance.BenJust found this website: http://www.aislebyaisle.com/access/calendartool.htmkinda similar to what i'm looking to do, just wanna know if theres anyway of doing this thats less complex? or free?

View 4 Replies View Related

Staff Schedule - 'how To' Help..

Aug 2, 2007

Hi all,

I'm relatively new to Access and would like a few pointers...

I have been asked by work to create a schedule database. We would like to be able to put in dates and see who's working on that day, pull up an individual and see his/hers rota for a week. Also see if they have meetings, annual leave, sickness etc.... We want it to be a very primitive WFM tool. Is this possible and is it relatively easy? The database doesn't have to create the shifts, they will be imported from Excel....

Which leads me to believe I need to think of it differently to excel... Attached is an excel rota, which would need to be importred - but maybe I need to be think a lot differently.

I know thats a big question but any help would be appreciated, or pointers to more resources... Thanks
Neil

View 4 Replies View Related

Schedule Next Appointment Automaticly

Dec 29, 2005

Good Day everyone,

I am looking at setting up appointments and having Access automaticly schedule the next appointment in either 6 months or a year depending on the Vender. I've search the forum and can't find anything.

For Example if their last appointment was 12-1-05 I would like access to automaticly schedule the next appoint 12-1-06.

Any ideas?

Thanks,

View 9 Replies View Related

Advanced Employee Schedule

Jul 4, 2006

Hello Everyone
I need to make an employee schedule in access. I am not sure if it is possible, but here are my constraints:

20 employees

5-6 different truck locations (sm1, sm2, sm3, sm4, sm5, sm6) These locations are scattered within our service area.

2 employees must meet at the same truck, (each truck has 2 employees working on it)

some employees can only meet at certain locations (ex. paul can meet at either sm2 or sm3)

all employees are PT so they can only work on certain days or a certain amount of days per week. (ex. paul can work up to 4 days a week and jim can work only sat and fri)

I would like to make a program in access that will take all these factors into consideration. EX. if i try to schedule paul on sm4 the program will not let me do it or it will give a warning or if i try to schedule paul for 5 days it will give me a warning.
It would also be great to have a counter that will show how many days each employee has left to be scheduled for, EX: if i scheduled paul for 2 days it will show that he can actually work 2 days more if i need him to.

I know this might be a lot, but i also know there are so many talented people here so i am sure it is feasible. Please let me know if you know how to do this or if you could guide me through it.

or if you know about a different program that can solve this please let me know
Thanks in advance

View 2 Replies View Related

Schedule A Task In Access

Oct 25, 2006

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 Related

Schedule A Compact & Repair

Dec 12, 2005

I have a database that is split. We want to be able to schedule a compact & repair on the back end.
Is there a way within access to schedule a compact and repair?

View 2 Replies View Related

Using Access To Create A Perpetual Schedule

Aug 10, 2005

I 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 Related

Recall - Create Schedule (sbaxter?)

Dec 20, 2004

This could be one for you Mr Baxter.

I want to automatically create interview slots. On a form I've got an interview set up and there is a one to many relationship with a table containing interview slots. I want the user to key in the start time of interviews and the end time and the duration of the interview. It should then create the records (time slots) and display them.

I assume it will be some kind of append query, but to be honest I just don't know where to start.

Please help. A cyber pint goes to the boffin that susses it.

Cheers,
Recall.

View 3 Replies View Related

General :: How To Display And Maintain A Schedule

Sep 7, 2014

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.

View 3 Replies View Related

Reports :: Creating A Classroom Schedule?

Apr 6, 2015

how to go about creating a report that lists a traditional style class schedule?

I would like the times on the X axis and the days of the week along the Y axis. Ideally, the name of the class and student list would be included.

I have all this information in a database, but right now my classes are being generated in an excel program (by hand) and I'd like to automate it somewhat since some class information is being changed on weekly basis.

View 14 Replies View Related

Reports :: Automated Rotating Schedule

May 2, 2013

What I would like to accomplish is to make a report similar to this, except make it automated so I won't have to change the dates. Users would be able to bring up a current schedule without worry that it's updated or not. The schedule repeats itself every 6 weeks. I made a cross-tab query, but I must admit that I'm not too familiar with working on them.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved