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 Replies


ADVERTISEMENT

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 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 10 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

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

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

Recording Visits To A Form

May 31, 2005

Is there a popular way to show when records are updated, with something like a text box with Now(), please, Gurus?

It needs to be automatically enacted. (A box that they complete themselves gets quickly forgotten.)

Then if the Client refers to records changed on such and such a date, I can find them instantly.

View 10 Replies View Related

Outstanding Visits...(Help With Report)

Jul 17, 2006

Oh my gosh, how frustrating! I'M TRYING (so desperatly) to create a report that will show me how many outstanding inspection visits to a business. I don't know if i'm going in the right direction but, i've created the report with a date range form. the main purpose for this report is to show me how many visits I'm behind by typing in a date range. For example i want to know how many outstanding i have between 1/1/05 to 1/1/06. Visits are done annually. so i need for it to pull up the ones that weren't done. it's complexed for me to elaborate on it so i've attached it..

View 4 Replies View Related

Tables :: Database For Recording Visits - File Number For Each Day

Dec 17, 2014

l have a database for recording patients visits in a hospital,in addition for "serial" field I want to make "fileNumber" field which represents a continous serial number changes everyday but of course all visits in the same day have the same filenumber as in follows..

Ser-----Date----------FileNum
===========================
1-------17.12.2014------1
2-------17.12.2014------1
3-------17.12.2014------1

1-------18.12.2014------2
2-------18.12.2014------2
3-------18.12.2014------2

I cannot depend on date (I think) in the desired code because I want to skip weekend and holidays so FileNum continues after weekend (Friday) from the last number as follows..

DayOfWork----FileNum
===================
Sat---------------1
Sun--------------2
Mon--------------3
Tue--------------4
Wed--------------5
Thr---------------6
Fri---------------off
Sat---------------7
Sun---------------8

View 14 Replies View Related

Huge Report With Visits Per Customer - Get Information Of Last Visit

Dec 2, 2013

We have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.

I want to create a querry that exports a table with 3 columns:

1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit

I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.

How to handle this in MS access 2010?

View 2 Replies View Related

Queries :: Huge Report With Visits Per Customer - Grab Information Of Last Visit?

Dec 2, 2013

we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.

I want to create a querry that exports a table with 3 columns:

1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit

I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.

how to handle this in MS access 2010?

View 9 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

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

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

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

Using Previous Month's "closing" Value For This Month's "opening"?

Jan 3, 2006

Hello,

I've been looking through the archives and learnt a fair amount about date manipulation, but am still a bit stuck. I'm sure there is a way to do this and would appreciate any ideas:

Basically I am monitoring changes in Fuel level over months. For each month, I have a variety of readings, each on a different table, including sales from that tank, delivery in to it, and most importantly a closing dip reading taken at the end of the month.

In order to figure out the change in Fuel level over each month, you need an "opening" figure : this is taken to be the closing dip value of the previous month. And this is where I'm getting stuck!

I am writing a query to generate new table which combines all the relevent values. I have managed to link them through the date field, even though the specific date is not identical what matters is that it is the same month. Effectively I am trying to add a new 'column' showing the opening value, where opening value = previous months closing value for this tank.

I noticed there is a post talking about previous month data <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=39708&highlight=previous+month">here</a>, which gives me some ideas but I'm not sure how to go about putting this into the SQL query. I can't use Date() because I'm interested in more than just previous to THIS month - the data spans back over a year!

I'm still learning Access so this might be a simple question - if so please point me to the relevent parts of the help file!

many thanks
Catherine

View 10 Replies View Related

Calculate A Value

Dec 16, 2004

How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?

How do i do this? Help me please :)

View 5 Replies View Related







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