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?
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.
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.
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
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.
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)
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"
: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?
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].
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.
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"
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.
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.
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.
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.
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
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"
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.
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"))))
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.
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:
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?
