Queries :: Top N For Each Combination Based On Group Averages
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.
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?
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.
I have two forms - Main and StudentAdvisor (filtered by student ID#) SAdvisor.jpg
The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.
On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).
The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)
Advisor Type 1=Initial, 2=Current, 3=Previous Changed 1=Yes, 2=N/A
so a record would record as: Initial and N/A Current and Yes Previous and Yes
I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.
also tried pulling form SA = Advisor to form Main = txtAdvisor
For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code: SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num] FROM LIST_WITH_TNC;
Is it possible to do combination of these two queries?
SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre] = Forms!frmWycenyObszarroboczy!ID_wycena_pre[/CODE]
and
Code: SELECT [Query1].[NumerArkusza], [Query1].[nazwa], [Query1].[id_wycena_pre] FROM Query1 WHERE ((([Query1].[numerarkusza]) In (SELECT [numerarkusza] FROM [Query1] As Tmp GROUP BY [numerarkusza] HAVING Count(*)>1 )));
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:
The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.
My Query for picking 1 of the 3 looks like this:
Field: Manager Employee Job Function Criteria: David Or: John Busy Work
When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:
David John
Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function).
Code: ID studentname lectureplace lecturesubject 1 jack A Biology 2 steve A politics 3 jack B math 4 steve A math 5 jack B politics 6 joe A politics . . . . . . . . 154 Jane B Geography
We need a query to implement two criteria to extract the following records:
1-Show lectureplace A AND politics lecturesubject 2-Show only those with lectureplace B regardless of the subject
In case the query is run we will have the following result:
Code: ID studentname lectureplace lecturesubject 2 steve A politics 3 jack B math 5 jack B politics 6 joe A politics
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix, IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
If it possible to change if a group is visible based on what page a different group is printed on.
The report I'm working on is a Invoice and it has subtotals in one group and totals in another group.
If the totals are going to print on page 1 then there is no need to print the subtotals. Is there a way to know what page the totals will print on so I can set visible for subtotal group to true or false?
My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.
In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form Field: Option group Value = 1 Value = 2 Value = 3 Field 2: Field 3: Field 4: and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
In my database, I have clients and client hours. I need to calculate average client hours. My total client hours expression is: TTotals: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted]))). I need to divide this number by the number of clients. Any suggestions ie sum of client number, sum of clients, count of clients. I've tried these and the results are incorrect.
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
Now there will be many patterns, the above however only contains one (called 1). Now what i need is the average Depth, Subdrill, Burden, Spacing, bcm/h. In MS Excel I would have following:
Average BCM per hole = Sum of BCM / Sum of Holes
How to do this in Access? Then just to make it more difficult I want to have the average BCM for each different pattern.
once again I have a problem for which I am looking for some hints...
I still have one table, called tblTransactions, which contains security market transactions. For each buy and sell order, respectively it contains one data set with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following code gives me all stocks, which are no longer part of the portfolio since they have been sold out completely for any arbitrarily chosen date (here 1/30/07) together with the date, on which the last position in a certain stock (identified by the ticker) were sold:
SELECT T.Ticker, max(T.Date) AS SellDate FROM tblTransactions AS T WHERE T.Date<=#1/30/2007# GROUP BY T.Ticker HAVING sum(T.Qty) =0 ORDER BY T.Ticker;
Now it becomes complicated: What I am looking for is a sub-query, which I want to add to the code above and which gives me the weighted average price at which the stocks were bought and sold, respectively if there have been more than one buy or sell transaction.
That is, for the following sample data of tblTransactions...
My database has 8 clients. During a sample date range, between 1/1/05 and 1/1/11, they worked a total of 348 hours. I need to query them for hours divided by client by date range. 3 clients, for instance, worked a total of 162 in the sample date range but the query is dividing the 162 hours worked by all 8 clients. I need it to divide the hours in this case by 3. Needless to say, these numbers will change when a different date range is inputted but if the expressions are correct... Currently, to calculate this number I am using this expression : Averages: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted])))/DCount("IDOC","spise clients_OLD_OLD"). A copy of the query 'Current Average/Total' is attached.
