Counting Occurances Of Months
Oct 9, 2006
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
View Replies
ADVERTISEMENT
Oct 9, 2006
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
View 14 Replies
View Related
Dec 21, 2004
Dear all:
I have created a form with a bound combo box with 3 selections: OFFICIAL COPY, STUDENT COPY and ADVISEMENT COPY.
From there I want to be able to keep a running total for each selection in separate textboxes.
Any ideas will be grealty appreciated.
Thanking in advance,
Dion
View 2 Replies
View Related
Dec 22, 2004
Dear all:
I need to count a specific expression in a text field in a table. I desire totals that says "copies" which is selected from a combo box. The total appears in a separate text box. Does anyone have any ideas on how I can count a non-number field?
Many thanks,
Dion
View 3 Replies
View Related
Feb 15, 2006
I have been through this board looking for an answer, I've come close but not quite getting it.:confused:
I (obviously) need to count how many records each user has completed (easy)
I need to count how many they did in a months time. I need to report multiple months in one report.
A little back ground on me...I only know the Query Design view and it's pretty limited. I have tried to move into the SQL view and alter the information there but it's not working in my favor. I also tried changing the format in the date properties to mm/yy but it's still sorting by the full date and time. :mad:
Any help would be awesome. Here's a copy of the SQL statement if it's easier to just change it there. Thanks in advance for the help.
SELECT Count(qry_QuotesIssued.Quote_Number) AS CountOfQuote_Number, qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date
FROM qry_QuotesIssued
GROUP BY qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date;
View 2 Replies
View Related
May 11, 2012
I have a query to calculate the monthly depreciation for a machine for specific period of time.
My problem is how to get the number of months for each period assuming i want to start from month September 2009 till may 2012.
Attached the data extracted by the query.
I want to calculate the depreciation to be grouped year by year in report.
[TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
View 3 Replies
View Related
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Jun 14, 2007
I am working through a lot of data and this is basically what I'm trying to do.
Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."
How would I do this?
View 2 Replies
View Related
Jan 21, 2006
Hi;
Summary: Using DCount in MS Access 97 to output the name of an event
and the total number of occurances per a user input date range.
e.g. the table is called EventData and it is a table of the types of car repair performed, and the fields are:
EventType Date Desciption other Time
======== ==== ============ ====
fix flat 17/01/06 comment.... 101pm
brakes 15/01/06 comment... 504pm
tuneup 02/01/06 comment... 725am
paint fender 03/01/06 comment.. 852am
My issue is to use a subquery for intermediate results and then another query.
I would like the user to enter the start and end dates, and then for output to be:
Output:
EventType Num Occurances
======== ============
fix flat 2
brakes 5
approach 1, use subquery called SummaryQuery:
SELECT EventData.EventType, EventData.Date
FROM EventData
WHERE (((EventData.Date) Between [Type the Start date:] And [Type the End Date:]));
This gives me output of all the events types in the date range and the time.
Then if I query this via:
SELECT SummaryQuery.EventType, DCount("*","SummaryQuery") AS TotalCount
FROM SummaryQuery
GROUP BY SummaryQuery.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
Here I am using DCount to get the num of ocurrances.. but it is not working.
I get the error that "Access cannot find the name "type the start date" "
approach 2, Use only one query:
SELECT EventData.EventType, DCount("*","EventData") AS TotalCount
FROM EventData
GROUP BY EventData.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
for this, I get no output, but the query runs.
Any comments or help with this is appreciated!!!!!!!!!!!!!!!
View 2 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies
View Related
Oct 29, 2007
Hello mates, :cool:
I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P
View 1 Replies
View Related
Sep 14, 2006
Hi,
I am trying to create a query that will show data in a rolling four month period. Example: For September, show June through September; for October, show July through October.
I am using a general date format in the field. I can use >DATE() - 120 to get a 120 day period, but that obviously does not give me data for the actual Calendar dates. Is there a better way?
Any and all suggestions will be greatly appreciated.
Thanks in advance,
Jake
View 2 Replies
View Related
Sep 15, 2006
Can this be done?
I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?
For example, total savings is $12k and effective date is 2/1/2006. I need to show $1k in each month field in my query from 2/1/2006 through the month of 1/1/2007. I've tried DateSerial and some other methods but I can only get it to give me the divided amount in the effective date column and nothing else. Is DateSerial the way to go?
Thanks,
Toni
View 2 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
View 2 Replies
View Related
Feb 27, 2007
How do I write an expression to pull data greater than six months from a user entered date.
View 3 Replies
View Related
Oct 18, 2007
The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.
I have done this before many years ago but now have a mental block and cant figure it out.
Any help with this would be greatly appreciated
View 3 Replies
View Related
Mar 27, 2005
Searched and can't find the specific answer to my age/month question so I hope you will bear with me.
on my form I need to calculate someones age and show it in years & months, then display the age (years & months) in the format 7.5 ( i.e. 7 years 6 months displays as 7.5 ).
I also need to round that figure down to the nearest 0.5 of a year so 7.4 would display as 7 and 7.9 would display as 7.5.
Any help would be very gratefully received.
Cheers
RussG
View 2 Replies
View Related
May 23, 2013
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
View 1 Replies
View Related
Aug 28, 2006
I need to (accurately) calculate a rental period to fractions of a month.
ie) 1/1/2006 to 2/14/2006 would equal 1.5 months.
DateDiff("m",[Start],[End]) returns whole months.
DateDiff("d",[Start],[End])/30 is very close , but not precise.
Is there a way to accurately calculate fractions of a month?
Thanks,
Sup
View 14 Replies
View Related
Jun 7, 2005
Good morning all,
I currently have the following expression in a query, to go back 6 months from the current date.
Between DateAdd("m",-6,Date()) And Date()
Does anyone know of a way from the current date, go back to the end of the previous month ( 30th, or 31st) then go back 6 months?
hope that made sense.
Thanks
Kevin
View 2 Replies
View Related
Jun 28, 2005
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
Bowes
View 4 Replies
View Related
Dec 12, 2005
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
View 1 Replies
View Related
Sep 30, 2006
I have a table which lists rentals, all have start/end dates and a rental fee p.a.
I want to query this table to show rentals that are effective for any part of 2 dates, [Enter Start Date] and [Enter End Date]. The purpose of this query is to calculate rental income between these 2 selected dates.
Now I have this much working, but I want to group all the figures by month, in order to produce a graph to view the rental income by months.... this bit has me stuck....:confused:
How can I group all these records, when I only have 2 dates... for eg, start 01-01-06 and end 31-07-06...
I want to see figures for Jan06, Feb06, Mar06...July06etc... rather than just a single total figure for the entire period...
Any ideas?
Help! I'm completely stuck here and have spent hours searching the internet but haven't found anything suitable...
View 10 Replies
View Related
Mar 19, 2007
Hey
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
View 14 Replies
View Related
Dec 3, 2007
I have been assigned a task to create a query that gets record from Access database by grouping it by year and then by months.
DATA ILLUSTRATION
date incidents
01/01/2007 2
01/02/2007 3
01/03/2007 4
01/01/2008 1
20/01/2008 1
01/02/2008 1
Based on the above data illustration, what I am trying to do is to get total by year and then break it down by the months
year 2007 total_incident: 9
Jan total_incident: 2
feb total_incident:3
mar total_incident: 4
year 2008 total_incident:3
jan total_incident:2
Feb total_incident: 1
I am using Microsoft Access, but trying to get the result into a web form. Many thanks in advance
View 2 Replies
View Related
Mar 12, 2008
Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.
Thanks for any help. :D
View 2 Replies
View Related
