Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19

    Unanswered: self-join, MAX and MIN

    There is a table like this:

    id, date, value

    I would like to see a result like this:

    id, MAX(date), value, MIN(date), value

    I got as far as:

    SELECT t1.Id, t1.wert, t1.datum
    FROM Tabelle1 AS t1
    WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)

    But

    SELECT t1.Id, t1.wert, t1.datum, t2.wert, t2.datum
    FROM Tabelle1 AS t1, Tabelle1 as t2
    WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)
    AND t1.id = t2.id
    AND t2.datum = (Select min(t2.datum) from Tabelle 1 as t2);

    doesn't work. Is this because the syntax is wrong or because Access can't do it?

  2. #2
    Join Date
    Mar 2004
    Posts
    9

    Re: self-join, MAX and MIN

    Originally posted by Clara Z.
    There is a table like this:

    id, date, value

    I would like to see a result like this:

    id, MAX(date), value, MIN(date), value

    I got as far as:

    SELECT t1.Id, t1.wert, t1.datum
    FROM Tabelle1 AS t1
    WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)

    But

    SELECT t1.Id, t1.wert, t1.datum, t2.wert, t2.datum
    FROM Tabelle1 AS t1, Tabelle1 as t2
    WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)
    AND t1.id = t2.id
    AND t2.datum = (Select min(t2.datum) from Tabelle 1 as t2);

    doesn't work. Is this because the syntax is wrong or because Access can't do it?
    I would suggest to use a self join on id column and get one set of data from one instance and wnd set from the other insatnce.. this should work..

    Vishal

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Thanks visheetal,

    but can you specify what you mean? It sounds like my example (which doesn't work) to me.

  4. #4
    Join Date
    Mar 2004
    Posts
    9

    Lightbulb

    Originally posted by Clara Z.
    Thanks visheetal,

    but can you specify what you mean? It sounds like my example (which doesn't work) to me.

    select a.id,a.date,a.date,b.date,b.val from
    a , b
    where
    a.id = b.id
    and a.date = (select max(date) from aa)
    and b.date = (select min(date) from aa).....

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Well, that what I tried in my example - it results in an empty recordSet.

    Mine:
    SELECT t1.Id, t1.wert, t1.datum, t2.wert, t2.datum
    FROM Tabelle1 AS t1, Tabelle1 as t2
    WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)
    AND t1.id = t2.id
    AND t2.datum = (Select min(t2.datum) from Tabelle1 as t2);

    Yours:
    select a.id,a.date,a.date,b.date,b.val from
    a , b
    where
    a.id = b.id
    and a.date = (select max(date) from aa)
    and b.date = (select min(date) from aa).....

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Could you post a message showing the sample result you'd like to see and a tiny MDB with a set of data that you'd like us to use? I think this would help a lot.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Originally posted by Pat Phelan
    Could you post a message showing the sample result you'd like to see and a tiny MDB with a set of data that you'd like us to use? I think this would help a lot.

    -PatP
    My first message says what the result should look like.
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    I should add that I whould like to see the values for the max and min Dates for each Id. Example:

    Id is a Patient id and the Dates are times of blood samples. I whould like to compare the values from the first sample with the values for the very last - for each patient.

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •