Queries :: Query Group By And Display Other Columns
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.
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?
I have 30+ columns each table I need to change into sum of.. but its default is group by, and need to change multiple columns to sum - rather than manually each one..
I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])
My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).
2013 March 9 2013 March 10 2013 March 11 2013 March 12 2013 March 13 2013 March 14 2013 April 14
Rank # Name Div Sex Score 1 8 Bird Twitty 52Kg Female 684.69 2 12 du Toit Carin 52Kg Female 608.77 3 13 Colbert Jackie 52Kg Female 607.62 4 11 Wade Jenna 52Kg Female 595.41 1 7 DeDee Jenna 67.5Kg Female 702.27 2 10 Diva Meisie 67.5Kg Female 550.46 1 5 Man Super 70Kg Male 655.09 1 9 Doe John 82.5Kg Male 480.3 1 3 Man Spider 90Kg Male 537.63 1 6 America Mr 100Kg Male 406.42 1 4 Hulk Incred 110Kg Male 645.8
SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.ScoreTotal > History.ScoreTotal) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Class.Description AS CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotal FROM Class, Lifter, Team, History, Meet, Activity WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND History.ScoreTotal > 0 AND Class.uidClass = Lifter.uidClassRef AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.uidLifter = History.uidLifterRef AND Team.uidMeetRef = Meet.uidMeet AND Lifter.Gender = Activity.Code GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreC, Lifter.Gender, Activity.Description, Activity.Activity, Lifter.LifterStatus, History.ScoreTotal HAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0) ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, History.ScoreTotal DESC
I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division. I know that I need a select in the WHERE part but how?
For whatever reason when I try to add up two columns in a query instead of adding up the two numbers it displays it as a text string. So if one column has a 5 and the other is a 2 I am looking for the calculated column to reflect 7, currently it is showing 5,2.
I have added up items in the past so I am unsure what the problem is
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
Folks, I need your help on GROUP BY clause. I have been struggling to get his report in a way I wanted for some time now. Here's what I like to do:
I am writing some project related data into a temp table (tblAppendForRskTblReport) by clicking a command button on a form. The data in tblAppendForRskTblReport looks as shown in the excel file tblFile1.xls (attached).
I wanted to make a report in Access 2003 which looks like another excel file rptFile2.xls(attached). As noticed, I wanted group the records into two main groups based on column "fCurrentlyOffered". Then for one group which reads "Yes" (column "fCurrentlyOffered") divided into several sub-groups based on column "chrCategoryID1" like Category A, category B, ....
I tried to use the following query:
SELECT A.chrProjectName, A.chrAqcsPropNum, A.memGuranItem, A.memFuel, A.memOpertgCondn, A.memPred, A.memGuar, A.memMargin, A.memCustReqst, A.memPermitValues, A.memRiskLevel, A.memLDs, A.memBasis, A.memCorrectAction, A.memComments, A.intCategoryRowID, A.chrCategoryID2, A.fCurrentlyOffered, Count(*) AS RecordCount FROM tblAppendForRskTblReport AS A GROUP BY A.chrCategoryID1, A.fCurrentlyOffered;
I could get a report based on column "chrCategoryID1" only (see attached sample excel file rptFile3.xls), but don’t know how to separate the rows (as new group called ‘Not Offered’) which reads 'No' under the column "fCurrentlyOffered".
Any attempt I make by modifying the above SQL leads to an error msg as follows:
"You tried to execute a query that does not include the specified expression 'chrProjectName' as part of an aggregate funtion."
I have Count(*) as an aggregate funtion in my SQL but I don't know why it's still complaining?
My question is: can I get the report as I mentioned above (as shown on rptFile2.exls) using SQL or do I need to handle this via VBA?
Can someone help me on this or offer any tips/idea?
PS: Some of the column data may looks like redundant info (normalization), but they are purposely replaced with 'deleted' or 'N/A' words for privacy.
This is the table: ID | Item | 2/2/07 | 3/2/07 | 5/3/07 | 7/3/07 --------------------------------------------------------- 101Tap10152010 102Ply25201010
Note that 2/2/07 till 7/3/07 are field names having purchase quantity.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Code: Function CohortQ(InputDate As Date) As Integer If InputDate = 0 Then CohortQ = 0 Exit Function End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows. If I put the function into the query,
I have would like to have a option group requery a list box with 2 yes/no columns as the criteria.
First Option = No criteria Second Option = Yes in first column only Third Option = Yes in second column only
So far I've been able to get option 1 and 2 to work, but I've not been able to define the same value to the third option, some of the values in the 2 columns over lap so using a single option doesn't seem to be the right method.
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.
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.
I run a query with several columns. The first column in that query is full of individual names; about 50. I have another spreadsheet with three names. I used to be able to set a criteria or a filter (whatever you want to call it) in a query that when "Run" would only return to me the names from the spreadsheet with 3. Not a drop box or a pop up where I have to fill out the name, just a spreadsheet with all the transactions that occurred with these specific three individuals.
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 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
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.
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.
