Queries :: Top N Per Group
Jun 19, 2014
I have been trying endlessly to get this working but to no avail. I have looked and used sample codes posted on the net.
I am trying to get top 1 [route_list_code] per [works_order_number]
I have attached three images:
- Query as isQL code
- Query with top n in criteria (pulling wrong information and its just by coincidence that the two records shown are split by group)
The code appears to just pull top # record (not per group)
as is.JPG
SQL code.JPG
query with top n in criteria.JPG
View Replies
ADVERTISEMENT
Jul 21, 2013
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
View 11 Replies
View Related
Mar 8, 2012
I have moved from Access 2003 to 2010. In Access 2003 I was able to create query groups that contained shortcuts to queries in the main database. This worked great for us because we did not want to create user interfaces that required support- but we could have multiple queries in a database and have users look only in their query group.
The database I am using was created in 2003 and had those groups. Is there a way to view, edit and create a similar view in 2010?
View 3 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
Nov 25, 2014
I have used Allen Browne's suggested method of ranking to rank by the date each client settled... Now I need to group the rankings by the referring company as below... so it ranks each client referred by referral company from earliest to latest - like so:
ReferrerID___ClientID___TotalValue___BeatenBy (Ranking)
Intro1_______CatCo_____6/11/14________4
Intro1_______DogCo_____8/3/14_________1
Intro1_______FishCo_____17/5/14________3
Intro2_______AntCo_____11/1/14________2
Intro1_______PigCo______2/4/14_________2
Intro2_______BirdCo_____12/8/14________3
Intro2_______FrogCo_____6/1/14________1
My current SQL view of my query looks like this:
SELECT qryDates.ClientID,
qryDates.TotalValue,
(SELECT Count([ClientID])
AS HowMany
FROM qryDates AS Dupe
[Code] ....
View 5 Replies
View Related
Mar 23, 2013
I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.
tblVersions
------------
VersionID (PK)
SOPID(FK)
Version (integer)
IssueDate (date)
I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.
One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:
Code:
SELECT Last(tblTest.VersionID) AS LastOfVersionID
FROM tblTest
WHERE (((tblTest.IssueDate)<Date()-10))
GROUP BY tblTest.SOPID;
The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.
Query3
Code:
LastOfVersionID
100
90
20
40
666
888
55
8
Update:
Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)
View 4 Replies
View Related
Sep 15, 2014
I have data like so:
Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc
And need to return a running count for the number of visits per subject, so:
Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.
I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:
VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])
View 4 Replies
View Related
Oct 18, 2014
MAX ID/date. I need to get TableB to TableA without using Group by.
Code:
TableA:
ID name1 name2 dt
1 n1A n2A 1/1/2010
2 n1A n2B 1/1/2010
3 n1C n2C 3/12/2011
4 n1D n2D 5/03/2013
5 n1D n2E 6/7/2014
6 n1D n2F 8/12/2011
.
.
Code:
TableB:
ID name1 name2 dt
2 n1A n2B 1/1/2010
3 n1C n2C 3/12/2011
5 n1D n2E 6/7/2014
etc
View 3 Replies
View Related
Apr 8, 2014
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies
View Related
Jan 15, 2014
I have a table that looks something like this in structure:
P_date (date)
Location_No (integer)
Invoice_type_A (integer)
Invoice_type_B (integer)
Invoice_type_C (integer)
Invoice_type_D (integer)
I want to create a query which shows the sum of the number of all four invoice types for a specific date regardless of the Location_No. By putting something like "Invoices_total: Invoice_type_A + Invoice_type_B + Invoice_type_C + Invoice_type_D" in the top field I am able to get the query to show me the total number of invoices per date AND location, but I would like the total number of invoices for a given date regardless of location number. How can this be done?
View 3 Replies
View Related
Aug 10, 2014
I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
View 1 Replies
View Related
Jul 6, 2013
I have 2 tables. tblOrders and tblOrdersItems. tblOrdersItems is the child and tbl Orders is the parent. the linking field is OrderID.
I am wanting to count all the OrderID in tblOrders. the criteria i need is EmployeeListID which is stored in tblOrdersItems.
When I put these tables into query design, add the criteria, grouping(see below)
Code:
SELECT tblOrders.OrderID
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeList.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;
I get (record count at bottom of query screen) 87 records. then i add a field to give me the count so i can then use it i get many more.
How to get the count working. i have uploaded the relevant tbl and my attempted query.
View 9 Replies
View Related
May 9, 2013
I try this code
Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;
But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"
and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google
I get the warning message that resemble with above message
and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.
View 3 Replies
View Related
May 6, 2015
I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.
View 1 Replies
View Related
Aug 5, 2013
I have a Union Query which the result was like this:
Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00
Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.
View 5 Replies
View Related
Oct 24, 2007
I have a table that looks like this:
caseID
date
value
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc
caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
Any ideas??
View 3 Replies
View Related
Sep 2, 2013
I have a table with 4 columns :
Product Price Quantity Supplier
Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A
Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
View 2 Replies
View Related
Jun 4, 2015
I have a table similar to the following
area q30 q40 q60
A 2 1 3
B 1 1 3
A 2 1 3
A 1 1 3
C 1 1 3
C 1 1 3
A 1 1 3
A 1 1 3
B 1 1 3
C 1 2 3
A 1 1 3
B 1 2 3
Grouping by the area and using the criteria
(Q30 = 1 AND Q40 = 1 AND (Q60 = 1 OR 2 OR 3))
I would like to see a table similar to the one below counting those which fulfils the criteria mentioned above
Area COUNT
A 4
B 2
C 2
View 2 Replies
View Related
Jul 17, 2014
I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:
SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));
View 4 Replies
View Related
Nov 7, 2014
I need to group data (cases) in range like this (0-5, 6-10, 11-15 etc).
I can do this with the iif statement but this is a bit tedious.
I have two simple table:
1. Salestbl with two fields: a. customerid, b. Cases
2. Rangetbl with two fields: a. Range b. Category
The rangetbl is where i would define my range and it look like this
the range field have records 0,6,11 with corresponding category which look like this '0-5', '6-10', '11-15'
How can I use this to group my data. that is customer with x cases have group '0-5' or '6-10' etc....
View 3 Replies
View Related
Jan 15, 2015
I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.
Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.
View 4 Replies
View Related
Feb 2, 2015
I have an access query named "leaveapp" and I want a running count as below:
EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1
and i need a column in the right with running count like below
EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1
View 3 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
Jul 16, 2014
I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.
I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.
I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.
Basically, what this does is show me the average profitablity of the top 10% in each grouping.
WaterSourceType | Crop | ProfitPerBushel
Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel
View 9 Replies
View Related
Mar 26, 2014
I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.I need to count each of these entries for a report which will run them into another formula.I am trying to use the below - obv I'm doing something wrong.
Code:
Police: Sum(IIf([OType],1,0))
And this is the original SQL for my queries if needed:
qry_BLT_RCCounts1
Code:
SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
[code]....
View 5 Replies
View Related
Jul 21, 2015
I am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
View 3 Replies
View Related
