selecting top/max record only

[chris Morrison]

Sounds very similar to a recent question/post. I have two
tables, company and data, with a 1 (company)to many (data)
relationship, linked by an autonumber field employernumber
(co) and idnumber (data). I have the data table display as
a subform on my main data-entry form. In the subform /data
table, I have a record number field that displays 1 for
the first record, 2 for the second record, etc...I want to
design a query that pulls information from the highest or
max or top value record for all companies, but I cannot
get it to work. Is there a way to pull just the maximum
record number for all companies? You see, my ultimate
goal is to list the values associated with the highest
record number from the data table, and also to design a
calculated field that takes a value from the highest
record number and subtracts it from a value in the same
field from the initial or number 1 record. Thank you for
any help or advice you could share with me. Any help would
be great. Since I'm not an expert by any means, could you
explain any advice given in terms of the QBE window?
Thanks again, chris Morrison

 

[Michel Walsh]

Hi,


Write a query that produce the max for each company, like:


SELECT Company, MAX(dataField) as MaxOF
FROM myTable
GROUP BY Company


That returns the max, by company. Save the query, say, under the name Q1.


Next, make another query, like:

SELECT Company, dataField-MaxOf
FROM myTable INNER JOIN Q1 ON myTable.Company=Q1.Company



that would make, for each record, the difference between the actual data,
and the max value (for the related company).



Hoping it may help,
Vanderghast, Access MVP