Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Query to find Expiration Date (dateAdd)

    Hi,

    I have a question. How can I achieve this on a query, If the "Vendor", "State" and "Contract" are the same between multiple rows, Return the Minimum "Eff" per Contract and give it an expiration date. If there is another "Contract", then the Expiration Date has to be a Date prior (See example)

    here is my query statement:

    DateAdd("d",-1,DLookUp("Eff","tblPayment","Vendor = " & Nz(DMin("Vendor","tblPayment","Vendor = " & [Vendor] & " And Vendor > " & [Vendor]),0)))



    Vendor State Contract Eff Exp NewEff NewExp
    300324 AZ M06 1/1/2006 1/31/2007 1/1/2006 12/31/2007
    300324 AZ M06 1/1/2007 12/31/9999
    300324 AZ PRDCR 1/1/2008 12/31/2008 1/1/2008 12/31/9999
    300324 AZ PRDCR 1/1/2009 12/31/9999 Database7.zip

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quick answer:-
    Design your tables properly for use in a relational database. That means normalisation
    Whenever you see columns like contracteffdat, exp date , neweff new expiry its a sure sign of possable flaky design. Same as col1, col2...coln or cola, colb... colx
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    This is just for example.Example.zip

    The following query helps do what I needed. The only issue is it does not order the table by ID when using an update query.


    SELECT ID, Vendor, Eff AS BeginDate, DateAdd("d",-1,DLookUp("Eff","tblPayment","ID = " & Nz(DMin("ID","tblPayment","Vendor = " & [Vendor] & " And ID > " & [ID]),0))) AS EndDate, ID
    FROM tblPayment
    ORDER BY ID;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?what update query
    there is no update query in your db, there is only the table

    your SQL as above is garbage
    Code:
    SELECT ID, Vendor, Eff AS BeginDate, DateAdd("d",-1,DLookUp("Eff","tblPayment","ID = " & Nz(DMin("ID","tblPayment","Vendor = " & [Vendor] & " And ID > " & [ID]),0))) AS EndDate, ID
    FROM tblPayment
    ORDER BY ID;
    where is the column ID in your table?
    even if it was in the table the element underlined is not any dialect of SQL I'm aware of, I cannot comprehend what you are trying to achieve with it

    looking at the table
    sorry thats just awful design

    how can you have more than one row for the same contract with overlapping dates. its sloppy design at best. its the root cause of your issues. yes a contract can be extended but recording the same contract more than once is daft.

    take a step out of the trenches and design your schema properly.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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