GroupBy Help Plz
Jan 5, 2007
Hi
I wanna do something similar to what Stephanie.D tried to do before..I read the suggestion she recieved but doesn't seem to work for me. I have one table with the following
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
I'd like to create a query that only returns the rows of the table where the Price of the Name is the smallest. Here's an example...
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
1 drinks wine Porto Store1 5
2 drinks wine Porto Store2 6
3 food bread Baguette Store3 1
4 food bread Integral Store3 2
5 drinks wine Bordeaux Store3 3
It should return
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
3 food bread Baguette Store3 1
5 drinks wine Bordeaux Store3 3
I tried doing a GroupBy but it only works when for the query I only use the SpecNameOfProd and Price fields. I tried using Where like it was suggested to Stephanie but I need the other fields to show...
Thx! for any help and suggestions
View Replies
Dec 29, 2006
I have what is probably a simple question for regular Access users. Before I ask the question, here is a simplified version of the tables involved:
Structure of EMPLOYEE
=====================
EmplID
Name
Structure of TRAINING
=====================
EmplID
CourseID
CourseName
DateTaken
Data in EMPLOYEE
================
EmplID----Name
1234------John Smith
5678------Mike Smith
Data in TRAINING
================
EmplID------CourseID----Course------DateTaken
1234--------NS01--------HeatStreet--06/15/2000
1234--------NS01--------HeatStreet--07/11/2001
1234--------NS01--------HeatStreet--02/07/2002
1234--------NS01--------HeatStreet--08/22/2004
1234--------NS01--------HeatStreet--01/28/2006
1234--------NS27--------Lockout-----01/06/2002
1234--------NS27--------Lockout-----01/27/2004
5678--------NS01--------HeatStreet--12/27/2002
5678--------NS01--------HeatStreet--08/11/2004
Its easy enough to join the two tables in the query and return ALL 9 training records, but I would like to find a way to display only the most recent occurance of each employee taking a course. ie, the max date for each unique EmplID-Name-CourseID-Course combination:
EmplID------Name--------CourseID----Course------DateTaken
1234--------John Smith--NS01--------HeatStreet--01/28/2006
1234--------John Smith--NS27--------Lockout-----01/27/2004
5678--------Mike Smith--NS01--------HeatStreet--08/11/2004
I thought maybe "GroupBy" on EmplID, Name, CourseID and Course and "Max" on DateTaken would do the trick, but apparently not. Is there a way to do this without code?
Thanks in advance........ :)
-SD
View 5 Replies
View Related
Jul 11, 2013
Im trying to run the following calculated query:
Total Value on order: Sum([Purchase Price])
I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function
View 2 Replies
View Related
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
