Results 1 to 7 of 7

Thread: Query Challenge

  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: Query Challenge

    I have a query that includes a couple running total fields using DSum.

    I need a way to find the first instance (DFirst?) where the value in field B for any record (sorted by a date field) exceeds the value of field A for a specific record. I'm not looking for where B exceeds A in a single record, but using a specific value for A, find the first record where B is greater.

    My efforts with the DFirst function have been lame and only returns the first instance of B > A in the same record. I think I need an expression to reference a specific record versus all.

    Anyone with a solution will be god in my eyes.

    Thanks,

    Kevin

  2. #2
    Join Date
    Jan 2009
    Posts
    8
    DFirst("fieldB", "table/query", "fieldB >fieldAspecificnumber")

    Not sure what you mean by specific record. Do you mean a constant value? or would this be dynamic as well?

  3. #3
    Join Date
    Jan 2009
    Posts
    11
    Thanks Lisa. Yes, it would be dynamic. Kinda of like:

    Date A B Date when A >= B
    1/1/2009 10 20 1/3/2009
    1/2/2009 15 20 1/3/2009
    1/3/2009 20 25 1/4/2009
    1/4/2009 25 35 1/6/2009
    1/5/2009 30 40
    1/6/2009 35 45

    Where the "Date when A>=B" is the expression I'm looking for.

  4. #4
    Join Date
    Jan 2009
    Posts
    11
    Bump: Still no help with this one. I'm hoping this picture better explains what I'm looking for. I need an expression for Field3 as described in the pic.

    I will gladly donate to the favorite charity of anyone with the answer.

    Thanks,

    Kevin
    Attached Thumbnails Attached Thumbnails HelpWithExpression.jpg  

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is the SQL to produce the first 4 columns (up to and including "Field2")?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2009
    Posts
    11
    Here it is PF:

    SELECT qryRentTableTest.RentPmtID, qryRentTableTest.Date, qryRentTableTest.BalanceDebt, qryRentTableTest.BalanceCredit
    FROM qryRentTableTest;

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hokally dokes. What's the SQL for qryRentTableTest please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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