Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query help (Access2000)

    The following is the SQL code of a query that retrieves records that have been amended. Sometimes the records are amended more than once and this query picks up each and every one of them. What I was hoping to do is pick up the most recent amended version of the record. Could somebody tell me how I could do that PLEASE?

    SELECT ZTrades.created, ZTrades.amendnum, ZTrades.tnum, ZTrades.id, Sec.name, Sec.isin, Sec.sedol, Sec.cusip, ZTrades.cancel, ZTrades.notes, ZTrades.td
    FROM ZTrades INNER JOIN Sec ON ZTrades.id = Sec.id
    WHERE (((ZTrades.amendnum) Is Not Null) AND ((ZTrades.td)=#8/22/2001#) AND ((ZTrades.fund)="INTL_SHARES"))
    ORDER BY ZTrades.td DESC;
    See attchment for example of data.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query help (Access2000)

    Didn't you get helped on exactly the same question before? The solution is the same as it was <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=77039&page=0&vie w=&sb=&o=&fpart=1&vc=1>in this thread</A>. You need to use a GROUP BY query.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (Access2000)

    YEP! I know and I did do what I was told to do, Have a look at the code below:

    SELECT Max(TradeAmendments.amended) AS MaxOfamended, TradeAmendments.tnum, TradeAmendments.id, Sec.name, Sec.isin, Sec.sedol, Sec.cusip, TradeAmendments.cancel, TradeAmendments.notes, TradeAmendments.td
    FROM Sec INNER JOIN TradeAmendments ON Sec.id = TradeAmendments.id
    GROUP BY TradeAmendments.tnum, TradeAmendments.id, Sec.name, Sec.isin, Sec.sedol, Sec.cusip, TradeAmendments.cancel, TradeAmendments.notes, TradeAmendments.td, TradeAmendments.fund
    HAVING (((TradeAmendments.td)=#8/22/2001#) AND ((TradeAmendments.fund)="INTL_SHARES"))
    ORDER BY TradeAmendments.td DESC;

    But it still is displaying two versions of a record. I don't get it.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query help (Access2000)

    You undoubtedly have the same problem you had before--you included a unique field, which will result in multiple records. What is TradeAmendments.id? If it's the identifier for the record, take it out or you will continue to get multiples. Just because you join on it doesn't mean you have to return it as a field.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (Access2000)

    ID is just another feild which has the same value as the other version of the record, the only feild which has the different values is the feild [Created] which has the date and the time the record was created and I'm using this to get the MAX.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query help (Access2000)

    Then look closely at the rows returned. The only reason to get mutliple rows for a value in a group by query is that you have another field in the query that makes each record unique. Find that field and remove it and you'll stop getting more than one record for a group.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (Access2000)

    THANX Charlotte,
    I think it works now.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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