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 Replies
ADVERTISEMENT
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
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
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 7 Replies
View Related
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
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
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
May 7, 2007
Hi ya, the database that I'm working on has many employees for example 2 directors, 6 managers, 22 tutors (there are different types of tutors permanant temporary etc), 4 finance managers etc etc when you click on one of the employee type it expands and shows how many employee are under that section and their details etc, like when i click on director, there is this + sign beside it and when I click on it, it expands and shows me info on all the directors only.. How would I get that?? Do I have to make a seperate table for named employee type?? but then how would I add a relationship to it between employee and employee type?? how would I go about doing that...
Also I need to know how to do it since I need to use queries lateron which will only be about tutors and the courses they teach, and since there are many different types of tutors I'm kinda lost... Thank you for your help in advance!
View 1 Replies
View Related
Jul 26, 2007
Hi Folks!
I've been asked to make a 5 questions test to evaluation potential new employees' knowledge of MS-Access. Honestly, I need your input on what to ask. The goal is not to make a very hard test, but rather to see if somebody as at least basic knowledge of SQL and Ms-Access.
Some background information of what we do with MS-Access
We use MS-Access as a central hub for different download from different systems. We make a lot of daily imports. We export queries in Ms-Excel. We open it from Access and format the report. We make hundreds of reports every day. We don't really use forms or reports.
What questions to ask?
I was thinking of having 2 questions about SQL. One simple query with a formula, and a query with 2 tables (perhaps a left join). On what should my other questions be?
View 2 Replies
View Related
Sep 5, 2007
Hey guys-
I am importing data on a daily basis- some of it contains existing records, some of those existing records have updated information, and then the rest will be new records. I am importing into a table, say, tbImport.
I have 3 tables I am splitting this info out to- tbListings, tbAgent, and tbAgentOffice. Each record can have up to 3 Sales Agents on the file- Agent 1, Agent 2, and Agent 3. Each Agent has their own Employee ID, as well as Office ID, Mobile, Home, Pager numbers. Each Agent can belong to only ONE Office, but every office can have multiple agents. I am using the Agent's Employee ID (it's assigned by another SQL db- and I will never be assigning this freely to anyone else, or new agents- they come into the DB already assigned a unique code).
Agent 1, 2 and 3 can freely change positions (Agent 1 can be Agent 3 at times, etc etc) from record to record. However, I want to store all their contact info into a separate table, going for 3rd Level Normalization...
So what I have is this-
tbImport
_____________________
fldInfoID
fldWhatever
fldAgent1Code
fldAgent1Name
fldAgent1Mobile
fldAgent1Pager
fldAgent1Home
fldAgent1OfficeName
fldAgent1OfficeCode
fldAgent1OfficeFax
fldAgent1OfficeNumber
fldAgent2Code
fldAgent2Name
fldAgent2Mobile
fldAgent2Pager
fldAgent2Home
fldAgent2OfficeName
fldAgent2OfficeCode
fldAgent2OfficeFax
fldAgent2OfficeNumber
fldAgent3Code
fldAgent3Name
fldAgent3Mobile
fldAgent3Pager
fldAgent3Home
fldAgent3OfficeName
fldAgent3OfficeCode
fldAgent3OfficeFax
fldAgent3OfficeNumber
**plus a ton of other data- but you get the idea here
AND I want it to go into tables like these 2-
tbAgent
________________
fldAgentCode (Primary Key)
fldAgentName
fldAgentMobile
fldAgentPager
fldAgentHome
fldAgentOfficeName
fldAgentOfficeCode
fldAgentOfficeFax
fldAgentOfficeNumber
tbAgentOffice
_________________
fldAgentOfficeCode (Primary Key)
fldAgentOfficeName
fldAgentOfficeNumber
fldAgentOfficeFax
So- the tbAgent table will store all the contact info for every agent, and tbAgentOffice stores info for their offices. I know I'll have relationships from tbListings.fldAgent1Code (as well as Agent 2 and 3 at the same time) to the tbAgent.fldAgentCode. I'll also have a relationship from tbAgent.fldAgentOfficeCode to tbAgentOffice.fldAgentOfficeCode. So, when I do daily imports- I will need to run an update query from the tbImport table into the other 3 tables at the same time. My question is this-
How do I cram the individual fields for all 3 agents, into one universal field in the tbAgent table at the same time? I can't get my mind around the concept here. How will it take all the fields for each agent in the tbImport table- and cram them into 1 record set (while checking for existing/update records) in the tbAgent table? How do I write that in the sql code to tell it to link Agent2Mobile into the same Record as Agent2Code, and not into the Agent1Code or Agent3Code? Right now it seems to me that there's no way to distinguish between the Mobile Number for Agent 1 2 and 3 compared to their own Codes to identify the records with. Does that make sense?
Any help you can provide is EXTREMELY appreciated!
View 1 Replies
View Related
Nov 2, 2005
Hai guys Iam looking out for a sample DB for employees leave management.
# With leave application form
# Leave approval form
# Leave status
# All with username and password
Kindly help me out.
View 4 Replies
View Related
Oct 25, 2012
On my form I need to limit the number of employees that can be entered using OnCall as the criteria or use ExpName or Employee ID
If this criteria is NOT met then disable On Call. In other words if they try and enter a 5th employee then the field On Call/checkbox is disabled
name of field is OnCall and actual control is named On Call
I need this to fire on the OnCurrent Event for the form which is named frm_Employees_on_Call_What_Order
In other words, no more than 4 employees are allowed to be on call at the same time. The fields for the query the form is based on are:
All are form the Employees table
Employee ID Employee autonumber primary key
ExpName name of employee, includes first and last name
On Call Yes/No checkbox used for if a employee is on call or not
query used is named qry_On_Call
View 1 Replies
View Related
Apr 26, 2006
=HoursAndMinutes([TimeOut]-[TimeIn])
I have the above function that calculates difference between the start and end times for an employees shift. I have employees that work from 11PM one night to 10AM the next morning. When evaluate time like this the function returns negative values. Example: 10:00PM start time to 12:00PM next afternoon returns a value of -10:00. How do I show that an employee worked 14 hours?:eek:
View 1 Replies
View Related
Jun 3, 2005
hi, i want to calculate how long each person has worked at a company.
i am using access 97.
would i be able to enter something in the criteria that would work this out for me?
something like year([START DATE]) - year(Now())
this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .
thanks for you help.
View 8 Replies
View Related
Jul 16, 2014
I have a Access database of employees and I need to count how many worked for each organization in the company by month. I can't figure out how to break this problem down and solve it. I can do this ten ways from sunday in VBA, but I'm looking for an SQL solution.
My data looks like this (simplified) example...Table.jpg In this database, a process runs nightly and adds any new employees with the [CreatedOn] date. This value never changes. The [LastUpdate] field is updated to the time the nightly process executes as long as the employee is still at the company. So when an employee leaves, the last update field is no longer updated. So in my example data the employees in the 1st, 4th and 7th line no longer work for the company.
I've been trying to produce a query(s) with these results...Results.jpg I've tried several incarnations of various queries with subqueires to filter [CreateOn] and [LastUpdate] so that I can count the total employees in each organization at month end. None produce correct results.
My goal is to ultimately produce a chart that looks like this...Chart.jpg
View 1 Replies
View Related
Dec 16, 2011
Attached i have a Database with 3 Tables.
-Table MA's. is the just a table were you can select if an Analyst is at the office or not. so if the check box is selected the analyst is available.
-Table Backups. We have Alias which is a reference code to a supplier the 1 Analyst and then Backup 1 and backup 2
-Table suppliers are just de supplier details.
what i whould need is when in the MA's table an Analyst is not selected a macro or query should search for backup 1 and replace it. when the backup 1 is not available as wel than backup 2 should be put in place.
When no backups are availeble then a message can put in the cell like"No Backup Available'.
This result can be put in a new table named. Decksplit with then the Supplier Alias, Supplier Name, Analyst
View 4 Replies
View Related
Oct 27, 2014
I have a combo box based on an employee table. I would like a former employee's name to be removed as a choice combo box list, but their name kept in the employee table (and noted as an former employee) and other records related to them.
View 3 Replies
View Related
Sep 28, 2005
Hello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"
And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.
eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department
would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?
View 3 Replies
View Related
May 5, 2013
I am working on a school project called employee skills. I have a table of skills, each has a check box, which if checked is true. I am trying to write a query that will find who has a specific skill.
View 6 Replies
View Related
Jul 18, 2013
I have a form with date fields I need to calculate review dates for employees. Example 45 day 60 day 90 day what would be the best option to do this?
View 1 Replies
View Related
Nov 8, 2012
I'm still working on that sales database...and I now ran into an issue with multiple employees per client.
I have 2 tables, tbl_Employees and tbl_Clients.
tbl_Employees:
[Employee_ID]
[Employee_Name]
tbl_Clients:
[Client_ID]
[Employee1_ID]
[Employee2_ID]
View 2 Replies
View Related
Jun 15, 2014
I'm developing a database that tracks monthly metrics of employees. These employee are at several different locations.
Here's what I would like:
A table of the employees and their location.
A table of the metrics.
A form that you can select an employee and it would autofill the location and then you could fill out the metrics.
the ability to change an employee's location without it effecting past records.
A report, by month and location of the employee's metrics. (pretty sure I can do this on my own, just can't get to this point.)
I've tried to use a auto lookup query but that then changes the employees location on past records.
View 4 Replies
View Related
May 19, 2013
design a database in access 2007.
employee personal recordemployee job statusleave recordinventory record issue to employeejob performanceinventory control of equipmentforms, queries, tables, login forms.
View 2 Replies
View Related
Jul 10, 2015
I'm creating an Audit form with a combobox from which the employee name performing the audit can be selected. The data source is the EmpNames query which selects from the Employees table, linked to another database. The EmpNames query includes a record selection criteria where Employees.TermDate is null so that terminated employees will not appear in the dropdown list.
This is fine as audit records are being added in the form for current employees, but in the future, if an employee is terminated and we go back to one of his audit records, the name will be blank because EmpNames won't include it. The Audit table doesn't store the employee's name -- just his employee ID, and the combobox has a two-column data source where the column 1 width is 0 and column 2 is the name. Is there a way to have only current employees be selectable but still be able to see terminated employees in existing audit records?
View 4 Replies
View Related
Aug 6, 2013
I have a database and form that is used to register times and various codes for employees to enter their work schedule for the day. Right now I am using a split form where they enter a ID code in the form and it auto populates with their name/department/and date. They then go and fill out a few boxes regarding time spent, machine used, and purchase order.
Once the information is added it is seen below due to it being a split form.
My question is this: Can I make the bottom portion (split form) filter and display only that specific employees ID number and his previous entries? Right now it is possible to view all of the entries placed and due to it being used on a network connection there are many submissions, I want it to narrow down so that once the employee enters his/her own ID only their previous submissions for that day can be viewed.
View 1 Replies
View Related
May 8, 2014
Currently if an employee attends a mandatory training session the details are entered individually into in a single table which contains all the employees' attendances to training, this is achieved via a bound form.
Works Fine.
My question is, if the training is carried out by a number of employees can this attendance be recorded on one form and assigned to each of the employees who attended?
Using Access 2003
View 3 Replies
View Related
