i have
Movements_during_qry ------ movments to all employee during day
Movements_qry------- which including arrival in morning and departure in end day
How I combine two queries in one query to show data
example
In day 05/02/2015 show employee 1 show all movements and arrival (from 2 queries)
I currently have a database that has the following tables:
t_Employees
t_Cost_Centers
t_Changes
The [t_Changes] tracks what cost centers the employee is in with a start date and end date:
Change_ID
Employee_ID
Cost_Center_ID
Start_Date
End_Date
I want to run a query that will show a single line per employee, and different columns for each of the cost centers the employee was in. If the employee was in 3 different cost centers, the query would have 4 columns (1 for the employee, 1 for the first cost center, 1 for the second cost center, 3 for third cost center).
I saw another thread to linked to a concatenate example by Allen Browne, but that places multiple values in a single cell. I want different columns per cost center.
I currently have a table where we are logging a task number against a date and time for each employees to track activity.What I am looking to do is in a query have access lookup the previous reference number for the employee before the date/time of the individual record but on the same day
example
Staff 1 08/08/2015 12:00 Task Ref: 1234
Staff 1 09/08/2015 14:00 Task Ref: 3456
Staff 1 09/08/2015 15:00 Task Ref: 7891
to show something like
Staff 1 08/08/2015 12:00 Ref:1234 Previous Ref: Null
Staff 1 09/08/2015 14:00 Ref: 3456 Previous Ref: Null
Staff 1 09/08/2015 15:00 Ref: 7891 Previous Ref: 3456
Fields are
Employee
DTActStart - this is when task started
DTActEnd - this is when activity ended
Task ID - Reference for task (unique field)
I've tried seaching forums (Used DMax and Lookup) but doesnt find the most recent record before the in view record/I'm thinking a query is the best way so that when Print a report of tasks run of tasks will then show
I have a requirement to categorize a field in the employee database.
The requirement is: for any given user designation, if the total number is 10 or more, I need the actual designation itself as the output. However, if a particular designation in the organization is less than 10 in total, the output should be 'Misc - and the grade'.
Also, this will need to be done for each grade as well. For example, though the staff designation 'Software Engineer' count is 15, the designation is present across two grades A and B. So, in the output, for the staff with Grade A will show the actual designation as the count is 11, and for Grade B staff with the desgination 'Software Engineer', since the count is 4, it should display the output as 'Misc - Grade B'.
I would like to achieve this in MS Access 2010.
We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:
[Workdate].....The date the work was done
[WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7"
[Employee].....Employee Name
[Qty].............# of hours worked that day
[RegHours].....Blank, to be updated by query
[OTHours]......Blank, to be updated by query
The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.
Example - Working 6 day week, at 9 Hours per day in the [Qty] field
Day.............[RegHours]......[OTHours]
Monday..............9..................0
Tuesday............18..................0
Wednesday........27..................0
Thursday...........36..................0
Friday...............40..................5
Saturday...........40.................14
Sunday.............40.................23
We have tried several things to get it to compute this field and haven't had much luck.
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
i have been trying to create an employee database to record:
1. Name , Address
2. Telephone
3. Date of birth
4. employee ID#
5. Emergency contact -name, address, telephone
6. Photograph
7. Other pertinent information related to employees
So far I have incorporated the contacts database forms into my new database but I am unable to link the forms into one form that will incorporate all the information needed. Is there an existing employee database that I may edit to suit my purposes?
Hello,
I have an employee table set up like so:
NAME, SALRY, SUPERVISOR_NAME
This table contains a large group of employees, and every employee has a Name, Salary and Supervisor. Some of these employees are also Supervisors, and hence they are in the table once as an employee but also have their name multipe times under the SUPERVISOR_NAME field. But not all employees are Supervisors. So if "Joe" is a Supervisor a section of the table would look something like:
Alan $12,000 Joe
Sue $14,000 Joe
Rob $19,000 Joe
"Joe" would also have his own record in the table somewhere else and have a Supervisor of his own.
Joe $22,000 Fred
Rex $48,000 Fred
Sam $12,000 Sue
The top-level person in the table has themself listed as their Supervisor. There are a large number of employees who are not Supervisors, and I need to do some data manipulation on these employees (and be able to check whether or not any employee is a Supervisor or not). So these "subordinate-only" employees have a record for their Name, Salary and Supervisor, but nowhere else in the table are they listed as anyone else's Supervisor.
I would like to set up a query that, in English, says: Show me all of the names of the employees who are NOT supervisors. Or (maybe using parameters), is this particular employee a Supervisor?
Any ideas?
Thanks,
Scott
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column:
[Enter Driver Name]
Then I tried to enter the following in to the criteria field in the date column:
Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY]
However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?
I am trying to create an Access database where I can let the employees enter their time-in and time-out, at the end of their shift. I already created this function working, but running into another issue. Everyday, employees need to enter a break. Sometimes they take a break and sometimes they don't, if they work shorter hours. Thus, the break is not a default 30min and has to be enterd by the employee.
Below is the code I have to calculate the total hour:
txtCalcTime has the below code in control source:
=Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)
Control source for this form "FrmTimes" property, in which Txtcalctime resides:
QryTimes
"QryTimes" query has the code below:
SELECT TimeID, TimeIn, TimeOut, Brake, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime
FROM EmpTimes;
This shows the total number of hours worked. But how can I make it to substract the break time entered by the employee in "txtbreak" on FrmTimes
Is anybody out there kind enough to post a good head start sample db for managing employees with photo included in form and in report.
Would appreciate any help and thanks,
cagay
Hi all, I am looking for an employee record template for data including sickness, vacation, skills, CVs etc. I would have thought there would be loads available but alas, I can find none
Can anybody point me in the right direction please.
Thanks in advance
Rob
Trying to setup a database to track employee compensation and need some advise on the setup. I was told in a previous post to normalize my tables which I think I've done. I would just like some other input on this to know if I've done it right.
I created a table called Employees with the following fields: autonumber id field, firstname, lastname, department, position, hiredate.
Next I am creating a new table for each year which will contain the following tables: autonumber id, base, bonus, addbonus, overtimecomm, car, 401kcon.
The autonumber ID fields in each table have been set as the primary keys and I've setup a relationship between Employees and each of the compensation tables.
Does this sound like it will work? Or do I need to go back and rethink some of the process?
I have a form which has a drop down box which links to a table with Employee names. One employee quit and I want to delete her name from the table. But... if I do that, I will delete her entirely from all previous records. Is there a way to keep all previous records, but take her out of the drop down choices?
View 2 Replies View RelatedI'm trying to use a query to calculate the payroll for all the employees but it won't work. The two tables I have are 'Employees' and 'Employee Payroll'. In the 'Employee Payroll' table there are three fields- Employee ID, Days worked and Earnings. I need to calculate the sum of the earnings field but it just displays a blank list of records when I try it.
Can anyone help?
Thanks
I'm trying to create a database in Access to track various employee data for the company I work for. We have about 44 employees. There are about 7 different groups of data our poor secretary now has to keep up with. These include: a list of the employees, with name, address, phone, etc. A handmade form with salary information like current wage, raises and when they were given, etc. A bonus section, how much and when. A list with attendance information including tracking missed days and tardies. A list of temporary employees and when their incremental reviews are due. There are other lists we use and others we are thinking of starting having to do with training, etc. It’s too much to keep up with manually now.
One of my questions is: can there be one table with the basic employee information that other tables can access without having to enter the same names again into other tables? (Why enter all those names more than once)? My other question has to do with linking tables together, I just can’t seem to grasp the concept even after reading two Access training books on the subject. I made a simpler database for another project and gave up on linking tables and the database seems to work just fine.
Any help is appreciated and sorry for the long post.
I have an Employees table. I have a form to Add Employee and a form to Remove Employee. Remove Employee works great. It's a drop down box, they select the employee and click on remove. With Add Employee, I have a textbox where they enter the name and a button to add that employee to the database. I setup a VBA script to take the value in the textbox labled "empname" and insert it into the Employees table when the button is clicked through the onClick event.
Is there an easy way to do this? I need the UI to be simple. I know you can bound the textbox to the employee name, but then when they open up the form it will show all the records and they have to click the new record button to add someone. I know this question was asked very recently, but it wasn't answered very well.
I have the db attached. It's in Access 2003 format. The macro in there is the VBA script that I wrote, so don't worry, it's not harmful. I'm very good with SQL, and I would've chosen to use ASP for the user interface, but the people that need this don't want to run any servers (IIS). They just need a file they can double click on and work from there.
Thanks
I am nearly ready to release my ACCESS 2000 database to 150 employees in our office (client/server network). However I have two problems, I have looked through countless posts and cant find exacly what Im looking for.
ACCESS automatically defaults to each individuals network ID when it is opened, so I have set that as UserName for each user, I have created a UserDetails table with this netowrk ID as a primary key, linking that as a foreign key to various OrderDetails tables. My question is how on earth can I get ACCESS to automatically enter this UserName onto each order a particular user makes? to be able to run queries for specific users etc? I dont want the user to be able to manually type in this because it can compromise the integrity of our data.
secondly, what is the best way to set up the FE, BE situation on our network? I tried the wizard and it gets a bit technical
Cheers for the help and congrats to a very helpful site,
Russ :)
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
I am tracking training for employees. I have a database that keeps track of the dates a particular training was taken. Employee training is ongoing from year to year. I have an Employee Training with a subform showing only the specific employee's training. I need a help with a few things. I need to keep track of training by the year. If I enter 2006 dates it will show in one line, if 2007 dates, another, all the way across the list of training classes for an employee. I also only want to limit a person enter more than one date for a particular class for a calendar year...Are these things possible. I have attached the database. Please help, make changes and suggestions. Thank you so much in advance.:eek:
View 3 Replies View RelatedDear Access Expert.
I would like to create a Scheduling database for employees. The database would include:
- list all of the employees
-tabs to look at schedule for this week, 1 week from now, 2weeks from now, 3 weeks from now, long term (1-2 months), etc
-small comment section within each day for the guys to enter some comments...e.g. dentist at 10, holidays, etc
There has to be something like this already out there that I can modify and work with. Is there anything like this already in the NorthWind Database that comes with Access? What about some free examples Access developers made?
What about Outlook? Can I modify Outlook and use VBA somehow to accomplish this? Outlook is basically on an account basis so I don't know if I could tailor it to my 30+ employee database.
Thank you very much for your input.
My computer crashed and I lost my employee vacation tracking database.
Does anyone have an Excel spreadsheet or other tracking method they could share please? I really don't have time to build a new database.
Thank you for your help.
Would like to hear from anyone that has designed a database that can be used to track employee compensation on a year to year basis. I am current designing a database to do just that but I'm having a hard time deciding on how to setup the tables. What they use this for is yearly reviews so I need it to be able to pull data from prior years and the current year. I've designed one but don't think its going to work for me. Just interested to see if and how someone has done the same thing.
Thanks,
Chester
I have to generate unique code for each person in an Employee table. This code will not be the primary key for this table. Primary key is an auto number. This Unique code is for refering to each person in all the correspondence/document/everywhere and every person will know his code and refer in all the correspondance.
I want to make a column in this table for this code. There can be two criteria for this code generation--
1. The code will be First alphabet of first name followed by sr no for that letter in 4 digits. Say Mr. Andrew is 99th person with alphbet A then his code will be A0099. Or Miss Jany is first person with letter J, her code will be J0001.
OR
2. The code will be a five digit 7 digit number--of which the first 4 digits are year of joining in organisation and remaining 3 digits are joining serial number of this person in that year.Say Mr. Andrew is 12th person who joined in 1996 then his code will be 1996012. Or Miss Jany is 118th person who joined in 2000, her code will be 2000118. For this I will add two columns namely Joining Year and SrNo in this Table.
I want the Access, by some means to generate this code based on the above critaria in one table.Can you people help me with the ideas about achieving this task. Presently we are manually assigning these codes.
I've been tasked to create a report that shows the date an employee hit a loss of $200.00 or greater for the company. Each day they work, they will either have an overage or a shortage in their till fund. They start out each day with a set amount of money in their till and at the end of the day they are expected to turn in that same amount; the tills are used for making change. If they are short, and the amount is fairly large, hence the $200.00 mark, then it becomes an issue that needs investigating.
I need to total these amounts up and when the total hits a negative $200.00 or greater, I need to display the date it occurred and then display the amount the company would have saved if the employee had been either terminated or transferred to a non-revenue department.
Getting the "saved" amount should not be a problem once I figure out how to get the above indicated loss amount.
Any ideas?
Kevin
Hello all!
Ok, lets get down straight to the point.
What i want to achieve is that when i login that automaticly the name has been filled in on every form.
I have a login form. I put my name in there like "Michiel" and ofcourse the password. After that its going automaticly to the mainpage. In that mainpage i want to create a field that automaticly filles in my name (so if somebody else log in that their name will be filled in). After that i want when i go the incident form that automaticly the employee form has been filled in. (The person that at the moment is logged in that his name automaticly been filled in) Ive tried various ways to achieve this goal but nothing worked.
Im not that very expert in Access but im trying to understand and learning pieces by pieces the Access world. So if somebody could help me because im getting desperate! Thanks in advance all! :D
Greetz.
Creegfire