=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:
I'm trying to establish the hours worked from start time to finish time. I have created a field for the result (Total time worked), the problem Im having is that some of the start times are 18:00 to a finish time of 08:00 which should be 14 hours but I can't seem to get it to work.
View 2 Replies View RelatedHello,
Using Access 2000, linking tables to Oracle 10g with a db trigger forcing the default display format within Oracle to dd/mm/yyyy hh24:mi:ss (ie 24hr time format). Unfortunately MS Access displays in 12 hr format in the datasheet grid. A Sybase article reported this issue:http://www.sybase.com/detail?id=1024202. Do you know if this has been fixed for more recent versions of MS-Access?
Thanks
Pascale
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!
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?
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!
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 :)
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.
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
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.
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
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
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 RelatedHello, 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!?
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 RelatedI 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 RelatedI'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]
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.
design a database in access 2007.
employee personal recordemployee job statusleave recordinventory record issue to employeejob performanceinventory control of equipmentforms, queries, tables, login forms.
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?
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.
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
I need the ability to assign employees to a different task in each of four different timeslots on a daily basis. What I would like to do is as I assign an employee to a task in timeframe 1, I would like for them to become unavailable to assign to another task in the same timeframe.
For Example: Three employees to assign to three tasks at the 8 AM to 10 AM time slot.
Employee
Task
Time
Assigned
Emp 1, Emp 2, Emp 3
Task 1
8 AM to 10 AM
Emp 2-Task 1
Emp 1, Emp 3
Task 2
8 AM to 10 AM
Emp 3-Task 2
Emp1
Task 3
8 AM to 10 AM
Emp1-Task 3
It would be nice to set this up in a form where my person doing the scheduling can assign an individual to a task and that individual name disappears from the combo boxes, list boxes, or is "greyed-out" in a listing of employees available for the time period for the remaining assignments.
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
View 7 Replies View RelatedSo basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error