Queries :: Summarize Three Fields By Work Week

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 Replies


ADVERTISEMENT

How To Summarize Fields Of Table

Oct 28, 2011

How to summarize the fields of the table this way? See the attached screen shot that show my question

I wanted to summarize the data according the values of Field1 and to get the values of Field1 and Field2 summarized separating the values with comma. How can i do that?

View 14 Replies View Related

Queries :: Trying To Summarize 2 Tables In Query

Jul 27, 2015

I'm trying to combine/summarize two tables in a query. What I've found so far is that I have to use a union query. I tried that but all I get is punctuation error.

So here is what I'm trying to do:

Code:
tblProdA
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 10
02 | 1/3/2015 | 10
03 | 1/10/2015 | 20
04 | 1/13/2015 | 20
05 | 1/17/2015 | 30
------------------------------------

tblProd2
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 5
02 | 1/5/2015 | 5
03 | 1/10/2015 | 10
04 | 1/13/2015 | 10
05 | 1/15/2015 | 10
06 | 1/17/2015 | 5
------------------------------------

qrySummary
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 15
02 | 1/3/2015 | 10
03 | 1/5/2015 | 5
04 | 1/10/2015 | 30
05 | 1/13/2015 | 10
06 | 1/15/2015 | 10
07 | 1/17/2015 | 35
------------------------------------

And I uploaded the sample as well.

View 5 Replies View Related

Queries :: Summarize Income And Expenses Over Periods

Apr 1, 2013

I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods

This is how it should look like.

Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc

I made the following query to get the result I wanted.

Unfortunately, the result seems to be multiplied instead of summarized.

View 3 Replies View Related

Need Help In Work Week

May 2, 2007

i would like to querry a date which only showing work week and year... i try in FORMAT([MYDATE], 'WW YYYY', 1, 2)... it work but when go to 31/12/2006 it showing "01 2006"... but suppose it should show "01 2007" how can i overcome this?

View 2 Replies View Related

Work Week Calculations

Jun 12, 2007

Hello All,

I need alittle help in finding out the difference between 2 dates within a work week (meaning Mon-Fri also excluding holidays) It would be simular to the Networkdays formula in excel. I was able to calculate the difference between 2 days in the query by using:

Expr1: DateDiff("d",[Act Del],[Orig Entry Date])

However, I need to exclude Weekends and Holidays. Is this possible?

Thanks

View 4 Replies View Related

Record Missing Using Work Week Query

Feb 25, 2007

I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

View 14 Replies View Related

General :: Assign Preferred Days Of Week When Volunteer Want To Work

Jul 26, 2014

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.

View 5 Replies View Related

Queries :: Getting A Query On Multiple Checkbox Fields To Work With A Date Range?

Nov 7, 2014

I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.

I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.

An example of the parameters used for one of the checkbox fields in Query Builder is as follows:

Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked

This works fine.

My latest parameters for the date range are this:

Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]

This gives a statement in SQL view of:

SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));

what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.

View 1 Replies View Related

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 1 Replies View Related

Calculate Sumproduct Of COST Between Any Two Fields (days Of Week)

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

Summarize A Query / Pivot Table Style

Dec 16, 2013

So, I've recently learned that Access 2013 took a step backward and got rid of PivotTables. Unfortunately for me, PivotTables were perfect for summary reports I have to create with large data sets (some with millions of records).

What are my alternatives to PivotTables within Access 2013? I was playing around with the report builder, but it seems to retain all line items and doesn't allow me to collapse everything into a one page report.

View 5 Replies View Related

Reports :: Inserting Page Break To Show Week By Week Pages

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

Queries :: Get Min Week For Each Record

Oct 4, 2013

I have a table with products information (week, inventory, orders coming from supplier). I want to find out what week is the first order coming in. I have tried using dlookup to find week but, not working.

View 4 Replies View Related

Rs Fields(0).value Didnt Work

Feb 1, 2005

hi lagbolt,

tried doing rs fields(0).value in the code but i didnt work.
one more thing i earlier had following code in a command button when it worked properly but now i added the code mentioned in last post, it was not working. please throw some light.

Dim x As String
x = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then
cn.Open "dsn=ABC", "", ""
End If
rs.Open x, cn
If (rs.EOF = False) Then
RESOURCEINFO.Value = rs(0)
End If
rs.Close
cn.Close

this was working fine till i copied this code with some modifications in the earlier code.

View 1 Replies View Related

Queries :: All Incoming Birthdays This Week

Mar 24, 2015

So I found this video (youtube.com/watch?v=FTjyuSTcTII&spfreload=10) and followed it to do a query for all upcoming birthdays. The video shows how to do it for the next two months and I tried to modify it just for the next 7 days. When I tried to run it, I got "Date type mismatch in criteria expression" error. I tried putting it back in the criteria she had, but still got the same thing.

At 6:53 you can find the code she used for the first table (basically asking when their next birthday is) and at 9:57 is the code for the criteria. Below is my modification.

Between Date() And DateAdd("d",7,Date())

View 14 Replies View Related

Queries :: Count By Current Week

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

How To Get DateAdd In A Query To Work Through Other Fields?

Sep 6, 2006

Hi,

I am trying to get DATEADD to work in a query where the parts are called by different colums on the query like so:

Code:ExpiryDate: DateAdd("" & [DDType] & "",[DDNum],[Date])
At the moment the above will return the [DATE] without adding on the numbers

The [DDNum] is the number and works fine
The [Date] is the date and works fine

I know this becase the following code will give a new date
Code:ExpiryDate: DateAdd("d",[DDNum],[Date])

Could someone kindly let me know what I am missing in my syntax to get this working - I can get it working in ASP but I also need it to work in an Access Database

thank you for your time

View 2 Replies View Related

Queries :: Calculating Time For Two Week Period

Jan 8, 2015

I am using access 2013 and have an issue creating a query with employee hours for a two week period.

For starters I have a table with the following:

Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)

My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.

At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.

In my query (to Start) I need to have the following with it set up per pay period.

Employee Number Rate Code Regular Hours Overtime Hours

How to set it up so that it calculates the hours based per rate code with the given pay period.

View 11 Replies View Related

Queries :: Week Number Query When Year Changes

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

Queries :: Sorting - Show Records From This Week Only

Apr 23, 2013

I'm doing a query and I want it to show records from this week only.

Is there a formula I can use in criteria?

View 3 Replies View Related

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 2 Replies View Related

Queries :: Unable To Get Current Week Number

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

Queries :: Grouping Data By Week And Display

Mar 19, 2013

I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.

Using the following in the query design window.

Week of Year: Format([Date],"ww")

This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?

I tried sorting but that doesn't work.

I also tried the following:

Format([Date],"mmmm,ww")

But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3

Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.

View 5 Replies View Related

Queries :: Return Week Number On The Basis Of Date

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

Queries :: Extract Day Of Week From A Date To Link To Data

Dec 8, 2014

I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.

I then want to link this "ddd" to data in my table that contains the three letter days of the week.

How do I make the day become "static" in order to be able to link.

View 6 Replies View Related







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