Total with a group by query

I have a query that takes in two dates, start and end, and a team as parameters. It calculates average interview times, and total interviews grouping by each person in that team. I would like to add a column at the end that shows the total number of interviews done by the entire team.

Output as it is now...

Name............Average Time..........Interviews
Matthew............30.........................7
Jane..................29.........................1 1

Output I would like

Name............Average Time Interviews Team Interviews
Matthew..........30...............7............... .....67
Jane................29..............11............ .......67

Here is the sql as I have it now,

SELECT tblInterviews.[Revenue Specialist], Avg(tblInterviews.[Interview Length]) AS [AvgOfInterview Length], Count(tblInterviews.[Interview Length]) AS [CountOfInterview Length]

FROM tblClient INNER JOIN tblInterviews ON tblClient.VisitID = tblInterviews.VisitID

WHERE (((tblClient.Date) Between [start?] And [End?]) AND ((tblInterviews.[Time End]) Is Not Null))

GROUP BY tblInterviews.[Revenue Specialist], tblInterviews.Subprocess
HAVING (((tblInterviews.[Revenue Specialist]) Is Not Null) AND ((tblInterviews.Subprocess)=[Team?]) AND ((Avg(tblInterviews.[Interview Length])) Is Not Null));

Any suggestions on how I could add a total number of interviews field?


Post your answer or comment

comments powered by Disqus
I'm running a Group By Query to get totals by Tier. If i have 4 records...3 have a value of 3 in the Tier field and 1 has a value of 2 in the Tier field I will get the following result from a Group By query for example

Tier Discount Rooms Units CUWODO CUWDO 2FAM 18H COMM_OFF WH VAC 2 20 188 14 14 14 14 14 14 14 14 3 20 511 30 24 24 54 26 28 12 22





I want to see this if there are no records under Tier 1.

Tier Discount Rooms Units CUWODO CUWDO 2FAM 18H COMM_OFF WH VAC 1 0 0 0 0 0 0 0 0 0 0 2 20 188 14 14 14 14 14 14 14 14 3 20 511 30 24 24 54 26 28 12 22







Can anyone give me an idea how to do this? Is there a way to create a "dummy" record place holder for Tier 1 and give all 0s? Any help would be very much appreciated. I'm trying to find a way to do this but i'm not even sure how to look up help because i don't know how to describe what i'm trying to do.
TIA
Ted

I have a form which uses a 'Group By' query on a given field. This works fine by compiling seperate orders (all from the same company and at the same time by the same person) but does not allow a user to input (I have a drop down box to select whether the order has been placed or not, which in turn adds the date and pre-fix of the order). This worked fine before I made it a Totals query and I am thinking it must be that I can't update a grouped record on a single row?

Is there a way around this does anyone know?

Thanks

Hello,

I am using Access 97 and I am getting very stuck when I am making a group by query.

Basically I have a table which has for engineering jobs (and a few other collums):
[id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]

I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:

SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1

good morning All,

In Microsoft Access 2002 (XP) I am having trouble in Saving or Running a query .. because

when I try in Saving or running this query .. Access closes Automatically ..

this query is a group by query that also have many IIF function inside it...

Note :
1- sometimes when I try also to see the design view of the query .. Access also close automatically
2- when you write IIF inside a Group by query ... the group by for this field is transefer to "Expression" in front of "Total" .. and it add another field for the IIF in the last column with "Group by" in front of "Total" .. and if I try to Run query I get this message in MS ACCESS "Syntax error" in IIF ... so I must delete the Last colum that access Added .. and changed the original column from "Expression" to "Group by"
but when I save ... Access close Automatically ... is there a problem in Group by with IIF function ??

thanks for anyone who try to help.

I’ve got a query which states: -


	Code:
	SELECT pID, pName, pStocked, pSold FROM tblPart
GROUP BY pID;


But it returns the following:


	Code:
	pID       pName              pStocked          pSold
1          Wrench             100                   5
1          Wrench             100                   7


Where I actually want it to return: -


	Code:
	pID       pName              pStocked          pSold
1          Wrench             100                   12


Where it groups pID, pName and pStocked which match each other, but it sums up pSold for every record with matching pID and pName.

I’ve tried the following: -


	Code:
	SELECT pID, pName, pStocked, sum(pSold) AS sSold FROM tblPart
GROUP BY pID, pName pPrice, pSold;


But it doesn’t seem to change the output, any ideas? And it wont let me group sum(pSold) as it states I cant have an aggregate field in a GROUP BY clause.

Also once I’ve got the required output I then want to then include the field (pStocked - sum(pSold)) AS pLeft, so it produces the output: -


	Code:
	pID       pName              pStocked          pSold                pLeft
1          Wrench             100                   12                     88



Any help will be much appreciated.

I have two tables Employee and PayJournal.

The Employee table has the following fields - EmpLogID, EmployeeID, FirstName, LastName, and Department. The EmployeeID is unique. EmpLogID is an autonumber.

The PayJournal table has the following - PayJournalID, EmployeeID, ProcessDate, and ProcessRate. This table can have multiple entries for an EmployeeID

The tables are joined by EmployeeID.

I would like a query to select EmployeeID, FirstName, LastName, Department, ProcessDate, and the sum of the ProcessRate fields for each EmployeeID for a specific date. Each Employee should only be listed once for a particular date with the total of all the process rates for that date.

A date range will be passed as a parameter from a form and will be matched with the ProcessDate.

I know that I need to use Sum and a Group by clause. The queries I have tried are returning all rows for every employee entered in the PayJournal table when it should only be one for each date.

For example: Polythene Pam has four entries in the PayJournal table. Two of the entries are 8/20/2008 with .50 as the ProcessRate. The other two are 8/21/2008 with .25 for the ProcessRate. I would like to then have two results for that employee from my query. One result would be 1.00 as the sum of the process rates on 8/20/2008. The other would have .50 as the sum of the process rates for 8/21/2008.

Can anyone help me out? It has been awhile since I have used this type of query. Thanks.

how do i perform a Count(Distinct) query for 2 different column values using GROUP BY

I have this "source" table like this....

|-------------------------------------|
|ROLLUP_ID | GAP_ID | REQ_ID | COST |
|-------------------------------------|
|001 | 001 | 002 | 12.00 |
|001 | 002 | 002 | 15.00 |
|005 | 005 | 009 | 9.00 |
|-------------------------------------|

I need a group by query with distinct for ms access 2007...

to make the following...aggregate query

|------------------------------------------------------|
|ROLLUP_ID | GAP_COUNT | REQ_COUNT | TOTAL_COST |
|------------------------------------------------------|
|001 | 2 | 1 | 27.00 |
|005 | 1 | 1 | 9.00 |
|------------------------------------------------------|

Any help would be so grateful...

I looked at this suggestion but I am not sure its applicable:

http://blogs.msdn.com/access/archive...in-access.aspx

Hi guys.

Does anyone know how to run a make table/group by query from vba?

What I want to do it run a loop through, say, 100000 records. This creates a new table with some added calculated fields. After these 100 000 records, I want the code to make a table with grouping for a selection of variables and summing for the last. Then I want the code to start from record 100 001 and repeat the procedure through record 200 000, make a grouped by table and append this table to the previous table. Then again for 200 001 to 300 000 etc until EOF.

I have the code that will perform the calculations and it works fine, if a little inefficiently. There are millions of records in the priginal table I'm working with, and the code (necessarily) creates a new table with many many more records in it, hence the need to group every now and again.

Here is the SQL string:

SELECT TEMP_KN_DATA.PRODUCT_CODE, TEMP_KN_DATA.POL_TYPE, TEMP_KN_DATA.PRM_STATUS, TEMP_KN_DATA.SEX, TEMP_KN_DATA.IRP_MKR, TEMP_KN_DATA.SMOKER_STAT, TEMP_KN_DATA.OCC_CLASS, TEMP_KN_DATA.CALENDAR_YEAR, TEMP_KN_DATA.DURATION, TEMP_KN_DATA.AGENEXTBIRTHDAY, Sum(TEMP_KN_DATA.EXPOSURE) AS SumOfEXPOSURE INTO THE_BIG_TABLE
FROM TEMP_KN_DATA
GROUP BY TEMP_KN_DATA.PRODUCT_CODE, TEMP_KN_DATA.POL_TYPE, TEMP_KN_DATA.PRM_STATUS, TEMP_KN_DATA.SEX, TEMP_KN_DATA.IRP_MKR, TEMP_KN_DATA.SMOKER_STAT, TEMP_KN_DATA.OCC_CLASS, TEMP_KN_DATA.CALENDAR_YEAR, TEMP_KN_DATA.DURATION, TEMP_KN_DATA.AGENEXTBIRTHDAY;

Have no idea what to do with it.

Please help!

I have an "update" query, based on a totals query. (basically i end up in the final query with a unique key field, and a new value field that i want to use)

I want to use this to update some values in another table, but it's non-updateable.

so I have achieved it in two ways.

1. use the "update" query, as a maketable query, then use the created table to update the real data

2. use the "update" query as the source of a recordset, and write SQL update statmeents row by row.

Both work, and method 1 is far quicker - but I wondered whether there might be a different way of setting out the queries so I do not end up with a non-updateable query in the first place

Hope someone can help.

I need to be able to use a variable to "name" a table created by running a "Make Table" query. The variable is a field in the both the original source table, the query and the newly created table.

We have mutiple stores who create "batches" of invoices etc. Each batch when closed is exported as an Excel spreadsheet and emailed to our accounts office.

My application creates a new unique "batch number" for each new batch for each store based on a store code taken from a set up table and combining it in a string with Day of Month, Day of Week and todays date/time. Thus multiple batches can be created by a store in the same day but each has a unique number.

I want to use this batch number to "name" an new table I create with a Make Table Query. At the momement I have to nominate a name for the new table in the Query ie. "XXBATCH" where XX is the two letter store code, then email this table as an XXBATCH.XLS file. Thus each emailed file has the same name?? This causes problems at the receiving end with overwrites etc.

If the above cannot be done can anyone suggest an alternative way of renaming either the newly created table with a variable or the export table (perhaps using DOS batch file?)

Appreciate your support. Great site by the way. Very helpful.

I'm trying to run a query with a custom built function 'StripNumbers'. This function removes any numbers from a string and returns a string as result.

This works:


	Code:
	SELECT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt

The same function with DISTINCT added returns a data type mismatch error.


	Code:
	SELECT DISTINCT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt

I get the same error with a GROUP BY clause.


	Code:
	SELECT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt
GROUP BY StripNumbers(pt.Code2)

Can anyone help me understand where the problem lies so I can work around it?

Hi All,

I have created a web database in Access 2010. I have published the same on the SharePoint 2010. I have create a Group BY query but aggregate option itself is not available in Access 2010. Please let me know how to create a query with "Group By" clause.

Please guide.

Regards
Sudhir

Hello,

I was wondering if there is a way to use a filter by query or the RunPermissions property so that a user will only see a certain set of the records in the database? For example, I am creating a database with employee records. The database will be used by department managers. I want the database to show only those employee records that are in the department that pertains to the manager that has signed into the database. When a different manager signs in he or she will then see only the employee records from their department. Is this possible?

Thank you for your help!

Robert

I have created a query with the follwoing column:

Month: DateSerial(Year([Day]),Month([Day]),1)

as soon as I try to create a group by query it gives me a:

"Data type mismatch in cirteria expression"

SQL is below:
SELECT tblUsage.[Product Number], tblUsage.[Contract Name], tblUsage.[Date Imported], tblUsage.State, Sum(tblUsage.Usage) AS SumOfUsage, DateSerial(Year([Day]),Month([Day]),1) AS [Month]
FROM tblUsage
GROUP BY tblUsage.[Product Number], tblUsage.[Contract Name], tblUsage.[Date Imported], tblUsage.State, DateSerial(Year([Day]),Month([Day]),1);

Any help would be greately appreciated

There are fairly simple queries to find the maximum or minimum value for a field in a grouped by query that may have 2-3 or more groupings, but is it possible to find, say the 2nd and/or 3rd highest values and their associated fields in an ACCESS (2003) query? or must one resort to VBA programming. In either case, how is this done - if it can be done?

Thanks,

Hi all, Can someone please help me with a date range query. Basically I have a query that shows delivery dates, from 01/01/2006 to 01/01/2009.
I just want to modify the query so it shows all deliveries within the past 21 days and all outstanding deliveries until 2009. Please note that in some cases, the delivery date may have been left blank in the table (as this it maybe unknown). If that is the case, the query criterion needs to pick that up as an outstanding delivery. Can someone help me with this? So far I’ve figured out

Between Date() And Date()-"21"

Thanks

Given a simple table with historical info about Hotelroom occupancies such as name of the guest, date in, type of room, number of nights, ...
I want to make a query resulting in 1 output record that shows the highest number of nights someone has ever stayed in the hotel, as well as the corresponding detail information: who was this customer and which type of room was he in?
Using the 'group by' and 'max' function it's easy to solve the first question, but I am not able to show the corresponding detailed information in that record (person's name, type of room).
I've done some experiments with a nested query in the 'expression' field, also trying a selfjoin but always getting errors. I'm confident there must be a solution? Can anyone help me out? Thank you!

I have been struggling with this for a while now and cant seem to get this update to work.

I have a table that holds a project id, a line ID and then the percent allocation to each service line. I am trying to calculate the percent allocation based on the number of repeat project id's IE: 1 project id appears in the table you get 1 for the percent allocation, 2 you get .5, 3 you get .3333333 and so on.

Here is my current query:


	Code:
	UPDATE 
     rlnAPOppServLine AS A, 
     (SELECT rlnAPOppServLine.SAPOMID, 1/Count(rlnAPOppServLine.SAPOMID) AS AutoPercent
FROM rlnAPOppServLine
GROUP BY rlnAPOppServLine.SAPOMID) AS B 
SET A.PercentAlloc = [B].[AutoPercent]
WHERE (((A.SAPOMID)=[B].[SAPOMID]));

Thanks

I have been asked by a colleague to help with a database that I have not been involved in designing.

It has data weather data for a number of times for every day. They wanted to get out the max wind speed for every day. This was easily achieved by using the Group By Day and Max wind speed in the basic query design window.

They would now like the direction associated with that speed. I can’t seem to do this easily using the standard query window. Could anyone help me please?

Thanks
CC

I would like to create a query that 'groups by' the first two columns, selects the 'max' of the third column, then 'carries over' the information in the last two columns that go along with the same row as 'max' of the third column. See below example input and desired output:

Input Table
C1, C2, C3, C4, C5
Mike, Smith, 10, D1, D2
Mike, Jones, 50, D1, D2
Joe, Smith, 60, D1, D2
Joe, Brown, 10, K1, D2
Mike, Smith, 20, D1, D2
Mike, Smith, 30, K1, K2
Mike, Jones, 60, K1, K2
Mike, Jones, 40, D1, D2


Desired output table

Mike, Smith, 30, K1, K2
Mike, Jones, 60, K1, K2
Joe, Smith, 60, D1, D2
Joe, Brown, 10, K1, D2

I've figured out how to 'group by' the first two columns and get the 'max' of the third column. I cannot figure out how to 'carry over' the last two columns. Any help would be appreciated... Thanks!

Is there any way to get the max based on two criteria or one criteria and a group by? so for example I have a table with 3 fields, [GroupByVariable], [Conc], and [D_Conc];

GroupByVariable Conc D_Conc Sediment 25 1 Sediment 30 0 Sediment 20 1 Soil 17 1 Soil 8 0
I want a query to get the max of field [Conc] based on [D_Conc] = 1 and [GroupByVariable]. It would look something like this...

GroupByVariable MaxConcBasedOnD_Conc1 Sediment 25 Soil 17
I'd like to do this in one query and also not by crosstab because I want to keep building...like get min/max/avg/count/ for each [D_Conc] 0 and 1 and then a total count for all 0 and 1.

I have a tricky scenario here that I am trying to handle with a report, but I'm really hitting a wall. I will try to explain what's going on as simply as I can.

The recordset of the report contains a list of orders, each with a company name, $ amount, and order date. The goal is to create a report that groups by company (showing only the company name once), with the total $ value, and the max of the order date. This is all simple enough, and best done on the query side, because Access does not handle the "grouping on one field, sorting on another at the same level" functionality. So at first I did this in the query, and on the report I just added a sort to the $ field.

Further, I need to be able to allow the user to specify a date range for the "ungrouped" recordset that contains each order date. I can't do this if I am grouping to get a max date on the query side. So now I had to change that recordset to not group by date. Now I would be forced to do this on the report side. I am basically trying to grouping on the Company field, while sorting by the $ amount. Really running into a wall. Don't know if this is possible. Any help would be greatly appreciated.

Thanks.

I am trying to setup a Command button that will filter my form based on the selection in a combo box.

Form Name: Agent
Combobox: CompanyFilter

Some companies have multiple agents working for them, so when I created my query for the row source for the CompanyFilter comboBox, I decided to use the Total function and use the GroupBy on the query.

In my query the fields Agents.ID and Company are displayed. With the group by on the Agents.ID set to count. When looking at the query it groups all the companies together.

However, when I select a company from the comboBox the filter on the form comes up with an empty record every time.

Here is the code I am using for the command button: Code: Private Sub Command65_Click() If IsNull(Me.CompanyFilter) Then Forms!Agent!.FilterOn = False Else Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter & "'" Forms!Agent!.FilterOn = True End If End Sub
Now, when I change the Row Source Type for this comboBox from table/query to Value List and actually type in the name of each individual company. Then when I make a selection it will filter the form correctly. However, I don't want to have to go into design view every time a new agent with a new company pops up and add it to the Value list.

I am using MS Access 2000 to try and help me with my handicapping for horse racing - have been trying to win at the races for the past 40 years - not successful yet - but I will never say die!! Think this must be easy but have spent days trying to figure this out, still - with no success yet.

I have table with 4 fields ......"first", "second" and "third" and "number of horses".

Within each of the fields("first", 'second" and 'third") are numbers from 1 to 14 which show the final position that the horse finished. The number of horses field is the number of horses in that race - i.e. - 5,6,7,8 etc.

I have been able to produce a report that displays a grouping by number of horses with individal results (first, second, third) for each race. So far I have about 1300 individual race results. So I am trying to determine if any patterns exist by number of horses.

So - does any one know how I can create a report....

First - by grouping - Number of Horses (5,6,7,8 etc) by their results and then being able to count the numbers of Final positions - i.e. count the total numbers of 1's, 2's, 3's, 4's etc. for each field "First", "Second" and "Third".

Then - would it be possible to create a report by number of horses - that if the result of the Field "First" is 1 - then list and count all the combinations of the "second" and "third" fields? Then if the result of Field "First" is 2 - then combination s of "second" and "third" fields etc and so forth?

I can visualize what I am trying to accomplish but cant get it to work in Access - I may not find any patterns but at least I am determined to try.

Thank you anyone who can provide some guidance. (Also - I am not too good in Access so if you wouldn't mind - plz advise where for instance I should add a formula (should it go in Criteria in a query etc - or in a report - the type of box I would need and where in a report - header footer etc)

Thank you very much - I appreciate any assistance - Steven


Not finding an answer? Try a Google search.