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;
I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.
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:
How do you update a table by reducing a number by 10?
My assignment question is:
10 students have left GY101. Write an SQL UPDATE statement to reduce the class size by 10 for all modules taken by GY101 students.
I can display the students who take GY101 with the following code
SELECT moduleCode, classSize FROM ROOM_BOOKING1 WHERE moduleCode IN (SELECT modCode FROM STUDENT_REG1 WHERE sID IN (SELECT id FROM STUDENT WHERE courseCode = "GY101"));
I am building a report and need to know the coding in the query (or in the report) to achieve the following.
Currently, in my query - 3 fields that work fine:
AppDate - dates of set appointments (Appt)
MonthNo: DatePart("m",[App_Date]) - get Month value
YearNo: DatePart("yyyy",[App_Date]) - get Year value
What I need is : EXACT coding to get these counts to show in my report:
January 2013 12 appointment (there were a count of 12 appts in January) over 23 days (there were 23 days where appts were booked in January) of 31 days (total number of days in the month of January)
February 2013 18 appts (there were a count of 12 appts in February ) over 19 days (there were 23 days where appts were booked in January) of 28 days (total number of days in the month of February )
I'm trying to calculate the number of days between two dates using the iif statement
Fields: [LDW] "Last Day Worked" [ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure], Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
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)
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.
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.
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
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.  Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
The client db is not Normalized. I understand their requirements and the current table structure has to remain in tact. The DATA ENTRY staff need to enter cost for Jan1 to Jun30. One of the many calculations that needs to be updated daily is the SUMPRODUCT from STARTDATE to ENDDATE - which can be anywhere between Jan1 to Jun30 at any time. The example scenario below needs to calculate the sumproduct of COST between any two fields (days of week).
For example, let's say a Purchase Order has been created, and the initial entry looks like this:
Here is the code I am using to calc the SumProduct upon initial entry WHERE the 'average daily burn rate' for the entire spread is the same every day (FYI, we need to calculate the YearTo-Date (YTD) and Remaining Balance from YTD for both Commitment and Incurred cost. For this example I am only snipping the code here to reflect Incurred) 'Initial Calculations
If CurrentDate <= frmStartDate Then SUMPRODUCTYTDIncurred = ZeroValue Else SUMPRODUCTYTDIncurred = Round((DailyBurnRate + DailyBurnRate * NoDays), 2) End If If CurrentDate <= frmStartDate Then SUMPRODUCTRemainingIncurred = ZeroValue Else SUMPRODUCTRemainingIncurred = Round(DailyBurnRate + DailyBurnRate * (DateDiff("d", CurrentDate, frmEndDate)), 2) End If
In my UPDATE SCRIPT (not shown - cause its TMI):
- I am using the For Each statement to search an ARRAY to filter for the db.TableDefs("MYTABLE").Fields I need to update. - I am also using a Do While True statement to catch the cost for the found field.
How to catch all the cost between Jan1 to Jun30 before updating the SUMPRODUCT field and then .MoveNext until EOF (remembering that the For Each statement will stop when field Jun30 is found)?
could someone please help me. ive tried all sorts of things and now im loosing the will to live. under the data protection act i have 40 days to produce requested files in a hospital. the problem is that my little database that has been working away for 2 years is not capable of flagging up any files that are gone over the limit.
i need to run a query that will bring up all files not completed in the past 40 days. or reaching the forty day limit.
ive tried <now()-40 or <date()-40 in the critieria of the date rquested field. ive also tried kicking the machine when my boss isnt looking. that didnt work either.
I created a database for an organization I volunteer for but I am running into design problems. I want the database to be able to assign preferred days of the week (that the volunteers want to serve) with the flexibility to change them.
The problem is in order to be able to keep my reporting options flexible. I will have to write a record (on a separate table) for each volunteer for every day they want to serve. A one to many relationship between the volunteer table and preferred days table.