Queries :: Numbering Records By Group In A Query
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 Replies
ADVERTISEMENT
Aug 11, 2005
Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.
I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.
The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers
IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:
SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
AND
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);
What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:
tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311
I would like to do something like this:
tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1
Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....
Any help would be appreciated
View 3 Replies
View Related
Jan 27, 2006
I need to add a column to a sorted query which effectively numbers from 1 to N. It is intended as a ranking field for later statistical analysis.
I can do this manually by saving the query as a table, then introducing a new autonumber field.
However, I need to do this automatically, as this is just one query out of many in a large and complex setup. Is it possible to add an autonumber field to a query?
View 2 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
Feb 6, 2014
I've taken on the task of transitioning excel reports (and their format) to a database. One report summarizes all the parts of a piece of furniture, and the order of the machines each part goes to.It looks similar to this:
Part# Part Name Machines/Operations
M7264Top Panel 112114116120121325216
M7265Under Top Panel 112114116120121
M7266Base Assembly 411325216311310312316
The table this comes from has both operations and setups, so I used a query to filter only the operations.The query (qryOperations) result looks like this:
PART NUMBEROPERATION ORDER
M7264 112 2
M7264 114 4
M7264 116 6
M7264 120 7
M7264 121 8
M7264 325 9
M7264 216 10
M7265 112 2
M7265 114 4
M7265 116 6
M7265 120 8
M7265 121 9
i need to add sequential numbering (1,2,3,4...) to each line of the qryOperations and use the sequential numbering as the column header.How do I add sequential numbers to the query, that restart after each change in part number? I can do this in a report easy enough, but not a query.This is the SQL of the query I need to have the sequential numbers in...
SELECT tblparts.[PART NUMBER], tblOperationCodes.MachineCode, tblOperationsList.Order
FROM tblparts INNER JOIN (tblOperationsList INNER JOIN tblOperationCodes ON tblOperationsList.OperationCode = tblOperationCodes.OperationCode) ON tblparts.[PART NUMBER] = tblOperationsList.[PART#]
WHERE (((tblOperationCodes.Function)="O"))
ORDER BY tblparts.[PART NUMBER], tblOperationsList.Order;
View 5 Replies
View Related
Jun 5, 2013
I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.
View 3 Replies
View Related
Aug 5, 2013
I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:
Code:
Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1
[Code] .....
I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.
I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by
Subquery 2:
Code:
SELECT
CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total],
CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax],
[Line Total With Tax]-[Line Total] AS [Line Tax],
[Inventory Transactions].*
[Code] .....
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
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
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
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
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
May 11, 2005
Hi I obviously have a table with records in it sorted in assending order. What I need is to know how to make it add into one of the table fields the number it is in the table. IE
Name Time Position Number
Mr Jones - 12:30 - 1
Mr Evans - 12:45 - 2
So if i added 12:35 it would change mr evans position number to 3 and make the new record position number 2.
View 5 Replies
View Related
Mar 23, 2006
Dear All,
Maybe my question is not too difficult.
I have a form-subform structure and my aim is to number certain records of the subform. These records would contain value: 1,2,...
And when changing the record on the main form, the subform's record should have the values 1,2,... again.
(These values are shown on the bottom of the subform, where we can step the subform's record.)
If I use an autonumber field, the numbering goes continously.
Please help me!
Thanks you in advance.
Sz.Cs.
View 2 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
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
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
Jul 4, 2013
I have a table with two fields like this:
F1 F2
110 1
110 1
111 1
111 1
111 1
112 1
112 1
.....
I need to change the number in F2 like this
F1 F2
110 1
110 2
111 1
111 2
111 3
112 1
112 2
.....
View 5 Replies
View Related
Oct 16, 2013
I would like to know how to code a report to add row numbering automatically?
View 6 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
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
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
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
Jun 26, 2015
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890
ABE12345
ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
View 3 Replies
View Related
