Top Values

[bgetson]

I've got a table with fields: ND, M, S, T, and P. The primary key is
comprised of those first four fields.

I have a query which is supposed to calculate a record's P (profit) as
a percentage of the maximum of all records with the most recent ND
(date). My problem is that everything I do seems to be implicitly
grouped by M.

When I want to find the maximum of all records with the most recent
ND, it's finding the maximum of all records with a particular M and
the most recent ND. When I want to display all records with the most
recent ND (by selecting TOP 1 and sort descending), it again limits to
all records with a particular M.

SELECT TOP 1 tblData.M, tblData.S, tblData.T, tblData.P/(SELECT Max(P)
FROM tblData AS X WHERE X.ND=tblData.ND)
FROM tblData
ORDER BY tblData.ND DESC;

It's my first day with SQL, and I can't figure out what it is, or even
a workaround for it.
Any help would be greatly appreciated.
-bgetson

 

[Ken Snell \(MVP\)]

I'm not following what you want to do. Show us some examples of the raw data
and what you want the query to show as its result.

 

[Ken Snell \(MVP\)]

The query's SQL statement that I posted should provide you with the results
that you seek (EXCEPT that the query includes a field named T, which is not
in your sample data), if I've understood your sample data and desired
results correctly. I have even tested the query with your data, and it
provides the actual results that you seek.

Post the actual SQL statement that you tried and that is not working.

 

[bgetson]

Thank you for all the help that you gave me. I don't yet understand
Access, but after completely starting fresh, I think I have finally
gotten the results I need. Somehow, when I copied the structure of my
old tables into a new database to duplicate the EXACT data I gave you,
I still had the same problems. But when I created the tables from
scratch, with the exact same structure and exact same data, everything
worked fine.

From my perspective, the two tables are exact copies of each other.

For some strange reason, Access wants to implicitly create a
relationship that used to exist in my data, but doesn't anymore. With
all that said - I can safely transfer my data now.

Thank you.

 

[John W. Vinson]

The weird thing is that I used this exact same statement yesterday to
calculate exactly what I wanted, but the field "M" didn't exist within
my data. Once that field was added, everything's been messed up
without altering the SQL. Adding that one field was the only change
that I made.

Any ideas what the cause is?

Be sure that Name Autocorrect is turned OFF. This sounds like an example of
why many of us call this feature Name Autocorrupt!

John W. Vinson [MVP]