I have 3 fields in query. I want the highest value from field2 in field3. Shown as below: How I do this without using Group By?
field1 field2 field3
1 1 2
1 2 2
2 1 3
2 2 3
2 3 3
Hi,
I am using this sql query : -
SELECT Monthly_below_report.Device
FROM Monthly_below_report
GROUP BY Monthly_below_report.Device, Monthly_below_report.[FS-ID]
HAVING (((Monthly_below_report.[FS-ID])=780));
The problem is this currently pulls out 5 records (all devices found and the amount of times they appear).
How can i get it to pull out JUST the highest count.
I.E :-
that query above will pull out
15
12
10
3
1
How can i get it just to display 15.
Thanks
k0r54
I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables
tblItem
tblFormula
tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
(I am a notive in database calculations) I have a human resources database with a report that calculates how long a person has been employed and calculates vacation and sick time based on the number of years employed. Is there a way to take the single highest value in vacation time text box and multiply it by a predetermined number? (i.e., if 5 is the highest number in the list for the person, multiply it by 24)
Thanks in advance.
question,
how do i select the highest value of a column in a MS ACCESS database with a sql statement???
thnx
Hello,
I am looking for a query to select the second highest value (in this case a date) from a table.
For example, if I had the following dates:
9/1/06
9/2/06
9/3/06
9/4/06
I would want the results of my query to be 9/3/06.
My table is called tblEquityFunds and the field I'm looking to select is called Added. I have the following query, which I think works, but it seems like an awfully goofy way to do it:
SELECT DISTINCT TOP 2 Max(tblEquityFunds.Added) AS MaxOfAdded
FROM tblEquityFunds
WHERE tblEquityFunds.Added Not In (Select Max(Added) FROM tblEquityFunds))
Is there a more elegant solution?
I'm trying to identify the 2nd highest number in a field in Access. While searching, I ran across the =large(a1:a3,2) formula, which works perfectly in Excel. I'm trying to duplicate this functionality in Access 2003, but it looks like this formula isn't in there. Any ideas on how to accomplish this?
View 5 Replies View RelatedHello,
I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:
14th Feb
10th Dec
1st Jan
I don't want it to return 10th Dec, I want it to return 14th Feb
Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!
Thanks!
using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.
View 2 Replies View RelatedI have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 Replies View RelatedI'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
Thanks.
Gayle Ann
This database is for a livestock show that I have been working on for quite some time now. This specific 'section' is for the Supreme Competitor award, in which points are given for the showman's placing in showmanship, ONE highest placing animal in market classes, ONE highest placing animal in breeding classes, and the showman's score on a quiz. I'm having a problem assigning 'points' for a single highest placing animal in market and breeding classes.
I would like to create a query/s that selects all animals shown by an exhibitor in a market class, then take the highest placing animal that the exhibitor had and award (update the livestock table-points field) points for a single animal. This is fine until one exhibitor has MORE than one animal that received 1st place. How do I go about telling the query to just pick one, lol... it doesn't matter which 1st place animal it selects to award the points... just as long as only ONE animal is updated and not all of the exhibitor's animals that received 1st... Then do this again to select one highest placing animal from the breeding classes.
Here's a little outline just for visual sake:
Market Classes
Name Tag No Class Placing <points field update>
Sally Johnson 100 1 1st 10
Sally Johnson 101 2 1st
Sally Johnson 102 3 1st
Kim Smith 200 1 2nd 5
Kim Smith 201 4 2nd
Kim Smith 202 5 3rd
See where Sally received 3 1st places, but only one of them is selected to be updated, and Kim received 2 2nd places (her highest placing) and only one is updated.
Thanks SO much in advance... this has really got me stumped.
I have 4 columns
PostCode
District
District Name
Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))
Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.
Hello Everyone,
I am in need of your vast array of knowledge and experience. I have been fighting with this Access report for a few days now, and I've finally decided to ask for your help.
I am running a report which provides a look at a set of 12 survey results. The 12 items are numerical in nature and are decimals with 2 decimal places. What I am trying to do is have Access automatically highlight the top 3 scores in one color, and the bottom 3 scores in another color.
So what I need is an expression that allows Access to discern the top 3 and bottom 3 from the series of 12 scores. in Excel you can identify the top score with this with the formula "Max(A1:A13)", but seeing as in Access we don't have the cell identifiers, this approach will not work.
I know that I will need to use conditional formatting in order to get the cells to highlight. I also see that there is "Max" and "Min" functions in Access as well, and I attempted to create and expression like this "=Max([field 1] AND [field 2] AND [field 3] AND, etc...)" with no luck. I also tried changing the "AND" to "OR", again without luck.
One thing that I should mention is that the report does contain a lot of other data, so I need to be able to apply whatever the best fix is, to only those 12 fields.
Unfortunately I am not accustomed to using code, and as a result I am unsure of how to input it properly. I am certainly open to code solutions, but I would also need a explanation of how to put it in properly.
The easiest solution for me, if possible, would be an expression that I can enter into the conditional formatting wizard to tell it to highlight the values.
If anyone has any ideas, I would appreciate it. I'll buy you a virtual beer :-)
I need finding the First to fourth highest numbers in four columns.
For Example:
Student Name Subject1 Subject2 Subject3 Subject4
Max 60 40 50 10
I need query to find the first, second, third and fourth highest marks in subjects.
I have a database in which the year is stored in a table called year with as laid out below
"YearID" autonumber field and the
"Year" field which is a date field.
I am trying to search for records via a query for just the current year, i.e. the highest autonumber and I am having no success.
I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
I want to have a report card where I can have attendance embedded in for each students and also the highest score for each class each session.
View 4 Replies View RelatedWhat is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
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.
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?