Calculate Variance Of Payment Month On Month - Values Comparison

Mar 19, 2012

I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.

I need to calculate variance of payments month on month and identify set of customers who have made huge payments.

View Replies


ADVERTISEMENT

Queries :: Comparison To Previous Month Or Week Depending On The Report

Dec 31, 2014

I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"

I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.

Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:

This month we did 250 units
Last month we did 300 units
so we did 15% less
"Yes my math is not exact"

They don't need to see last months data but i need a way to query the previous record to compare the data

This is the criteria code i use in the query that "Form B" is based on.

Code:
[Forms]![Form A]![Report_Start_Date]

I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.

How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.

View 3 Replies View Related

Calculate Visits Per Month

Jun 28, 2005

Hi All.
I need to calculate how many visits per month. What formula in a query I need to use to calculate the same value of DATE field.
Thanks.

View 5 Replies View Related

Calculate Visits Per Month

Jun 28, 2005

Hi All.
I need to calculate how many visits per month. What formula in a query I need to use to calculate the same value of DATE field.
Thanks.

View 1 Replies View Related

General :: How To Calculate Next Month

Aug 27, 2013

I have an birthday report for the current month (august) and would like to make a button on the report that can calculate next month (september) and shows birthdays for that month. how to calculate next month?

View 7 Replies View Related

Calculate First Day Of Next Month In Following Year?

Jul 30, 2012

I have tried to do this using DateAdd but I cant get it to add both the month and year.

I have a date field [HireDate] and I need to add one year then get then get the first day of the next month.

For example: HireDate = 1/15/2012 - I need access to return 2/1/2013.

how to build that expression?

View 4 Replies View Related

Calculate An Empty Month In A Few Months

Dec 12, 2007

Sorry the title dosen't make much sense, can't come up with a good title. Here's the problem, I work in a hospital and I have a program that store patient's admit date and discharge date. My concern is, I need to calcuate patient days (the amount of days the patient stay in the hospital). Let's say a patient was admitted on Jan 15th and discharged on March 20th. So I need to build a query that capture the months, let's say I do it on Feb, how can I tell that this patient was here on Feb? since I have admit date and discharge date. Thanks


eg. we have 4 patients John Smith, Ortiz Prerez, Jim Johnson and Nancy Cruz and we are running the query for Feb.

ID Name AdmitDate Discharge Date "The result should be"
1 John Smith 1/2/07 1/26/07 24 patient days
2 Ortiz Perez 1/27/07 3/4/07 28 patient days (because this user was in the unit)
3 Jim Johnson 12/25/06 Null 28 patient days because of Feb month
4 Nancy Cruz 2/14/07 4/1/07 14 patient days

View 10 Replies View Related

Queries :: Take Current Date And Calculate Last Day Of Last Month?

May 16, 2013

Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?

View 3 Replies View Related

Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

May 5, 2014

I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?

Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.

View 2 Replies View Related

Tables :: DCount - Calculate ID For Month And Year From Another Table

Sep 25, 2014

I have a table that displays the year and month and another field that I want to calculate how many id have that month and year from another table.

View 3 Replies View Related

Modules & VBA :: Calculate Next Service Month Based On Dates

Jan 16, 2015

I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.

For example:

Start date: 01/01/2014
End date: 31/12/2015

service frequency: Every 4 months

Using the above information the service months are:
1 - April 2014
2 - August 2014
3 - December 2014
4 - April 2015
5 - August 2015
6 - December 2015

View 6 Replies View Related

Queries :: Calculate Fiscal Year And Month From A Date

Jul 13, 2015

I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:

The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.

In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.

Code:
UPDATE tblSAPOD
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]<
DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";

.. but I guess it's wrong, it gives me a syntax error.

View 11 Replies View Related

Queries :: Calculate Profit Made By Type / Month And Year

Apr 22, 2013

I want to make a query to figure out profit made by month year and room type.

View 1 Replies View Related

Queries :: Database For College - Calculate Total Of Lessons Taken In A Month

Jan 12, 2015

I'm currently making a database for college about a driving school.

I have a table that stores the following: booking id, student id, lesson id, student forename, student surname, cost per hour and date paid.

How to create a query that calculates the total of the lessons taken in a month.

View 4 Replies View Related

Matching Current Month With Month In A Table

Oct 18, 2005

I have a table full of dates of meetings through out the year...

Example

tMeetingDates

16th August 2005
18th September 2005
19th October 2005
23rd November


-----

i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.

I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.

i then want this date to be displayed in the Report.

So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...

Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...

we only ever have one meeting a month!!!!

Please help

Andy

View 11 Replies View Related

Forms :: Calendar Scrolling Month By Month

Aug 9, 2013

Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?

View 6 Replies View Related

General :: Calculate Deposit Amount For Current Month - DSUM With Multiple Criteria

Mar 23, 2013

I am trying to get deposit amount for the current month but results are in accurate i am using this

DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))

View 3 Replies View Related

Queries :: Include Date Criteria In User Defined Function That Calculate End Of Current Month

Jul 22, 2014

I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().

The field on which this function is to enter as a criteria is another calculated date function called Due.

When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.

View 3 Replies View Related

Date Values For This Month

May 1, 2007

This thing is driving me nuts and is possibly something simple, but can't figure it out.

I have a query which gives me data in three fields : Vendorname, vendorlocation and surveydate
The first two are text fields, the last is a date field.
What I want is those names and locations, where the surveydate is in THIS month only. So today's month (5) is May and I want all entries for this month only.

How would I do that .

View 8 Replies View Related

Month String To Numerical Month

Oct 13, 2006

HiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter

View 5 Replies View Related

Calculating Daily Values For A Month

Jul 7, 2006

Hello all. I hope someone can lend a helping hand here. I am trying to create a query that will calculate a 24 hour value for every day of a month. For a month a go out a read meters. I need to get the difference of two readings by subtracting the first day from the second day and so on until I get to the end of the month. So, Day2-Day1=24 hour value, Day3-Day2, Day4- Day3... I have this in a spreadsheet but can't seem to grasp it for a query in a database. I would appreciate any help. Thanks.:confused: :D

View 1 Replies View Related

Replacing Date Values For Last Friday In Month

Apr 30, 2008

Is it possible to create the following logic in query:

"If there is a "SHIFT2" or "SHIFT3" value on the last Friday in month,
then change the date values for these entries to the next date (Saturday)"

In the attached sample, there is a number of such values within the "Adv Track Shift" field dated on Friday 04/25/08 in the "shift_date" field. The logic should replace the applicable dates to Saturday 04/26/08. This should work for any month, regardless if it finds either of the two shifts or both of them.

View 4 Replies View Related

Forms :: Entering Values For Multiple People On One Form Based On Month

Jan 31, 2015

I need to enter workload counts for 10 people, and it is done on a monthly basis. So I have a table of Months (Jan-Dec), a table of names, and a joined table with the months, names and a field for the workload counts.

I would like to make a form where I could select the month and all the names show up so I could go and enter the counts for everyone at the same time. I've attached a diagram to show what it would look like

View 3 Replies View Related

Month By Month Query

May 17, 2005

please help me

i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..

any ideas please

View 2 Replies View Related

Modules & VBA :: Monthly Payment - Create A Worksheet Which Calculate Balance

Mar 3, 2014

I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.

Monthly Payment Period: 1 to 300

Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.

Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12

Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.

The final Balance Remaining should be exactly £0.00.

View 1 Replies View Related

Forms :: Worksheet That Calculate Balance Remaining At The End Of Each Monthly Payment Period

Mar 3, 2014

I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.

Monthly Payment Period: 1 to 300

Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.

Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12

Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.

The final Balance Remaining should be exactly £0.00.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved