Determine Field1 @ Max(Field2) (1 Viewer)

[emorris1000]

I'm trying to build a query that will normalize some graphical data along the x-axis. To do this I need all the peaks to be at the same point. I'll just talk in generic terms of FieldX and FieldY for the X and Y variables (resp)

In my mind the steps require me to
1) Determine FieldX at Max(FieldY)
2) Determine difference between FieldX and arbitrary normalization point (probably 100k)
3) Shift FieldX at all points to put Max(FieldY) over 100k (its a log scale so I'll be dividing)

Steps 2 and 3 are easy, but step 1 is giving me grief.

Ok, so I know how to find Max(FieldY), easy enough. Now how do I find FieldX? My first thought is a complex series of subqueries. At some point in the past, where I was smarter than I am today, I created a query that would do numerical integrations. Looking back at the query I can't understand it, but I think the principle will be similar.

But is there an easier way? I'll definitely be putting this into an Excel export which should be relatively easy, but I need to be able to do it in this as well.

Any ideas on how to do this that doesn't involve a large ugly query filled with strange subqueries?

ed: Just to be clear I was able to solve this using three consecutive queries. This is not a good solution because I need to solve this like 10 times, which would give me 30 queries. And I see every query on the left side of my screen as a personal failure.

 

[emorris1000]

Well, I solved this with a series of nested queries, and I'm not super thrilled about them. Part of the reason is that they are nested subqueries, which I have heard bad things about. Secondly, while these queries work, I'm not sure they should be able to since I didn't alias a table in the middle subquery, which I think I needed to do. To be honest, I am in way over my head on this one.

Anyways, here's the query. If anyone has any suggestions on cleaning this up or sees a glaring error let me know:

SELECT
  TData.SampleID,
  TData.FieldY,
  (TData.FieldX - qMaxAdj.MaxFieldX+100000) AS Adj_FieldX
FROM 
(
  SELECT
    qMax.SampleID,
    TData.FieldX AS MaxFieldX,
    TData.FieldY 
  FROM 
  (
    SELECT
      TDataDupe.SampleID,
      max(TDataDupe.FieldY) AS MaxofFieldY    FROM TData AS TDataDupe 
    GROUP BY TDataDupe.SampleID
  ) AS qMax 
  LEFT JOIN TData ON qMax.SampleID = TData.SampleID
  WHERE ((TData.FieldY)= qMax.MaxOfFieldY))
) AS qMaxAdj 
LEFT JOIN TData ON qMaxAdj.SampleID = TData.SampleID;

Notice that I'm using the table TData in both the main query and the qMaxAdj subquery. I feel like this is bad and I should be using an alias. Is it?