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 RepliesI have several records to search for. I need to select just 1 record for each Tkt_no group using Tkt_id (max no).
Example, I only want to select
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
114problem 1Done
210problem 2Done00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
from below data
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
14problem 1Ongoing00/01/1900 20:00
114problem 1Done
25problem 2Ongoing00/01/1900 14:30
26problem 2Ongoing00/01/1900 11:30
27problem 2Ongoing00/01/1900 10:15
28problem 2Ongoing00/01/1900 15:00
29problem 2Ongoing00/01/1900 8:00
210problem 2Done00/01/1900 20:00
31problem 3Ongoing00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
Any ideas?
Appreciate the feedbacks.
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 RelatedI 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.
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.
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]));
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.
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
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]....
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?
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?
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.
I am trying to group the following Query to find duplicates with no success.
Base Query:
SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));
Find Duplicates Query:
SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..
So far this thread has been very useful: [URL] ....
It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).
In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").
The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])
So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.
Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.
I used an option group to classify a record in my database. It works great for this purpose, but I am at the point now of creating a query, and I would like to query based on these classifications (or options). Is this possible to do?
Also, for the option buttons, the value is numeric. When I create the Query (or a report off of that query) I would like the actual text values to display, not the data value Access is using.
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
Hello,
I have several Option Group fields that are stored as Numeric with a datatype as Long Integer. I want to create a function that decodes these numbers into values (i.e 1 = open, 2 = in-work, 3 = shipped) and then use the function in a query.
Any guidance is appreciated.
Markw
My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.
When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;
I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'
All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.
I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?
I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".
The value being selected is a StoreID, This ID is matched against the AssetRegister to find all group names, The GroupID and GroupName are then retrieved from the AssetGroup table, then finally the list is grouped on the Group ID and Group Name.
I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.
Here is the SQL query:
Code:
SELECT AssetGroup.ID, AssetGroup.GroupName
FROM AssetGroup
INNER JOIN
(SELECT AssetRegister.AssetGroup, AssetRegister.Store
FROM AssetRegister
WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID)
INNER JOIN Store ON Store.ID = ar.Store
GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName
And here is the inline query applied to the RowSource:
Code:
AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _
& "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _
& "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName"
AssetGroup.Requery
I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
I have a query where I group by EmpID, so duplicates do not show up. In this query I have two fields with no data. These fields are to be filled in after the query is run in a form. However, these fields are not editable due to the group by feature. I tried two unbound fields, which does allow me to type, but doesn't save the changes once I click out of the field. The field then reverts back to blank.
View 10 Replies View RelatedI am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
View 14 Replies View RelatedQuery is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.