Queries :: Counting Number Of IDs For Each Week - GroupBy
Oct 22, 2013
I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.
Code:
SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;
View Replies
ADVERTISEMENT
May 27, 2014
I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?
I have two fields - MobDate and DeMobDate
I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.
View 14 Replies
View Related
Feb 28, 2014
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1
All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.
How can I amend my query so that the above all show the correct year and week number: 2014_1 ?
View 9 Replies
View Related
Jan 10, 2014
I need to a query on the week number, the week number is in the table. Imjust struggling with getting the current weeks data.
View 10 Replies
View Related
Jan 23, 2014
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
View 4 Replies
View Related
Jul 11, 2013
Im trying to run the following calculated query:
Total Value on order: Sum([Purchase Price])
I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function
View 2 Replies
View Related
May 29, 2005
What I would like to do is get a count of the product sold and view it over a ten week period in this case week one starts 26th June.
Wk1, Wk2, Wk3 ....
x 10 5 15 etc
y 20 4 12
in the format above but I am not sure how to achieve this, I have the following:
SELECT COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description, Count(COURSEBK.[COURSE-DSName]) AS [CountOfCOURSE-DSName]
FROM COURSEBK
GROUP BY COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description
HAVING (((COURSEBK.From)>=#6/26/2005# And (COURSEBK.From)<=DateAdd("ww",+10,"26/06/05")))
ORDER BY COURSEBK.From;
(the above is working despite access changing the date format between the hashes)
regards in advance foir guidance
View 5 Replies
View Related
Sep 19, 2013
I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
View 7 Replies
View Related
Aug 11, 2015
I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.
The process goes: Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]
It then gives me the requested information.I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.
Can I simply add an additional line to the Macro?
View 7 Replies
View Related
Nov 7, 2013
I have an access dbase with a single table
I am interested in reporting so need totals, I can write a query that will count the number of machines in a dept like so
SELECT [Computer Inventory].Department, Count([Computer Inventory].ID) AS CountOfID
FROM [Computer Inventory]
GROUP BY [Computer Inventory].Department;
This works fine, however I now want to add more so are two fields
To_be_migrated
Is_migrated
These are just checkboxes so I assume criteria is true or false
I need a query which will give me
The total number of machines by dept
The total marked for migration
The total migrated
View 4 Replies
View Related
Nov 6, 2006
I have a query that groups records by date into week numbers using the following expression.
WeekNumber: DatePart("ww",[Date],2)
I would prefer to display the result as the week commencing Monday date.
How?
View 13 Replies
View Related
Oct 29, 2006
Hi,
I will really appreciate ur help. I work on Access-2000 database and have a problem. I need to get the number of the week. So i used the funktion as a standard value as following:
"=((Monat(Datum())*30,5-Tag(31-Datum()))/7)"
It shows me for today 39 instead of 44.
Could u please help me and tell me why this funktion doesnt work?
Thanks in advance
View 5 Replies
View Related
Jan 18, 2006
I have a large report that runs a six weeks average based on the last six weeks of production. Our fiscal year starts November 28th and our weeks starts on Monday.
I have used the following format to start the week on Monday
Week1: (DatePart("ww",[EntryDate],2))
To set up the fiscal year I used the following format in my query.
FiscalYear: IIf(Format([EntryDate],"mmdd")>"1127",DatePart("yyyy",[EntryDate])+1,DatePart("yyyy",[EntryDate])) . The criteria is 2006
I have a Weekending table that I have all the weeks listed including the weeks in calendar 2005 that are included in our fiscal 2006 that I have linked to my query.
When I run the report, it will not include weekending 1/1/06 (Week 1). It is totally eliminated. There is probably a conflict in the above formats, but I am not knowledgable enough in Access to figure it out. I have been searching this site without success. I would appreciate any help I can get. Last week I had to do the reports manually because I couldn't get the works to run right.
Thanks,
View 1 Replies
View Related
May 14, 2007
Hi Folks,
I use this to get a week number for a date range:
WeekNo: DatePart("ww",[Timestamp])
Is there a way that I could also get just the first date from that week number to appear in the query as well?
ie. if Week 1 is 3/1/07-3/7/07 the query would show 3/1/07 as well as week 1. TIA
View 1 Replies
View Related
Feb 8, 2007
hi
i am in a situvation that i have to identify the dates which comes under particular week1 or 14.
View 3 Replies
View Related
May 24, 2005
Hello everyone.
I would like to ask for help on my current problem.
I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.
for example:
May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1
May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2
May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3
May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4
May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5
Same goes for all months.
Is there a function to determine which week number of the current month the input date falls?
Please help.
Thanks,
Edwin
View 2 Replies
View Related
Aug 23, 2005
Hi,
I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.
How would I obtain the start date and the end date from just the week number?
Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.
Any help would be much appreciated.
View 2 Replies
View Related
Apr 10, 2008
hi
i would like to display the date and week number on one of my forms but have no idea about dates and how to code this.
can some one help? id appreciate it it beyond me :confused:
thanks :)
rob
View 4 Replies
View Related
May 17, 2005
Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.
View 5 Replies
View Related
Sep 29, 2005
Help!!
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??
View 1 Replies
View Related
Apr 24, 2008
I am trying to avoid the creation of a lookup table where I know I should be able to calculate this. I need to determine the start and end date of a week number. I did find this post from 2002: http://www.access-programmers.co.uk/forums/showthread.php?t=33116&highlight=start+end+dates+week but it doesn't seem to work.
What I am trying to do is show all projects that were opened at the start of a week number. For example, week 2 is from 1/7/2008 and ends on 1/13/2008 (provided that your week starts in the week that 1/1 occurs and that your week starts on Monday). I need this start date so I can show all projects that started on or before 1/7/2008 and were in an open status.
If I can't calculate this then I will have to create a table and relate the query to it.
|Week Number | Start Date | End Date |
View 3 Replies
View Related
Feb 3, 2005
Hi,
How can i do the following -
When a date is typed in a text box, ie, 17/01/05
I want a Query to return what Number week in the year it comes from.
In this example the result would be Week "3"
I'm sure this would be easy enough to do if i typed in every day in the year into a table with the
week number beside it.
Is there some code or a more straight forward way.
hope all makes sense, if not i can go into greater detail.
View 2 Replies
View Related
May 1, 2008
When using the Format:'yyyy mm dd' in access queries.
When trying to show a week is there a way to show the
date range instead of week number.
Week 01 = Jan1-7. If the results are only week numbers
that makes it hard for people to understand when it
actually is. Bottom line is: I would like to convert
Week number to actual date range. Can anyone help with
this?
View 9 Replies
View Related
Jan 6, 2005
Wondering if anyone can advise...
The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:
WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)
However my problem now is that I need to replicate this format in a Query within Access. The formula
WeekNo: Format(Date(),"ww")
treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?
There's a challenge for someone!! Help appreciated.
View 1 Replies
View Related
Apr 23, 2013
I have a summary form with the company name,week number, week-ending automatically populated. e.g. this week is week 4 and users enter records for week 4 as the week number is automatically generated. Basically the system will just recognise today's date and generate the week number. Problem is I am trying to display previous week records and I do not know how to filter it as my week number is generated automatically, week by week. All the other forms are linked to this week number. How can I display records for a specific week that I want? say, I want to display all records for week2, etc. I am dumbfounded as I am on a learning curve with Access.
View 2 Replies
View Related
Jan 5, 2007
Hi
I wanna do something similar to what Stephanie.D tried to do before..I read the suggestion she recieved but doesn't seem to work for me. I have one table with the following
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
I'd like to create a query that only returns the rows of the table where the Price of the Name is the smallest. Here's an example...
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
1 drinks wine Porto Store1 5
2 drinks wine Porto Store2 6
3 food bread Baguette Store3 1
4 food bread Integral Store3 2
5 drinks wine Bordeaux Store3 3
It should return
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
3 food bread Baguette Store3 1
5 drinks wine Bordeaux Store3 3
I tried doing a GroupBy but it only works when for the query I only use the SpecNameOfProd and Price fields. I tried using Where like it was suggested to Stephanie but I need the other fields to show...
Thx! for any help and suggestions
View 4 Replies
View Related