Queries :: Sum To Work Out Annual Leave
Mar 6, 2015
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
View Replies
ADVERTISEMENT
Dec 1, 2014
On the surface this seems easy, but I am struggling. two tables
tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months
So I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. I cannot determine the correct query formula.
View 8 Replies
View Related
Apr 12, 2014
I've worked out how to sum all my sales (turnover) from a given nominated date...but what I seek now is to project forward what the annual turnover will be (which will quickly give me a 'pulse' & indicate if I'm growing as my financial year progresses)
My financial year started on Feb 4 2014 ...I can easily sum all my sales from that date, but ideally what I'd like to do is divide that sum by the number of days elapsed (this will give the average turnover per day) & then project that forward to Feb 3 2015 (the last day of my financial year)
So is there anyway that access can work out the number of days that have elapsed between 4 feb & 'now' (to get the average) ....but more specifically also how many days between 'now' ....and Feb 3 2015 - I then can use the remaining number of days to multiply the average daily takins to give an indication of what my year end annual turnover will be :-)
Or am I faced with having to do such calculations manually outside of access?
View 2 Replies
View Related
Feb 22, 2005
I've just been asked to generate an annual report, to the 1st of January of every year the current expression i'm using is for up to date, how would i change it to annual example Jan 2005
Years Of Service: DateDiff("yyyy",[EmpHireDate],Date())+(Date()<DateSerial(Year(Date()),Month([EmpHireDate]),Day([EmpHireDate]))) & " Years, " & (DateDiff("m",[EmpHireDate],Date())) Mod 12 & " Months"
Thanks...
View 4 Replies
View Related
Dec 5, 2005
:confused: I wish to construct a form that displays a graph showing the monthly sales totals year on year. In this way - I will be able to see quite easily how the present monthly income compares to previous years.
Therefore, each year will have its own data series on the graph... the problem I have is that I wish the graph to automatically recognise the roll-over to the next year thereby creating the next series for that year.
This would mean having the months Jan-Dec on the X axis and the income scale as the Y axis. Each data series representing the year.
Otherwise, the alternative is to manually change the underlying datasource every year to include .... has anyone done anything similar to this that they would be willing to share please?
Thank you.
Guido
View 7 Replies
View Related
Jan 26, 2008
Need a little professional advice.
I have a product table. Those products expire over time. Their shelf life is monthly, quarterly, or annual.
What should I use for monthly, quarterly, and annual in my table.
My first thought was 30, 90, and 365. Then in my query I would use the datediff function. If the datediff was >= to ...then ...
However there are months with 28,29, and 31 days. With this quarterly can change as well. The annual as well by 1.
What is the rule, if there is one?
View 9 Replies
View Related
Jan 27, 2005
Hi there,
I've a button that position the table on a new record:
DoCmd.GoToRecord , , acNewRec
And I create a button to cancel that action:
Me.Undo
But the code field (autoincrement) continues autoincrementing! Explaning:
1. I click on "Add" button
2. The table's positioned on autoincrement code #1
3. I click on "Cancel" button
4. The register is cancelled, it isn't includded on table
5. I click on "Add" button
6. The table's positioned on autoincrement code #2 and the #1 isn't exists...
How it's happen? How I solve it?
Thanks
View 8 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
Jul 10, 2013
I am trying to select a few values for my report, can this be done in the report design view or do I have to make a query and take from that?
I am using access 02!
EDIT: I want to leave out all the values that are 0!
View 2 Replies
View Related
Apr 26, 2006
Hi,
I have 2 tables... one with 'answers' (on questions) and one with these 'questions'.
Now I want to show the questions with their answers on a rapport
(based on a query) .. but only these whereby the answer-field is NOT NULL..
So I have already:
ansques1: IIf([answers.1] Is Null;Null;[questions.1]+" "+[questions.1])
But now if the answer-field is NULL, then the ansques-field is also NULL...
I want if the answer-field is NULL, to have no new field.. like ansques1.. just want to leave it out of the query... can someone help me with this?
Thanks in advance,
Ankie
View 2 Replies
View Related
Oct 5, 2005
I have an unbound text box that displays when certain criteria exits, which I placed in the Form's properties, "On Current" ...
If [Decor6Date] <= Date - 30 And [NonrecommendQuestion] = 0 And IsNull([DateDecReceived]) Then
[LateDecoration2] = "Late Decoration"
Else
[LateDecoration2] = ""
End If
I then made a timer event to show/flash the message
Me!LateDecoration2.Visible = Not (LateDecoration2.Visible)
Everything works fine ... the problem is the message will only flash if I exit the record then return to the record. How can I fix that problem? I tried Refresh ... no luck.
Any help would be appreciated.
View 1 Replies
View Related
Apr 13, 2011
I am trying to create a database to record employee sick leave and to indicate when an employee has surpass they allotted sick leave yearly.
I have the following tables and attributes
Employee Table
ID#, FName, MInitial, LName
Status Table
Category
Allocation
Transaction Table
EntryDate
StartDate
EndDate
TotalDays
Certify?
DaysBalance
Category indicates if a person is Temporary, Appointed or Substitute and Allocation indicates how many sick days is allocated per category yearly. I place both in the same table so when the status is selected the allocation is autofill
I am thinking of creating a form with the Employee Table info at top and a subform with the status and the transaction information.
I would like when a new year begins the sick days not used is discarded and a new allocation of days per employee is generated also when an employees status is change how to deal with that. When an employee used all of there sick days is there a way to have all those names display when you open the database.
View 14 Replies
View Related
Nov 29, 2005
Hi there, thanks in advance for any help or input with this. I am working with tables that have annual data on insurance policies that are effective for 1 year. I have Policy number (PolicyNum), current premium (CurrentPrem), the date the policy went into effect (PolicyEffDate), and cancellation (CXDate, which is 1/1/2001 if the policy was not cancelled). I am trying to earn out the CurrentPrem into monthly buckets. The tables are by the effective year going back to 2004, so basically 2004, 2005, 2006.
I'm not sure how to go about tackling this and have tried a number of different ways. I sense that I will need a query that will have 24 fields for all the months over a two year period that any given 1 year policy can be effective over. For instance, a policy created on 5/1/2004 (May 2004) is effective until 4/31/2005 (April 2005), and a policy created on 12/31/2004 is effective until 12/30/2005.
So for every month between January 2004 and December 2005, there needs to be a value generated (either zero or a monthly premium value).
Attached is a sample table with data. Really all I've been able to do is calculate how long a given policy is in effect for (how many months) and then how much the monthly premium is. I cannot figure out how to appropriate the monthly premiums to the corresponding months a policy is effective for.
Note that the current premium value takes into account cancellation which implies that the monthly premium for a cancelled policy is not 1/12 * [CurrentPrem] but 1/[MonthsInEffect] * [CurrentPrem].
View 1 Replies
View Related
May 18, 2014
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
View 5 Replies
View Related
Sep 14, 2013
Trying to modify this calendar database to track employee leave. I need the calendar form (frmCalendar) to show all employee leave on the calendar using the "Show All" command button on click event. This works if I specify a uID (UserID from employee table) in the code, but only for that specific employee. I need all employees at the same time up on the calendar so I can see if there is more than one employee off on any given day.
Events or leave is populated on the form through "Private Sub DisplayEvents" and the mdlCalendar module.
Using Access 2010
View 2 Replies
View Related
Jan 4, 2006
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, ""
FROM Table1
UNION SELECT Table2.Column1, "", Table2.Column3
FROM Table2;
Results
NewTable
Column1 Column2 Column3
A...............................3.1.......
A...............................3.2.......
A...............................3.3.......
A...............................3.4.......
A...............2.1.......................
A...............2.2.......................
A...............2.3.......................
A...............2.4.......................
View 14 Replies
View Related
Jan 30, 2013
I have a form with an image embedded in it. I want to be able to click on the image and leave a "dot" or "mark" where i have clicked, allowing me to click in multiple places on the image and leave multiple "dots"
I am assuming i need to crate a new shape each time i click, how is this done?
View 3 Replies
View Related
Mar 20, 2013
I am creating payment database for a community gym, the data will be created on a data sheet.
There are 2 payment types monthly and annual (these are on a dropdown box with their own id number).
I have a joining date and a paid date, but reflected against the payment type ie 31 days or 365 days.
I have been trying to do something along the lines of:
if [monthly] (is selected) (so this would call up a 31days) date() is > [joining date] then give message to renew and same kinda thing for annual.
View 1 Replies
View Related
Sep 27, 2005
Hi all,
I am not real sure whether or not this can be done with a query, but here goes.
I have a query randomly selecting the top 6 from a selected group.
SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name]
FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID
WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0)
ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);
What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.
I would greatly appreciate any thoughts or help…
Thanks Enviva
View 1 Replies
View Related
Jan 9, 2014
I need to do the price in table [price] multiply by 1.20 if the price is higher then 150. If the price is between 75 and 150 it have to multiply by 1.25. Continue... continue... continue...
expr1: IIF([Price]>"150",[Price]*1.20,IIF([Price]>75,"[Price]*1.25",IIF([Price]>50,"[Price]*1.28",IIF([Price]>30,"[Price]*1.35",IIF([Price]>15,"[Price]*1.45",IIF([Price]>0,"[Price]*1.6"))))))
View 3 Replies
View Related
Jun 20, 2007
The records that I'm working with are pulled weekly from another db, which has been modified by a boatload of folks, over the years. Every one of them had their little ways of doing things.
The data in "Product" field isn't always entered the same way. For example:
SS_11_0101__Z2 and SS 11__0101_Z2 and SS 11 0101 Z2 are all the same product, just apparently selected from different drop down boxes which (of course) are usually slightly different, usually a problem with the underscores.
This is causing me to miss some listings when I run my queries.
Fixing the main db is NOT an option.
What I need to do is figure out how to find any "_" (underscores) and replace them with a single space, in my db.
I run some searches here, but haven't found anything that triggered an "AHA!" moment.
Anyone have any recommendations about how to tackle this?
Update query, Replace() function or maybe a macro?
As always, thanks in advance for any suggestions you may have.
BeckieO
View 7 Replies
View Related
Nov 23, 2006
I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.
I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.
I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.
Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.
Why would one query return the value and the other not???
View 4 Replies
View Related
Feb 14, 2007
I have a problem where I can create queries in code using functions such as Left() and they will work fine on my clients machines with a complied MDE file but if I try to use the same function in a saved querie they get an error: "Function is not available in expressions in query expression..."
The Queries work fine on my machine but not on those using Access Runtime. From my research it appears to be a problem with them not having the correct Reference on their machine. If that is true then which Reference do they need and is there away of installing that Reference by code?
Thanks for your help....
View 1 Replies
View Related
Jan 13, 2014
I read a lot about retrieving the LAST generated Identity from an SQL-server table. Everythings seems to work, but I get a wrong result.It seems that SELECT SCOPE_Identity does not work with access when working with such a code for example:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; SELECT SCOPE_IDENTITY() AS TestID; "
Call SQL_PassThrough(strsql,"myTest_PT")
2nd parameter is an existing PT-Query with the connection and return values=yes. If no 2nd para then there is no resultset, only the insert.
So I changed it to:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; "
Call SQL_PassThrough(strsql)
Set rs = CurrentDb.OpenRecordset("select @@Identity as TestID from tblTest")
lastID = rs("TestID")
This brings me to my big surprise an Identity from ANOTHER (!!) table, but not the last one from tbltest. The code is running for test reasons in another modul and not in the one I creating the received ID.
As I have to get for sure the last ID from tblTest I cannot work with DLookup, as in my multisuer App this is not sure enough.build a construction where I get the last ID from the table where I just made my insert.
View 2 Replies
View Related
Jun 14, 2013
I am trying to get an IIf statement to work, but I am not quite sure how I can lay it out properly so that it works.So this is what I need
(IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]), (THATS THE CONDITION)
[SAON_START_NUM] & " ", (THATS THE TRUE PART)
(IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","") & (IIf(Not IsNull([SAON_START_NUM]),[SAON_START_NUM],"") & IIf(Not IsNull([SAON_START_SUFFIX]),[SAON_START_SUFFIX] & " ") & IIf(Not IsNull([SAON_END_NUM])," - " & [SAON_END_NUM] & " ","") & IIf(Not IsNull([SAON_END_SUFFIX]),[SAON_END_SUFFIX] & " ","") & IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM])," ","") & iif(NOT IsNull([PAON_START_NUM])," " & [PAON_TEXT],"")
(ALL OF THIS THE FALSE PART)
I know the syntax for the FALSE PART is wrong how can I make the whole statement to work?
View 5 Replies
View Related
Aug 29, 2013
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
View 9 Replies
View Related
