Group By and Expressions

I have a table linked to another table in a one to many relationship.

I am trying to get a query to work out some statistics by group the many records on the right table. I can use the group by function and the built in functions such as min, max etc.

However, I need to do slightly more complex expressions to produce statistics that I require.

To complicate matters, my tables are built into another query (X) which calculates some expressions. In the query I am trying to set up I am trying to maniputae numbers from query X.

I can't get this to work using the build expression function, I keep getting errors. Is this because I can't query a query or can't query previously calculated fields?

I hope this makes sense.


Post your answer or comment

comments powered by Disqus
I have the following code:

SELECT ClassID, COUNT(*) AS Total
FROM dbo.tbl_Claimant
GROUP BY ClassID

The problem is I want to filter out PersonID 20 but when I do that I have to put PersonID in the group by and then it groups it by PersonID instead of just ClassID. So instead of getting 10 ClassID with a count I get 100 records with a count.

Any suggestions?

In the form i have here there 4 columns the first one i have to count and group by branch_number. I have put group by and count in those coilums and take to long. the coulmn branch number has this criteria CDbl(Right([DBA_INC_LOAN_BANK]![BRANCH_NUMBER],3)) how i can put this because i want to know how many accounts i have in a branch.

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?

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!

Hello all.

I have a query that must ORDER BY Aisle_Number

I have 2 other fields that must GROUP BY for a report.

The report calls the query.

One field, tblStoreProduct.Storage, can be either "I" or "C". The report must start a new page for "I" and "C" in the same aisle.

Another field, tblStoreProduct.Use, can be "S", "O", or "C". The report must ALSO start a new page for each Use within each Storage within each Aisle.

Is this possible to do?

I am trying to establish Service Level achievements for issue resoultion and have managed to create a query that gives me the working hours for each query. For Instance:

Date Received Week # Date Done TotalHours
25/01/2005 10:22:00 4 25/01/2005 10:23:00 0
25/01/2005 15:45:00 4 25/01/2005 15:45:00 0
27/01/2005 16:18:00 4 28/01/2005 10:35:00 2.3
27/01/2005 16:41:00 4 28/01/2005 10:35:00 1.9
01/02/2005 16:56:00 5 02/02/2005 09:00:00 0.1
02/02/2005 13:47:00 5 02/02/2005 15:09:00 1.4
08/02/2005 10:02:00 6 08/02/2005 13:30:00 3.5
08/02/2005 11:07:00 6 14/02/2005 09:00:00 29.9

etc......

What I want to do is analyse these by range of total hours by week number. So in Week 4, 50% resolved between 0 and 8 hours, 40% between 8 and 16 hours and 10% over 16 hours etc....

Can anybody help with this, please? Possibly not Group by etc...

Hi guys,

i have a table where I can get the record with most recent date (not the most recent record) for each user on that table, so first I made a query to sort by date then I made a query based on the date sorted query to group by user and pick the last value, but the last value is always from the original table not from the query, I made this:

utentes = users

q_date_sorted (q_utentes_mensalidades_ordena_data)
SELECT utentes_mensalidades.*
FROM utentes_mensalidades
ORDER BY utentes_mensalidades.data;

Now I wanna pick the last value of Valor field for each user (utente)
q_pick_last_value
SELECT utente_id, Last(valor) AS LastOfvalor
FROM q_utentes_mensalidades_ordena_data
GROUP BY utente_id;

If in the original table is, for user 1:
date ---- value
1-1-6 ---- 100
1-3-6 ---- 200
1-2-6 ---- 300

(dates are in american format)
the result should be 200 (most recent date)
but I am getting 300 (most recent record)

I hope it's clear,

regards,
Nuno.

Hi,
im haveing a few problems working out how to do the following:

i have a query that has 4 fields:

A - B - C - D

i want to group by the A ~ C and then summarise the content of D into one cell per group.

EG

if the data looks like the following

A - B - C - D

aa1 - bb1 - cc1 - dd1
aa1 - bb1 - cc1 - dd2
aa2 - bb2 - cc1 - dd1
aa2 - bb1 - cc2 - dd1
aa3 - bb1 - cc3 - dd1
aa3 - bb1 - cc3 - dd1

the results should look like:

aa1 - bb1 - cc1 - dd1 / dd2
aa2 - bb2 - cc1 - dd1
aa2 - bb1 - cc2 - dd1
aa3 - bb1 - cc3 - dd1 / dd1

any ideas on how i can do this?

best regards

stu

Hello!

I have 1-many relationship:

tblMain: Invoice, Date

tblProducts: ProdID, Invoice, Product, PriceProduct, Packaging, PricePackaging

Okay, the deal is that all of the fields are being filled with info except PricePackaging which is being filled at the end of the month.

PricePackaging is a monthly value which depends on "Packaging", in other words, I tried making a query like this:

M: Month(Date), Packaging, PricePackaging
Group by M and Packaging

I could make it, but I cannot modify PricePackaging. I actually guess Group By would never allow me modify a query but how can I make this work so as not copy and paste the same values? That is a really heavy task on my db since its really big.

Thanks a LOT in advance!

Hi,

I have a table of data, Enrolments, that includes the columns, [DeptNo], [CourseNo], [LearnerNo] and [Gender] with a load of other data. The same [LearnerNo] can have rows relating to one or more [CourseNo]'s that might be in one or more [Dept]'s.

I need a query that will return by department {Group by [Dept]} and gender {group by [Gender]} the number of enrolments in the department {count of [LearnerNo]} and also the head-count by gender.

It's this last bit that I am stuck on. What I need is the number of (Males) already returned by {group by [Gender]} grouped by [LearnerNo] and counted ** I think **.

Sample data:
[Dept] [CourseNo] [LearnoNo] [Gender]
X AA R M
X AA S F
X AA T M
X AB R M
X AC R M
X AC S F
X AC T M
X AD R M

would return for department X
6 male enrolments by 2 Learners and
2 female enrolments by 1 Learner

Is there a way to do this?

Thanks in advance,
Ambre

Hi,

I have a table with 2 columns:

Company and Gender

I would like to make a query which groups by Company and counts the values in column gender if gender value is "male"

br Bertrand

I have a simple query where in I am looking at Member Plans in 6 different states. I need to group by State and get a count of total plans per state and then have a grand total at the end can I do this in Access? I am using MS Access 2007 odbc connected to an Oracle Database. I am enclosing a copy of the query design. Thanks!! Attached Files Count Question.zip (117.5 KB, 1 views) Reply With Quote 04-19-2012, 12:07 PM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,230 Click on the "Totals" icon on the ribbon. That will add a Totals row to the design grid. Change the default Group By to Count for the relevant field. You may want to drop some of those fields, or change them to Where. Play around with that and see how it does.

Hi - this is my first post, so please be gentle....

I'm a self-tought Access user, and have been trying for the past 4 hours to get a query to work with no success....

I have a table with many records (50,000 +) that I am trying to make a query out of...

I want to make a query that combines the rows that are the same concurrently..... (i.e. combine the rows with the same values in the row below in a certain field).

My table is set up like:

Hole ID -- From -- To -- Rock1
1a -- 0 -- 1 -- granite
1a -- 1 -- 2 -- basalt
1a -- 2 -- 3 -- basalt
1a -- 3 -- 4 -- basalt
1a -- 4 -- 5 -- granite
1a -- 5 -- 6 -- granite
1a -- 6 -- 7 -- granite
1a -- 7 -- 8 -- basalt


and what I want it to look like is:

Hole ID -- From -- To -- Rock1
1a -- 0 -- 1 -- granite
1a -- 1 -- 4 -- basalt
1a -- 4 -- 7 -- granite
1a -- 7 -- 8 -- basalt


In my query (design veiw), I have the columns needed, with
'hole ID' - group by, sort ascending
'from' - first, sort ascending
'to' - last
'rock1' - group by

and it looks like this:
Hole ID -- From -- To -- Rock1
1a -- 0 -- 7 -- granite
1a -- 1 -- 8 -- basalt

it's nearly right, but not quite....

I would appreciate any help
PLEASE...............

Hi,

I have an UPDATE query I hope to get some help with.


I have two tables, tblcustomers and tblreadings. The are related through on-to-many (tblcustomers -> tblreadings).

I would like the UPDATE to perform the following:

For every customer in tblcustomer, find the MAX(Reading) and corresponding ReadingDate, and add that to the MaxReading and MaxReadingDate field for that specific customer. I guess I need som GROUP BY and MAX on the tblreadings and the UPDATE, SET and JOIN that subquery to the tblcustomers, but I am unsure?


tblcustomers (simplyfied)


CustomerID - MaxReading - MaxReadingDate
------------------------------------------
1 - Null - Null
2 - Null - Null


tblreadings (simplyfied)


ReadingID - CustomerID - Reading - ReadingDate
-----------------------------------------------
1 - 1 - 200 - 2012-01-01
2 - 2 - 300 - 2012-02-02
3 - 1 - 400 - 2011-09-01

I have below given a short example of what I would like the result UPDATE to be:

tblcustomers

CustomerID - MaxReading - MaxReadingDate
------------------------------------------
1 - 400 - 2011-09-01
2 - 300 - 2012-02-02

Someone with an idea?

With the SQL Server as the BE DB, I can run the Estimate Execution Plan (in the Management Studio) to visually see what indexes SQL Server is going to use. I can then drop not needed ones / add ones that I see missing.

Is there any way to do that sort of detailed analysis with Access FE queries?

I have reports which do a GROUP BY, and overall have an ORDER BY a couple of different columns.

I would like to know what indexes on the FE temp table would assist Access with the task of displaying the report. "Would a multi-column index be helpful? Or would Access prefer several single column indexes?" That sort of thing.

Hi all.
Stumbled onto this forum during a google search.. looks like a great forum.

I have a question.
this thread http://www.access-programmers.co.uk/...d.php?t=124689

goes into a simple function to group by the name and add their total.. however what i need to do is that plus have a user input of the date.

eg of the table


	Code:
	Date             Title               Invoice
02/02/07       ABC123             11.00
02/02/07       ABC123             12.50
02/02/07       DEC123             11.50
03/02/07       ABC123             10.50

What i need is a paramater query to be able to work to give me the total of the groups..
Whenever i try to join my Parameter query with my sum query it gets messed up..

heres the code... what am i doing wrong?..
What i want displayed after they input the date is the group by and sum total of the parameter query..


	Code:
	SELECT RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST,
Sum(RawData_tbl.Invoice) AS SumOfInvoice
FROM RawData_tbl
GROUP BY RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST
HAVING (((RawData_tbl.[Date In]) Between [Please Type the first day of the month] And [ Please insert the last day of the
month]))
ORDER BY RawData_tbl.[Title 2];



Dear all,

I find that Access cannot use "group by" in the sub query. The following query will prompt up a message "Operation must use an updatable query." if being run:

update Summary as s inner join
(SELECT refno, sum(amount) as s_amount
FROM Details
WHERE Refno = '20090213001'
group by r.refno
) as d on s.refno=d.refno
set s.amount=d.s_amount

If I remove the "group by" and "sum(amount)" in the above statement, this update query is runable.

How to solve this problem or is there any workaround for this kind of SQL statement?

Many thanks

Quick question...

I have a table with 50,000 records in it. It was a linked table and one of the fields I needed to convert to numeric. So, I went ahead and put my "Val()" conversion formula in the necessary field. Initially I had made everything a "Group By" in the "total" field. I ran the query and I only received 48,000 records as the result. I took off the "Group By" and the query returned 50,000 records. I was under the impression the "Group By" did not consolidate records. I know this seems elementary, but never ran across something like this before.

(Access 2007) I have a table with 2 columns: 'Sample_Time' and 'num_of_messages'.
the table contain around 400 records for each day. I want to make a query which will give me as output the time (date and time) and 'num_of_messages' where maximum 'num_of _messages' appear for every day (group by date).

I tried to run the following query but it gives me an error: "You tried to execute a query that does not include the specified expression 'Format([Tbl_message].[Sample_Time],"Short Time")' as part of an aggregate function":

SELECT Max(Tbl_message.[num_of_messages]) AS [max messages], (Format([Tbl_message].[Sample_Time],"Short Time")) AS 'Sample Time'
FROM Tbl_message
GROUP BY (Format([Tbl_message].[Smaple_Time],"Short Date"));

I can only run the following but it gives me only the date without the time which is not good enough for me:

SELECT Max(Tbl_message.[num_of_messages]) AS [max messages], (Format([Tbl_message].[Sample_Time],"Short Time")) AS 'Sample Time'
FROM Tbl_message
GROUP BY (Format([Tbl_message].[Smaple_Time],""Short Time"));


does this mean that I cant use different Time format? how to overcome this?

Thanks for Your help

Hello,

I'm trying to build a SQL statement with which I can group the table entries according to month and year, and not according to the whole date. Eg, all entries entered in September 2004 must be in one group. At the moment I can only get them to group by the whole date, thus entries entered on 13 September 2004 will be grouped, but entries on 13 and 24 September will not be grouped together.
I'm trying to build links for each month of a year, so that users can click on a link and view all the entries for that specific month and year.

Am I being clear here? I'm getting more and more confused....

I'm using ASP with an Access database.

This is the SQL code:

SELECT format(arcDate,'mmmm yyyy') AS fdate, format(arcDate,'dd/mm/yyyy') AS ndate
FROM archive
WHERE arcCatagory = 2
GROUP BY arcDate
ORDER BY arcDate DESC

which groups by the whole date.

fdate is for the links, and ndate is for the querystring value that gets passed when a link is clicked.
arcDate is the date the entry got entered into the database.

Now when I try to group by only the month and year:

SELECT format(arcDate,'mmmm yyyy') AS fdate, format(arcDate,'dd/mm/yyyy') AS ndate
FROM archive
WHERE arcCatagory = 2
GROUP BY format(arcDate,'mmmm yyyy')
ORDER BY arcDate DESC

I get this lovely error:

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'format(arcDate,'dd/mm/yyyy')' as part of an aggregate function.

Helep? What am I doing wrong?
Helep Helep!

Hello

I have a query fed by 3 tables and 2 queries. For years the query displayed my data with no problems. However, when I added a large number of extra fields to what was already a large number of fields I get the error above.

I've found that if I delete 12 or more fields the query will run. I'd like to keep those fields though since it will cause me more work bringing them back to the data. In my query every field except one is a group by since I have totaling turned on. There is one expression field.

I tried going to sql view and deleting all the group by fields except the one key field but get the error You tried to execute a query that does not include the specified expression 'Program Name' as part of an aggregate function. Program Name is my first field in the query. I tried deleting the field but the error then gives me my second field.

I do not know sql but do know enough to go to the view and change something.

Any suggestions or comments would be welcome.

Let me start by saying I know the BASICS only. Most of what I have learned has been from reading here. I am trying to create a report grouped by payment date. The problem I'm having is there are 3 different payment date fields on one table and 1 payment date field on another table. I cannot figure out how to get my group expression to pull up the dates from each one of the fields. Do I need to create a new table for each payment type, or is there a way to create a common field "Payment Date" and pull up date from these 3 fields. I have a criteria form which sets beginning and ending dates, but have no idea where to assign it. The report is called "Payments by Date" and I need to show PPD (primary payment date), SPD (secondary payment date), etc. Please help

I have a table with 4 fields, A,B,C and D.
Would like to sum up individually the values of B,C and D and GROUP BY A,


	Code:
	 
For example Input is
A      B    C    D
1      3    4    1
1      1    1    3
3      4    7    2
3      8    4    3
5      3    5    1
 
Output should be
1      4    5    4
3     12   11    5 
5      3    5    1

I tried using Sum and Group By, but to no avail.

Is there a single SQL statement which can achieve this?

Thanks,
John

Can you use a Union Query and a Group By

I have two identical queries except for the source.

Select Blah, Blah (some have the Sum function)
From Blah, Blah
Group By (things not Sum'd)
Order By Blah, Blah

Each work fine individually and I have tried a couple of small Union Queries that don't have Groups and they work. However, I'm trying to put the SQL code in the form itself and I can't seem to get the group working.

Has anyone done this? I can't seem to figure out the syntax?

Thank you,


Not finding an answer? Try a Google search.