Count For Week Ending.. In Different Fields
Jan 15, 2007
Morning All!
Boss-Man has a db (humongous) that (along with a zillion other things) tracks which specific tasks have been completed, who has done it and when did they do it.
All tasks will eventually be completed by all employees.
He's got a query that returns all of the dates on which each task has been completed, using each TASK name as a field.
The short version below shows the TASK1 was completed on 1/1/07, 2/7/06 and 2/17/07, TASK2 was completed on 01/05/07,by 2 people on 2/15/07 and again on 02/28/07 and so on.
TASK1***** TASK 2***** TASK 3 ***** TASK 4
1/1/2007 *** 1/5/2007*** 1/13/2007 *** 1/2/2007
2/7/2006 *** 2/15/2007 *** 1/7/2006 *** 1/15/2007
********** 2/15/2007
2/17/2007******
********** 2/28/2007 ********* 2/28/2006
What he would like to do is be able to show how many tasks were completed in any given week, such as:
WEEK ENDING # TASKS
01/06/07 3
01/13/07 1
02/17/07 2
Looks to me like you'd first have to identify the possible WeekEnding dates and then try to total across all the fields, but I've not got a clue how you'd do that.
Is there an easy way to "query the query", work with Week Ending on Saturday, and get the info he's looking for?
Thanks,
BeckieO
View Replies
ADVERTISEMENT
Dec 26, 2013
Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.
DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).
So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.
Office1 5
Office2 1
Office2 1
Office2 1
Office2 1
Office2 1
Office3 5
View 2 Replies
View Related
Jul 18, 2014
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.
View 1 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Jan 24, 2014
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
View 2 Replies
View Related
Jul 17, 2013
I've got three fields - date_time, # of issues, issue reasons
I want to summarize these by work week.
So,
WW....... # of issues ...............................issue reasons
1 ..........<sum of all issues for the week>..list of all reasons entered
2 ..........<sum of all issues for the week>..list of all reasons entered
3 ..........<sum of all issues for the week>..list of all reasons entered
4 ..........<sum of all issues for the week>..list of all reasons entered
5 ..........<sum of all issues for the week>..list of all reasons entered
I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.
View 1 Replies
View Related
Feb 7, 2013
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:
StartDate = Jan30
End Date = Jun3
Jan1 thr Jan29 Cost = 0
Jan30 Jun3 Cost = 500 (average daily burn rate)
Jun4 thr Jun30 Cost = 0
... and the staff receives and update to the daily spread. The Cost Spread between Jan1 to Jun30 NOW looks like this:
StartDate = Jan15
End Date = Jun3
Jan1 thr Jan14 Cost = 0
Jan15 thr Mar29 Cost = 1000
Mar30 thr Jun2 Cost = 3000
Jun3 thr Jun3 Cost = 500
Jun4 thr Jun30 Cost = 0
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)?
View 1 Replies
View Related
Feb 11, 2015
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
View 8 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Dec 29, 2004
Can I let a Session variable end when all the pages are closed?
View 1 Replies
View Related
Jul 16, 2007
Hello Everyone,
I would like to run one query that can accommodate entering twelve possible date ranges and output the data all in one query. I could accomplish that by writing 12 queries and attaching each one to a start and end box on a form and then doing a Union Query to obtain one result. I think this might be the long way to accomplish this.
Is there a better way to get the result on one query for multiple Start and end Date Ranges?
Thanks
Joe:D
View 1 Replies
View Related
Jun 27, 2005
Hi,
I am creating a small call logging database and I would like the user to receive a message 1minute before the call is due to end.
I was thinking something like:
if [callend] =now() then msgbox "Call Due To End"
Can anyone help?
Many thanks
Charlie
PS, I have searched, but to no avail as yet!!!!
View 3 Replies
View Related
Jan 15, 2006
Hello - I have a database that is almost finished but unsure on the final calculations and how to get the correct data to flow to the next payperiod.
Each payperiod someone enters all staff members hourly time, vacation time used, sick time used, etc... Depending on the staff members status (fulltime or parttime), they accumulate a certain amount of sick and vacation hours but can never exceed the maximum in any given payperiod.
I have a query (which is dependent upon another query) that calculates the beginning sick time, sick time used, sick time earned, and gives me the remaining sick time and it works fine for the first payperiod.
However, I do not understand how I can get the remaining sick time to become the beginning sick time for the next payperiod. Is this done in the same query? A different query? A different method?
I have been beating my head against the wall for several days. Any guidance would be greatly appreciated!
View 4 Replies
View Related
Mar 14, 2005
I know i have read this before, and i have been searching for a hour now.
can someone tell me how set a variable = the beginning date ofthe month and another variable2 = the ending date of the month?
Thanks for your help!
View 1 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
Sep 24, 2013
I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.
My Requirement :
The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.
But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.
For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?
View 6 Replies
View Related
May 26, 2015
I'm currently looking to change the ending of all filepaths of imported files whenever I run the import. I want to change the path ending from .txt to .doc. So basically, the link/path to the file should be got, the ending changed from .txt to .doc and then the path (with the .doc ending) put into the table.
Code:
Private Sub bimportinternal_Click()
On Error Resume Next
strFolderPath = "S:Foo reportsSearchable"
strFolderPathSave = "S:Foo reportsSearchableArchiveword" & objF1.Name
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
[code]...
View 4 Replies
View Related
Jul 24, 2014
how to generate a Report through "Form".I should be able to input the date ex: text box1= Start Date and text box2 should be End Date, by Clicking a button ...i.e Generate Report Button, a report should generate should be generated showing data in between the Start Date and End Date.
View 3 Replies
View Related
Oct 30, 2012
I have a text box with the following statement:
Summary detail: Sum(IIf([dacthc]=0,Null,Fix(100*[dqty]*([dstdhc]*[drate]/[dacthc]-[drate]))/100))
My question is, if it meets the criteria, is it possible to make it default to a "0" in the field.
View 1 Replies
View Related
Jun 5, 2006
I'm building a health data system that stores data from health tests as follows:
[test01], [test02], [test03],.........[test25], etc etc.
Each of these tests comprised of a "Pass"/"Fail" value.
Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].
So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?
ie count([test01] [test02] [test03] [etc] [etc], ="Pass")
As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.
Many thanks in advance for any tips.
View 3 Replies
View Related
Oct 12, 2005
Hey,
I'm currently working on a database for my school project.
Currently, I have a table:
Job:
Contractor_ID [Foreign Key]
...
Contractors:
Contractor_ID [Primary Key]
Contractor [Text]
Basically I want to create a query that shows a table like so:
Wages:
Contractor [Contractors.Contractor]
Jobs_done [counts the number of rows from Job that has the Contractor_ID equal to the Contractor_ID specific to Contractor (above)]
Eg.
Job:
Contractor_ID
1
2
3
2
1
4
Contractors:
Contractor_ID Contractor
1 Bob
2 Bill
3 Jim
4 Jill
Wages:
Contractor Jobs_done
Bob 2
Bill 2
Jim 1
Jill 1
View 1 Replies
View Related
Jun 26, 2006
hi there,
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Thanks
View 2 Replies
View Related
Jun 26, 2006
hi there,
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Thanks
View 3 Replies
View Related
Jul 25, 2006
Hi there. Im trying to work out how I can count the dates in my database per month. What ive done is created an unbound box with an sql statement
SELECT Count([tbl Main].[Date of Call]) AS [CountOfDate of Call]
FROM [tbl Main]
HAVING (((Count([tbl Main].[Date of Call])) Between #4/1/2006# And #4/6/2006#));
When I select the totals button to count the records 0 are pulled out.
Does anyone know if im doing something wrong here. As always im sure its quite simple..
Cheers
View 7 Replies
View Related
Oct 14, 2007
Hey all,
I'm having a hard time getting the output below from a query and was wondering if anyone would be able to reproduce this. Basically I'm trying to retrieve products that have more than 5 sales on a specific date and the retailers they sold at. I have the following simplified table.
Table name: SALES
PRODUCT RETAILER SALESDATE
---------------------------------------
PRODUCT1 COMPANY1 10/10/07
PRODUCT1 COMPANY2 10/10/07
PRODUCT2 COMPANY1 10/10/07
I am trying to find which products had more than two sales on the same date, and retrieve the product name, the retailer and the sales
I.E THE ABOVE TABLE WOULD PRODUCE
PRODUCT SALESDATE RETAILER
--------------------------------------
PRODUCT1 10/10/07 COMPANY1
PRODUCT1 10/10/07 COMPANY2
I've been able to only get the first two fields successfully by using
SELECT PRODUCT, SALESDATE
FROM SALES
GROUP BY SALESDATE, PRODUCT
HAVING (Count(PRODUCT)>=2);
MY problem is getting the retailers to appear also. Any help would be appreciated.
View 3 Replies
View Related
Feb 1, 2006
How do you sort on a count field in a report
View 4 Replies
View Related
