Top Values

  • Thread starter Thread starter bgetson
  • Start date Start date
B

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
 
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.
 
Alright, if I have the following in tblData...

ND M S P
6/16/2007 ES 1 5032
6/16/2007 ES 2 2787
6/16/2007 YM 1 13210
6/15/2007 ES 1 6000

The Query should return....

M S P
ES 1 38.09% (5032/13210)
ES 2 21.1% (2787/13210)
YM 1 100.00% (13210/13210)

Instead, I get.....

M S P
ES 1 100.00% (5032/5032)
ES 2 55.40% (2787/5032)
YM 1 100.00% (13210/13210)

Note that records from an older date are ignored (which is good), but
it's pulling the maximum from all records of the same M. I can't
figure out the cause of this problem.

Hopefully this clears things up a little better.
 
Try this:

SELECT tblData.M, tblData.S, tblData.T, tblData.P/(SELECT Max(P)
FROM tblData AS X WHERE X.ND=tblData.ND)
FROM tblData
WHERE tblData.ND=(SELECT Max(T.ND)
FROM tblData AS T)
ORDER BY tblData.ND DESC;
 
Thanks for the help, but I'm still not getting the results I need.
Now, not only are the calculations are being done the same way (with
the wrong maximum value), but only records for one value of M are
being displayed.

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?
 
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.
 
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.
Click to expand...
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.
 
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?
Click to expand...

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]
 
Back
Top