Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    35

    Unanswered: use SQL only to select most recent date?

    Hello,

    I have a table that holds visits (including a date) for a provider visit (so, multiple dates for the same provider). [tblAllVisitsThru2001]

    I need to populate another table (which holds about 150 records) with the most recent date for the same provider from above mentioned table. [tbl2003NewVisits]


    How do I do this using just SQL? Is it even possible??

    I have tried several permutations of this and got zero results (this one to select all dates from year 2001):

    SELECT tbl2003NewVisits.*, tblAllVisitsThru2001.Review, tblAllVisitsThru2001.Date, tblAllVisitsThru2001.Rate, tblAllVisitsThru2001.Records, tblAllVisitsThru2001.Date
    FROM tbl2003NewVisits INNER JOIN tblAllVisitsThru2001 ON tbl2003NewVisits.pin = tblAllVisitsThru2001.PIN
    WHERE (((tblAllVisitsThru2001.Date)>=#1/1/2001#) AND ((tblAllVisitsThru2001.Date)<=#12/1/2001#));

    Joyce

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: use SQL only to select most recent date?

    Originally posted by perlgurl
    Hello,

    I have a table that holds visits (including a date) for a provider visit (so, multiple dates for the same provider). [tblAllVisitsThru2001]

    I need to populate another table (which holds about 150 records) with the most recent date for the same provider from above mentioned table. [tbl2003NewVisits]


    How do I do this using just SQL? Is it even possible??

    I have tried several permutations of this and got zero results (this one to select all dates from year 2001):

    SELECT tbl2003NewVisits.*, tblAllVisitsThru2001.Review, tblAllVisitsThru2001.Date, tblAllVisitsThru2001.Rate, tblAllVisitsThru2001.Records, tblAllVisitsThru2001.Date
    FROM tbl2003NewVisits INNER JOIN tblAllVisitsThru2001 ON tbl2003NewVisits.pin = tblAllVisitsThru2001.PIN
    WHERE (((tblAllVisitsThru2001.Date)>=#1/1/2001#) AND ((tblAllVisitsThru2001.Date)<=#12/1/2001#));

    Joyce

    the sql should look like
    select provider
    ,max(date_column)
    from table.

    as an insertinto statment

    insert into tableb
    select provider
    ,max(date_column)
    from tableA.

    as an update statment

    update tableb
    set date = max(date)
    from tablea
    where b.provider = a.provider

    those are the generic sql forms
    let us know if you need more
    Jim

  3. #3
    Join Date
    Nov 2002
    Posts
    35
    Hi,

    Thanks. I had previously tried max in my select statement and would get this message:
    "You tried to execute a query that does not include the specified expression 'pin' as part of an aggregate function"
    Had not thought to put it in an update statement, so I tried it, and now am getting:

    Syntax error (missing operator) in query expression 'max(date) fromtblAllVisitsThru2001'

    actual code below:
    Code:
    update tbl2003NewVisits
    set  date = max(date)
    from tblAllVisitsThru2001
    where tblAllVisitsThru2001.provider =  tbl2003NewVisits.provider;
    Thought that maybe it was because of a reserved word conflict (date) so I changed it to an update statement
    Code:
    tbl2003NewVisits
    set [date] = max([date])
    from  tblAllVisitsThru2001
    where tblAllVisitsThru2001.provider =  tbl2003NewVisits.provider;
    I think my problem us that I have 150 records I am trying to find the WHERE providerID = providerID and can't seem to do a join since there are multiple rows for a provider in the main table that I am trying to pull from, because even without the clause regarding the date, trying to do a join on the table where I have one providerID (with the 150 rows) and the main table (900 rows) but when I take the join out I get 21000 plus rows (I do understand why this happens). Does that make sense?

    Thanks in advance,
    ~Joyce

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's me again and it's domain aggregate functions again

    result = DMax("[FieldYouWantToFindTheMaxFor]", "theQuery")



    izy

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by perlgurl
    Hi,

    Thanks. I had previously tried max in my select statement and would get this message:


    Had not thought to put it in an update statement, so I tried it, and now am getting:




    actual code below:
    Code:
    update tbl2003NewVisits
    set  date = max(date)
    from tblAllVisitsThru2001
    where tblAllVisitsThru2001.provider =  tbl2003NewVisits.provider;
    Thought that maybe it was because of a reserved word conflict (date) so I changed it to an update statement
    Code:
    tbl2003NewVisits
    set [date] = max([date])
    from  tblAllVisitsThru2001
    where tblAllVisitsThru2001.provider =  tbl2003NewVisits.provider;
    I think my problem us that I have 150 records I am trying to find the WHERE providerID = providerID and can't seem to do a join since there are multiple rows for a provider in the main table that I am trying to pull from, because even without the clause regarding the date, trying to do a join on the table where I have one providerID (with the 150 rows) and the main table (900 rows) but when I take the join out I get 21000 plus rows (I do understand why this happens). Does that make sense?

    Thanks in advance,
    ~Joyce

    The erros taht you are getting are because of my stupidity!!!!
    you need a group by statment for all the columns that are not in the max statment, max is treated like an aggr function in that regards
    so
    add
    group by column name at the end and you will get the correct answer
    Jim

  6. #6
    Join Date
    Nov 2002
    Posts
    35
    I just wanted to thank everyone who tried to help me with the Max(Date) issue I was having.

    I ended up using this

    Code:
    SELECT DISTINCT tblAllVisitsThru2001.PIN, Max(tblAllVisitsThru2001.Date) AS [date]
    FROM tblAllVisitsThru2001, tbl2003NewVisits
    GROUP BY tblAllVisitsThru2001.PIN;
    and then attempting a join it to my table with the one provider id (which is very similar to what I had been doing and I kept getting zero rows or errors due to my own fault), but I finally figured out that I had made an erroneous assumption about my data; I had thought that what would exist in one would exist in the other (due tot he nature of the subsets of data I had compiled) but in fact the one table that had the one record per provider that I was initially trying to join to the table that had many records (many dates) per provider were mutually exclusive!

    There is one learning experience I won't shortly forget....though I am sure I learned it before, in a galaxy far, far away.....

    Thanks again from one sometime confused database droid named Joyce

Posting Permissions

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