TOP "N" Records By Group
May 11, 2005
I have a query that has the following...
AdvisorName Sales Class
Bill Jones $50000 Vetran
Tim Taylor $46000 Vetran
Mr Grumpy $10000 Roockie
Sid Vicous $90000 Rookie
The end result I'd like to show..
AdvisorName Sales Class
Bill Jones $50000 Vetran
Mr Grumpy $10000 Roockie
The result being the "Top" (1 or 5...) by group (Vetran or Rookie...)
I know how to do top values but not by group.
Any pointers in the right direction would be greatly appreciated.
Thank you :confused:
View Replies
ADVERTISEMENT
Dec 4, 2005
Hi,
I've got a database containing three tables:
=====================
tblEvent:
---------------------------
Id (PK) | Event_Title | Event_Price
=====================
=====================
tblDelegate:
---------------------------
Id (PK) | Delegate_Name
=====================
=====================
tblBookings:
---------------------------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)
=====================
I need to output an HTML table with the following headings:
==============================================
Event Title | Event Price | No. Delegates Booked | Total Price
==============================================
How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.
Does this make sense?
Any help would be much appreciated, thanks!
View 6 Replies
View Related
Oct 29, 2012
We're developing a database to manage tournament registrants as well as event results.
The key tables we have ar:
ContactsRegistrationsRegistration DetailsTournamentsAgeGroupsBreakingAgeGroupsExperienceGroupsEventsPayments
There are queries for using information in the tables to determine age, competition divisions etc.
At the moment the RegistrationExtended query, which uses the ContactsExtended and TournamentsExtended queries and the Registrations table to determine the number of events for each registrant as well as what division and age group they are assigned. This is done with some IIF expressions.
For the 2012 event we had 4 division classes based on experience and 6 age groups. For 2013 there might be more division classes and age groups. So now I have the tables AgeGroups, BreakingAgeGroups, and ExperienceGroups. The two age group tables have a query that calculates the age group name based on the values in the MinAge and MaxAge fields. For example 8 & Under, 9-11, 12-14 etc. The ExperienceGroups also have a minimum and a maximum field but only one field will be completed for each experience level. If the value is a maximum then that would be for those with less than the maximum experience and if the value was a minimum then that would be for those with minimum of that many years of experience. For example Grasshopper for less than 1 yearWarrior for those with 1 year to less than 2.5 yearsSamurai for those with 2.5 years and less than 4 yearsShogun for those with 4 or more years.
We want to maintain the history so the age groups and experience groups are now tied to the tournament.
I'm think that I'll need some VBA code that will compare the registrant's tournament age to the ranges assigned to that tournament and will assign them to the correct age groups. Then there will be code to compare the registrant's experience and assign them to the correct division class. The key here is that each year might have a different number of age groups and experience groups as well as different experience group names. For instance in 2013 we may change the 8& Under group to 7-8 and add a 6 & Under group and we may add another level called Emperor for those with 20 years or more experience.
View 12 Replies
View Related
Feb 9, 2015
how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
TomJones 1978 .465 56
TomJones 1979 .165 85
View 9 Replies
View Related
Apr 24, 2006
is there a way to count - in one query - the number of records that fall between specific times:
Time CountOfRecords
0000-0100 7
0100-0200 2
etc
2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).
View 2 Replies
View Related
Sep 6, 2013
I have a table which holds parts sales data for 6 individual dealers, and I basically want to be able to produce a report which details the top 10 selling products by volume by group. I can produce a query which gives me the top 10 results of ALL of my groups accumulated, but really need this to be split by group, and would prefer not to create 6 different queries to obtain the data.
My table (tbl_Part Sales by Dealer) has the following fields:
Dealer Code
Part
Description
Volume
Sales
So I'd like the results to show the top Part sales by Volume, for each of the (6) Dealer Codes within the table.
View 1 Replies
View Related
Aug 3, 2012
I would just like to know if it is possible to group Months together: this is what i have did so far :
Code:
SELECT Count(Source.ID) AS CountOfID, Clusters.Cluster_Desc, Department.Dept_Desc, DatePart('m',Source.Day_Month_Year) AS [Month], Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action
FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID
GROUP BY Clusters.Cluster_Desc, Department.Dept_Desc, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Day_Month_Year;
View 1 Replies
View Related
Nov 14, 2012
my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message Which Reads: Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0
View 6 Replies
View Related
Sep 3, 2004
I need to write a report with the following groupings
Customer_GroupHeader
State_Groupheader
Client_Groupheader
Detail Section
Client_Groupfooter
State_Groupfooter
Customer_Groupfooter
What I have noticed, I maybe wrong, the record is a available for the "group_header" section closest to the detai section, in my case, "Client_GroupHeader". However, I need information in the record to format goup header information in the "Customer_Groupheader" and "State_Groupheader. Can anyone help me with this.
thank you
newbie and learning
View 1 Replies
View Related
Sep 26, 2013
I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.
Item Cost Basis Group Cost
1HF20812 1HF208 6.17
1HF20816 1HF208 8.63
1HF20820 1HF208 9.44
Here is the davg string I am trying to use.
Group: davg("Cost","Cost Basis Group")
View 2 Replies
View Related
Aug 5, 2014
I've got a table, where each record is a group of students. So the fields include Student1, Student2, Student3, etc. I know this is bad database design, but I've done this because I want the group number (primary key) to be automatically generated.
Now, I want to add more information for each of these students as if they're seperate records but I also want an autonumber for the group.
View 5 Replies
View Related
Aug 15, 2013
I am trying to email a group of records in a table and with the code I have written, it just loops to the first record in the recordset and will not move down to the next record. Below is my code.
Code:
Private Sub TestOutlook()
Dim db As Database
Dim rstMail As Recordset
Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
[Code] ....
View 6 Replies
View Related
Jun 2, 2013
I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items
I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.
After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.
Is that possible?
View 2 Replies
View Related
Oct 28, 2014
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator
FROM MatterContactsMade
GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator
HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator]))
ORDER BY MatterContactsMade.MatterId;
View 1 Replies
View Related
Aug 1, 2013
I have a report that is group based on a number refered to as "Line". I want to only have three records per group. How can I do this?
View 5 Replies
View Related
Jun 18, 2012
I'm having a problem trying to have my query place the group record count on my form. The scenario is this: I have the query name (SPED Main Query Count) group the School Name field and then count the school name which in turn gives me a count of each school. but for some reason the field in the table name School Cnt will not build a relationship with the Countofschool Name in the query in my relationship.
View 13 Replies
View Related
Mar 28, 2013
I have a report I am grouping by a category, and I want to put the total number of records in that category in a textbox in the group footer.
How would I go about doing this? I have searched high and low with no avail...
View 2 Replies
View Related
Aug 8, 2013
I'm developing a database where people basically can fill in an order form and then the database can track if the order has been placed, when it came in etc.I have a form called frm_Suppliers to be Ordered that runs off a query. The tables that relate to the query are called tbl_Products, with a primary key called ID and tbl_Suppliers, with a primary key called Supplier ID.
The form called frm_Suppliers to be Ordered, shows a list of suppliers that have open orders. Each supplier may have several orders open. I want to be able to click a button after each supplier name and bring up a continuous form that relates to all the records from that supplier that have open orders.
I have tried using the code found in the thread called "open a form based on the record that is selected in current form" from 06-08-2011, however it will only work when my form lists each open order separately, not when they are grouped by supplier.
View 2 Replies
View Related
Jan 17, 2013
I have a table (table1)that table has several columns: company / Qty / date / ERT...I want to be able to run a query that will group all the records by Company and by date and provide the sum of QTY - as well as the cumulative totals for each company and date.
My query looks like this:
amonth: MONTH CODE
Group By
assending
Field 1: Alias: Company
Group by
Ascending
Field 2: QTY
Table1
Sum
Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]=" & [alias] & "")..I have the query set up - but my runtotal Dsum formula is giving me grief - specifically a #ERROR and I can't figure it out..I basically want it to look like this:
A company 15/Nov/2012 10 10
A Company 16/Nov/2012 10 20
B Company 12/Jan/2012 15 15
B Company 21/Mar/2012 5 20
View 3 Replies
View Related
Sep 20, 2014
I have a report which gives me a count of records found for each group
group 1 - 10
group 2 - 13
group 4 - 82
what i want is a total below this - ive looked at calculated controls however cant seem to get it what soever - I've tried likes of =sum([counts])
View 1 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Jul 25, 2013
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
View 1 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Mar 12, 2007
I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?
p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???
View 5 Replies
View Related
Mar 22, 2008
I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...
I think maybe I should upload the database, let me know.
Here are the names:
Table name: PPM Data
Fields:
Company
PPM Month
PPM Amount
There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.
I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.
View 6 Replies
View Related
