largest value of field in query output

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MrGQ - 18 Jul 2007 00:12 GMT

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

Reply to this Message

DCPan - 18 Jul 2007 00:34 GMT

The group by didn't work because of the date field. The group by on the date
field would nullify the max function as it is put in a separate group.

If you just leave the columns of Title, Rev, and Position, the MAX will work
on the Rev when you use the group by.

> I have a query that pulls all the records from a table. For example,
> the query outputs the following:
[quoted text clipped - 25 lines]
>
> thank you in advance

Reply to this Message

MrGQ - 18 Jul 2007 12:03 GMT

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

Reply to this Message

DCPan - 18 Jul 2007 17:06 GMT

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

Reply to this Message

MrGQ - 20 Jul 2007 21:33 GMT

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?

>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
[quoted text clipped - 20 lines]
>>
>> THANK YOU

Reply to this Message

DCPan - 20 Jul 2007 22:32 GMT

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.

> I didn't think that I was so uninformed when it came to Access but I'm
> finding out how little I know !
[quoted text clipped - 31 lines]
> >>
> >> THANK YOU

Reply to this Message