Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: Can you help with sub query

    Table 1
    Date Game ID Ending
    4/1/2009 883 100
    4/2/2009 883 200
    4/3/2009 883 300
    4/4/2009 883 400


    Query1
    Date Game ID Ending
    4/1/2009 883 100
    4/2/2009 883 200
    4/3/2009 883 300
    4/4/2009 883 400

    I would like to add column in query one which will show me previous day’s ending
    Query1
    Date Game ID Ending Previous Day Ending
    4/1/2009 883 100 0
    4/2/2009 883 200 100
    4/3/2009 883 300 200
    4/4/2009 883 400 300
    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1) Build a first query like this:
    Code:
    SELECT Table1.fDate, DateAdd("d",[fDate],-1) AS prevday, Table1.fEnding AS fPrevEnding
    FROM Table1;
    2) Build a second query that will give you the result you're looking for, like this:
    Code:
    SELECT Table1.fDate, Table1.fGameID, Table1.fEnding, Query1.fPrevEnding
    FROM Table1 INNER JOIN Query1 ON Table1.fDate = Query1.prevday;
    By the way, it's not a good idea to use Date as the name of a column. Date is a reserved word in Access (it's the name of a function).

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    67

    Please help with sub query

    Thanks a lot it worked now I have small problem. When I designed the report it won't let me subtotal. I'm getting error in the report says calculation not allowed.

  4. #4
    Join Date
    Apr 2009
    Posts
    67

    Please help with sub query

    Thanks a lot it worked now I have small problem. When I designed the report it won't let me subtotal. I'm getting error in the report says calculation not allowed.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's strange because it works without any problem here (Access 2003). Did you built your report directly on the query?

    Have a nice day!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Calculation not allowed? ? O_o

    That's a new one on me
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's why I was asking if the report was directly built on the query, just in case something else is in the way and causes problems.

    Have a nice day!

  8. #8
    Join Date
    Apr 2009
    Posts
    67
    Yes the report is built directly from the query

  9. #9
    Join Date
    Apr 2009
    Posts
    67
    I'm getting error that says "Multi-level GROUP BY clause is not allowed in subquery.

  10. #10
    Join Date
    Apr 2009
    Posts
    67

    thanks

    I was able to create another query that runs the first subquery and then base my report with this new query and it worked

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You wrote:
    Yes the report is built directly from the query
    Now you tell that the query is based on another query. Which one is it?

    Anyway, if it works where is the problem?

    If it does not work, what is the code of the query you were able to create?

    Have a nice day!

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Glad to see you got it sorted hmho
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2009
    Posts
    67

    Thanks

    Thanks guys I'm new to access but I'm trying my best.

    Thanks

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm glad to know everything is all right now.

    Have a nice day!

Posting Permissions

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