Queries :: Projecting Annual Turnover From Given Date
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 Replies
ADVERTISEMENT
Apr 10, 2013
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
View 5 Replies
View Related
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 14 Replies
View Related
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
Mar 10, 2015
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..
employee name
joining date
department
section
employee left (Yes/No)
Left date
Now I am looking for;
i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start
ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)
iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise
iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)
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
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
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
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Jul 11, 2014
I currently have a query set up which responds and filters by the date.
I use the current code in the criteria on the query for todays date less 7 days
Code:
DateAdd("d",-7,Date())
This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....
How would I go about doing that?
View 4 Replies
View Related
May 7, 2013
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
View 3 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Apr 20, 2015
I've two fields to work with:
[Date of Device]
[DischDate]
If i was explaining it, it would be as follows:
If [DischDate] Is in the next month after [Date of Device] then Y else N.
to add for example if the [Date of Device] is April 2015, and the [DischDate] is also April then i'd expect a N answer
to add for example if the [Date of Device] is April 2015, and the [DischDate] is May then i'd expect a Y answer
View 2 Replies
View Related
Aug 8, 2013
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies
View Related
Nov 5, 2013
The query is there to query data, not to edit data or manipulate data in anyway. However, I am disgusted by the appearance of the date picker when I pick a field that is a date field.
Is there a way to hide/remove these date pickers from the query sheet?
Within the table itself I turned the "Show date picker" property to "Never" but that didn't make a difference.
View 2 Replies
View Related
Mar 26, 2013
I've attached a stripped down version of a small order database I'm working on.
A user would enter an order, the amount and the date the order is required by.
As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.
What I'm trying to build are two queries:
1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)
2. A totals query which shows the total order amounts by season
how I might build these 2 queries.
View 2 Replies
View Related
Feb 24, 2015
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
[Code] .....
View 9 Replies
View Related
Feb 23, 2015
How can you export cross tab queries by using date parameters (for example: Jan 1, 2014 to December 31, 2014)...
View 3 Replies
View Related
Jun 19, 2015
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
View 11 Replies
View Related
Mar 12, 2015
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
View 10 Replies
View Related
Mar 13, 2014
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
View 4 Replies
View Related
Mar 17, 2015
I have a table, tblVisits, holding patient's pre and post surgery visits:
Code:
PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...
And another table holding patient's surgeries (each patient will have only one surgery)
Code:
PatientID SurgeryDate
1 4/1/12
2 ...
I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:
Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...
I've tried various MIN and MAX approaches and can't seem to get it right.
View 2 Replies
View Related
Oct 8, 2013
I have a query where the intent is to return the most recent Statement Closing Date by Account Identifier.This works great
SELECT [ME - Balances and Activity at Cycle End_Updated].[Principal Bank Identifier], [ME - Balances and Activity at Cycle End_Updated].[Account Identifier], [ME - Balances and Activity at Cycle End_Updated].[Member Number], Max([ME - Balances and Activity at Cycle End_Updated].[Statement Closing Date]) AS [MaxOfStatement Closing Date]
FROM [ME - Balances and Activity at Cycle End_Updated]
GROUP BY [ME - Balances and Activity at Cycle End_Updated].[Principal Bank Identifier], [ME - Balances and Activity at Cycle End_Updated].[Account Identifier], [ME - Balances and Activity at Cycle End_Updated].[Member Number];
However, when I introduce variable data, such as a balance field where the balance is different every Statement Closing Date, I get everything.
My question is how do I get my query to return just the most recent Statement Closing Date, regardless of the variable data?
View 2 Replies
View Related
