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?


Sponsored Links:



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