largest value of field in query output

I have a query that pulls all the records from a table.  For example,
the query outputs the following:

TITLE               REV              POSITION         DATE          
methodA     /    0           /       tech          /      july 5
methodA     /    1           /       tech          /      july 10
methodB     /    0           /       tech          /      july 5
methodB     /    1           /       tech          /      july 10
methodB     /    2           /       tech          /      july 12
methodC     /    0           /       tech          /      july 6
methodC     /    1           /       tech          /      july 9
methodC     /    2           /       tech          /      july 20
methodD     /    0           /       tech          /      july 5

I'd like the output to be:

TITLE               REV              POSITION         DATE          
methodA     /    1           /       tech          /      july 10
methodB     /    2           /       tech          /      july 12
methodC     /    2           /       tech          /      july 20
methodD     /    0           /       tech          /      july 5


Basically for a given method, I want to get the record containing the
largest value of REV.  How does one get only those records that
contain the largest value from that specific field?

I tried MAX and GROUP BY but they didn't seem to work.

thank you in advance

0
MrGQ
7/17/2007 11:12:05 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1169 Views

Similar Articles

[PageSpeed] 6

the group by function didn't work so let me start with the basic design of
the database to see if I'm on the right track.....

tbl_employee has [auto id] [lastname] [firstname] [title]
tbl_title has [auto id] [title]
tbl_procedure has [auto id] [procedure name] [procedure number] [revision
number] [revision date]
tbl_action has [auto id] [procedure ID number copied from procedure table
auto id] [action] [title]

* the concept is there are a number of procedures that are written and over
time get updated.  that is captured in the procedure table

* we have employees with various titles (directors, VPs, etc).  that data is
captured in the employee and title tables

* depending on your title, you are assigned a procedure and specific training
(for example- procedure 1 may be assigned to a VP and training is read only
yet a director may get classroom training).  This is hopfully captured in the
action table with linking to the title table and the procedure table.

AM I GOING IN THE RIGHT DIRECTION?




my next step with be relationships so that I can add a new procedure (or
revise an existing one) and assign each title with a specific type of
training


THANK YOU

-- 
Message posted via http://www.accessmonster.com

0
MrGQ
7/18/2007 11:04:03 AM
The Group By -> MAX won't work if you have anything in the line that makes 
the line qualify as a different grouping other than the field that you were 
doing MAX on...so, if you have different titles, etc, that would screw up the 
max function as well.

This may not be the "best" way to do it, but what I would do is write a 
query that just has two fields in it.

The two fields would be tbl_Employees.Auto_ID and tbl_Procedure.Revision_No.

Do a Group by Max on just those two fields.

Then use the result set to relink with the rest of the tables to get the 
other data points that you need.

The group by does work...it didn't work when you tried it because there were 
too many grouping in it...when I used your sample data set in the earlier 
post, the max worked just fine.

"MrGQ via AccessMonster.com" wrote:

> the group by function didn't work so let me start with the basic design of
> the database to see if I'm on the right track.....
> 
> tbl_employee has [auto id] [lastname] [firstname] [title]
> tbl_title has [auto id] [title]
> tbl_procedure has [auto id] [procedure name] [procedure number] [revision
> number] [revision date]
> tbl_action has [auto id] [procedure ID number copied from procedure table
> auto id] [action] [title]
> 
> * the concept is there are a number of procedures that are written and over
> time get updated.  that is captured in the procedure table
> 
> * we have employees with various titles (directors, VPs, etc).  that data is
> captured in the employee and title tables
> 
> * depending on your title, you are assigned a procedure and specific training
> (for example- procedure 1 may be assigned to a VP and training is read only
> yet a director may get classroom training).  This is hopfully captured in the
> action table with linking to the title table and the procedure table.
> 
> AM I GOING IN THE RIGHT DIRECTION?
> 
> 
> 
> 
> my next step with be relationships so that I can add a new procedure (or
> revise an existing one) and assign each title with a specific type of
> training
> 
> 
> THANK YOU
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
7/18/2007 4:06:01 PM
I didn't think that I was so uninformed when it came to Access but I'm
finding out how little I know !

I tried what you suggested but it did not work....I even went so far as to
create a new database and reducing the tables to only those fundamental items
(in a sense, normalizing the database I guess?)....but I can't get some
queries to work....I am also trying NOT TO use VB coding and instead using
the GUI and wizards built into Access.

Is there a way to post the database so that you can take a look at it?



DCPan wrote:
>The Group By -> MAX won't work if you have anything in the line that makes 
>the line qualify as a different grouping other than the field that you were 
>doing MAX on...so, if you have different titles, etc, that would screw up the 
>max function as well.
>
>This may not be the "best" way to do it, but what I would do is write a 
>query that just has two fields in it.
>
>The two fields would be tbl_Employees.Auto_ID and tbl_Procedure.Revision_No.
>
>Do a Group by Max on just those two fields.
>
>Then use the result set to relink with the rest of the tables to get the 
>other data points that you need.
>
>The group by does work...it didn't work when you tried it because there were 
>too many grouping in it...when I used your sample data set in the earlier 
>post, the max worked just fine.
>
>> the group by function didn't work so let me start with the basic design of
>> the database to see if I'm on the right track.....
>[quoted text clipped - 24 lines]
>> 
>> THANK YOU

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1

0
MrGQ
7/20/2007 8:33:56 PM
Here...just put the sample data in an access database and call it table1 with 
the columns of title (text), rev (number/long integer), title (text), and 
date (date/time)

So, 1st query is called query1 with the following SQL (just paste it in the 
SQL view)

SELECT Table1.Title, Max(Table1.Rev) AS MaxOfRev
FROM Table1
GROUP BY Table1.Title;


So, 2nd query use 1st query to relink with table 1

SELECT Table1.Title, Table1.Rev, Table1.Position, Table1.Date
FROM Query1 INNER JOIN Table1 ON (Query1.MaxOfRev = Table1.Rev) AND 
(Query1.Title = Table1.Title)
ORDER BY Table1.Title, Table1.Rev;

If you still can't get this to work, I can send you a zipped file of this, 
if you give me your e-mail address.


"MrGQ via AccessMonster.com" wrote:

> I didn't think that I was so uninformed when it came to Access but I'm
> finding out how little I know !
> 
> I tried what you suggested but it did not work....I even went so far as to
> create a new database and reducing the tables to only those fundamental items
> (in a sense, normalizing the database I guess?)....but I can't get some
> queries to work....I am also trying NOT TO use VB coding and instead using
> the GUI and wizards built into Access.
> 
> Is there a way to post the database so that you can take a look at it?
> 
> 
> 
> DCPan wrote:
> >The Group By -> MAX won't work if you have anything in the line that makes 
> >the line qualify as a different grouping other than the field that you were 
> >doing MAX on...so, if you have different titles, etc, that would screw up the 
> >max function as well.
> >
> >This may not be the "best" way to do it, but what I would do is write a 
> >query that just has two fields in it.
> >
> >The two fields would be tbl_Employees.Auto_ID and tbl_Procedure.Revision_No.
> >
> >Do a Group by Max on just those two fields.
> >
> >Then use the result set to relink with the rest of the tables to get the 
> >other data points that you need.
> >
> >The group by does work...it didn't work when you tried it because there were 
> >too many grouping in it...when I used your sample data set in the earlier 
> >post, the max worked just fine.
> >
> >> the group by function didn't work so let me start with the basic design of
> >> the database to see if I'm on the right track.....
> >[quoted text clipped - 24 lines]
> >> 
> >> THANK YOU
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1
> 
> 
0
Utf
7/20/2007 9:32:03 PM
Reply:

Similar Artilces:

assign formatted cell value to another cell
Hello everyone! If A1 has a floating point number for a date, e.g. 39647.25 for 2008-07-18 06:00:00 and I want to have that formatted string in a new cell - does anyone know a formula to apply to another column, so it puts the *formatted* value there, instead of the original number? I want the string "2008-07-18 06:00:00" as the value of my new cell, and I can't find a way to do that with a Range formula at the moment (I want to avoid looping over each row and doing it manually in VBA, for speed reasons). Thanks in advance! Lars =TEXT(A1,"yyyy-mm-dd hh:mm:ss&q...

Auto-populating a reference field
In the Purchasing module within the Payable Transaction Entry Distribution window, we have the ability to type a description of a transaction on the Distribution Reference field. Is there a way to automatically populate each entry with the vendor name and voucher number? Scott, There is nothing like this out of the box, you might need to write few VBA lines of code to have this done. Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "S...

Crosstab Queries VS Pivot Tables.
I am not entirely sure when to use one and not the other. I suppose Crosstabs should give you some more flexibility if you want for example the total as the first column. I prefer using CrossTabs for 2 reasons: 1. I can easily create a report based on one. 2. I can easily export the data to Excel. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Avid Fan" <me@privacy.net> wrote in message news:eTwtFbs3KHA.1016@TK2MSFTNGP02.phx.gbl... > > I am not entirely sure when to use one and not the o...

Frx
We just installed the Add-in that allows AA reporting to be done in FrX. I've successfully created a report which filters on specific AA values by column. However, some transactions were never assigned an AA code. How do I filter for those in a column? I've tried using wildcards up to that point of the account structure or putting spaces, but neither works. Thanks! ...

Need to Loop Through the values in Cells and Extract Parts
I have a file with a column with a cells in it that each have multiple values in each cell. So there may be 400 rows in the column and within each cell there may be 40 unique values in it. For example : cell A1 would have the following: a:37:{i:0;s:3:""500"";i:1;s:3:""503"";i:2;s:3:""506"";i:3;s: 3:""508"";i:4;s:3:""511"";i:5;s:3:""514"";i:6;s:3:""517"";i:7;s: 3:""519"";i:8;s:3:""522"";i:9;s:3:""525"...

Opening an excel file which has a query to external data
I have an access application which opens an excel file which has a query to that access application to import data. * I open the excel file when the access application is closed, i get the message "this workbook contains etc ... Enable automatic refresh". Until here everything works as expected and the data is refreshed fine. * When I open the excel file when the access application is opened i get the message that the db is opened exclusively by another user. This I understand ass well, but is it possible to resolve this? * Now the thing I'm looking for is: Is it possible to ...

Set Y-Axis max value to cell value without VBA?
I'm working on a home-grown pareto chart in my workbook; the data will change and I'd like to dynamically have the (primary) Y-Axis max match the sum of all values so that that it will synch with the secondary Y-axis which will always have my 0-100% of the total (line). Is there an easy way to force the primary Y-axis to have a maximum value matching a cell in my worksheet? Thanks, Keith You need VBA, but it's not terribly hard. Here's how (my site): http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html and here's a utility that handles the heavy lifting for yo...

Index fields and formatting
Hi I've inherited a document and some of the index fields - XE - are formatted as bold and I want to remove the formatting. I found a similar post going back to 2005 and the answer then was use paragraph formatting. I have Word 2003 and 2007 and just wondering if this is still the case. Also, is there a way to bold the text without bolding the index field. Bolding the line with fields switched off still bolds the field. Many thanks Ian To change the formatting applied to the XE (index entry) fields, you can make use of Find and Replace. Display the Find and Repla...

code wild card value for form variable
i use a check box in an option group to generate a specific value for a variable. using the following code If Forms![fee sched form]![select region] = 2 Then Forms![fee sched form]![region var] = "Roch" End If If Forms![fee sched form]![select region] = 3 Then Forms![fee sched form]![region var] = "CNY" End If this variable is hidden on the form and value is used a query. how do i code this, so i can put "*" in the variable and thereby get all values in the query? the query code generates like "*" but i do not know how to code via the fo...

Default Date Value
How do I get a record create date to automatically fill in on a field in that record as the default value? -- BigK9 In your table you could set the default value to the field as =Date() or set the default value of your field on the form to =Date() "BigK9" <BigK9@discussions.microsoft.com> wrote in message news:9B260C6E-2672-4E87-813F-1D15AE6CB806@microsoft.com... > How do I get a record create date to automatically fill in on a field in > that > record as the default value? > -- > BigK9 I recommend making it the default value of the field in the tabl...

year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...

Number to Text Value
Hey, I have data coming from Access via ODBC with a value of 0 (NO) or 1 (YES) from a tickbox in Access. Is there a way I can covert these 0 & 1's to a text word in excel in another column. 0 = NO or FALSE 1 = YES OR TRUE Thanks in Advance. "=?Utf-8?B?S1lNTw==?=" <KYMO@discussions.microsoft.com> wrote in news:C7FAFC78-67BA-4747-905D-497AF99727DB@microsoft.com: > Hey, > > I have data coming from Access via ODBC with a value of 0 (NO) or 1 > (YES) from a tickbox in Access. > > Is there a way I can covert these 0 & 1's to a text word ...

Notes field in Data Analysis
Hi, Is there any way whatsoever that I can get the text from the task 'Notes' field to be displayed in Data Analysis views? /Spiro. Hi Spiro, Well, as I say to my customers, everything in a 'puter system is either a 1 or 0, therefore everything is possible. Is it easy to do what you want, no, It would be custom develpment, but it would be easier to write an SSRS report that listed tasks with notes... -- Regards, Ben. http://www.applepark.co.uk http://appleparkltd.spaces.live.com/ "ST" wrote: > Hi, > > Is there any way whatsoe...

Lookup value above cells with non-zero value
I have a table with two rows, 33 columns long. Top row has Years (200 to 2036) in it and the row below is intended for the inputing of value in percentage form. Some of the cells in bottom row may be left blan or with 0 value. Elsewhere on my spreadsheet I want to create formulas that will retur the top row value (year) for each year where the value in bottom row i greater than 0. For example, if A1 = 2004 A2 = 2005 A3 = 2006 A4 = 2007 B1 = 0 % B2 = 5 % B3 = blank B4 = 20% What formula in cell C1 will return value "2005" and in cell C2 th next year value for which the cell in row...

how do i retrieve the largest value from several records/rows in e
i'm trying to retrieve the largest value from a field. the records are many but i only want to include the records related to a specific person. "DMAX" seems to be the closest function that would do the job but ... Assume your source table in A2:B2 down names in col A, real numbers in col B In E2 down are the inputs for col A, ie specific names: Name1, Name2 .. In F2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula: =MAX(IF(A$2:A$100=E2,B$2:B$100)) F2 will return the max value from col B for the name in E2. Copy F2 down as required. Adapt the ranges to...

Default value 07-11-07
Is there any way to initially put the Available qty = the In qty? Because I need the available qty to be deducted everytime there is a transaction? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 On Wed, 11 Jul 2007 03:08:54 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >Is there any way to initially put the Available qty = the In qty? >Because I need the available qty to be deducted everytime there is a >transaction? You can use the AfterUpdate event of the Form to set the default value...

need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some intermediate values I need. I would be greatful for anyone who can offer a formula or suggestion. I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are beside it. The values in A & B change as they go from 1-30, they change at different rates. I have some intermediary values at given points but need to calculate what the values in between would be, based on the fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as best I can without including an attachment. Thanks. chris@cjalexander...

Search from value to value on text fields
I have a suggestion concerning search on text fields. Fx the zipcode field. In Denmark we often want to search for an interval fx 5000 to 5999. This isn't possible on Text fields in CRM. You have to create a search with a lot of OR's in AX it is possible to use the syntax 5000..5999. Or a selection of method Between or From and To. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this li...

Web Query and Line Break <BR> tags
Hi there, I have a problem that I have seen come up a few times in searching through the archives, but I have not been able to find a solution (if there is one). I have a project that I believe an Excel Web Query would be perfect for, except for one small problem. When I run a Web Query against a website to pull in a table, and in that table one of the cells has multiple lines separated by <BR> tags, Excel puts the multiple lines into separate cells. The ideal situation would be for it to use the <td> tags as cell delimiters, not the <br> tags. Can anyone think of any cl...

Time values
I am trying to create a simple equation that subtracts 2 time values. For example Cell 1 has 38 minutes and 24 seconds. Cell 2 has 43 minutes and 20 seconds. The difference between the 2 is 5 minutes and 4 seconds How would I go about doing this? How should I enter the numbers in a cell? When I enter 38:24, it makes it into a Date and Time field. What are my options? Thanks, Jasper Easiest is to enter minutes and seconds as 00:38:24 00:43:20 with the values in A1 and A2 then just subtract =A2-A1 returns 00:04:56 for me and not 00:05:04, you need to format as either hh:mm:ss or ...

Can't type in form field
I was emailed a Microsoft Word form I need to fill out, but when I tab to each field, I cannot type in the box. The document appears to be in design mode. Thanks for any suggestions! Turn off Design mode. In Word 2007, click the Design Mode button on the Developer tab. However, if the form contains ActiveX controls, macro security settings (which are user/machine specific) may force Design mode every time the document is opened. The best way to fix this is to remove any ActiveX controls from the form document (but I realize that you may not have any influence over the d...

Largest mailbox
We have some very large Exchange mailboxes in my organization and I'm trying to convince them to use quotas. Our largest mailbox right now is nearly 10GB with 100,000 items. Our top 10 mailboxes total 60GB together. What's the largest mailboxes any of you have heard of? Are other organizations storing this much email in Exchange? I can't imagine we're the only email pack rats in the world. -- Brian Spooner, MCSE Network Engineer I have a user at 2 GB and I was hounding them. This is just not efficent. It takes the data base for event to defrag and run other maintenan...

Print field from different table
Access 2007 on WinXP I have a report designed that is printing the department number, I would like to print teh department name instead. The depatment number is included in teh query the report is based upon. The report requires data from 4 differnt tables and when I try to add a fift table into the report relationship the output data duplicates unreliably. I work primarily with the click and drag method of report build in design view as opposed to coding everything. What I want is to print the DeptName filed from the TblDept table where the tblDeptID in TblDept is equal to t...

blank cells having no value
Hi, though this is Steve's account it is his wife Val writing with my problem. Back in the old days of DOS there was a function which allowed you to set all blank cells as blank - that is = not having a value of Zero. I am working on Excel 2003 at home and 2007 at work so am at the moment somewhat confused and cannot find that facility on either system. What I am trying to do is: I have two cells one E6 representing "goals For" and another F6 representing "goals against" I have the following simple formula. =IF(e6>F6,3,IF(e6=F6,1,0)) this works fine...

logical (if a1=specific word, can c1 = value entered in a2?)
I am trying to make a payroll sheet so that if someone enters the word "Stat" into field a1, and then manually enters the number of hours worked into a2, then c1 automatically displays the same value that a2 displays. (I have 2 different columns at the end, one for regular hours and one for stat hours, and I need them to display all of the regular hours into one field and all of the stat hours into the column beside it.) =IF(C1="Stat",A2,"") "mel" wrote: > I am trying to make a payroll sheet so that if someone enters the word "...