Results 1 to 10 of 10

Thread: DSum Nightmare

  1. #1
    Join Date
    Jun 2008
    Posts
    5

    Unanswered: DSum Nightmare

    I am currently trying to implement a new database system for work, but I've come to a brick wall. If anyone can help I will be extremely grateful because I've been pulling my hair out for hours. Here's the problem:

    I have a query in which there is a field called amount paid. In a new query I want a field with a running total of the amount paid to date. I've tried many versions of the same code, but keep getting the same error message:

    The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Tenant number?.''

    Here's the code:

    SELECT [Rent account for specific tenant].Date, 12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]) AS Monthnumber, DatePart('d',[Date]) AS Daynumber, [Rent account for specific tenant].[Amount paid], [Rent account for specific tenant].[Payment method], [Rent account for specific tenant].Notes, [Rent account for specific tenant].[Actual rent due], DSum([Amount paid],"Rent account for specific tenant","(12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]))<" & [Monthnumber] & "" & "OR ((12*(DatePart('yyyy',[Date])-2008)))+DatePart('m',[Date])=" & [Monthnumber] & "" & "AND DatePart('d',[Date])<=" & [Daynumber] & "") AS [Total paid]
    FROM [Rent account for specific tenant]
    GROUP BY [Rent account for specific tenant].Date, 12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]), DatePart('d',[Date]), [Rent account for specific tenant].[Amount paid], [Rent account for specific tenant].[Payment method], [Rent account for specific tenant].Notes, [Rent account for specific tenant].[Actual rent due];

    If anyone has any ideas or alternate methods please let me know.
    Thank you very much, Alex.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Use the Running Sum property in a report rather than doing this in a query?
    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

  3. #3
    Join Date
    Jun 2008
    Posts
    5
    Quote Originally Posted by StarTrekker
    Use the Running Sum property in a report rather than doing this in a query?
    I have considered this. It would work fine for creating reports showing each tenant's account, but there are other calculations I need to perform that will use the running total data, so this query is required.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok.

    I only suggest it since I've never had to have a running sum in a query before. Any time a running sum is displayed, it is always in a form or report... right? Or do you allow users to open queries?
    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

  5. #5
    Join Date
    Jun 2008
    Posts
    5
    Another query will calculate the cash collectable based on the rent due and current balance with an iif function. This will then be used to, among other things, create a report of the cash collectable from each tenant on a given date, then another report showing the cash collectable from each tenant on a given date where the cash collectable is at least twice the weekly rent.

    That's just one use of the running balance. Is it possible to do that within a report? What makes me doubtful is that the contents of the report would be based on the functions that are proposed to be done in the report itself, which seems to me like nonsense.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How can I know? I have no idea of your table structure!
    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
    Jun 2008
    Posts
    5
    Ok, I've attatched the database. There are no reports or anything yet, I haven't got that far.
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you should stop what you're doing and have another read-up on table design and relationships. You'll save yourself a lot of headaches and time down the track.

    * Having a table that records every single date MUST be a mistake.
    * A table to hold property details is called for.
    * A 1:1 relationship between Tenants and Rooms is a mistake.
    * Arrival Date and property number don't belong in Tenant, they belong in another table, maybe called Tenancy along with the TenantID and the RoomID they are renting.
    * Avoid spaces and question marks in field names.

    Sorry, but I can't give any quick answers to your problem with such a table design.
    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

  9. #9
    Join Date
    Jun 2008
    Posts
    5
    Ok, I'll take all that on board. Thank you for taking a look and for your advice.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    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

Posting Permissions

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