Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access: DSUM with criteria that compares fields from two different tables?

    I am trying to add a calculated field in my query to sum the payments up until the date of the entry. There are two tables involved. I have a table for the dates and amounts of wired payments ("KYOWAPayments") as well as a table of invoice# and shipping information ("AP statement log"). The two tables have no relationship.

    I wanted to make a field that will automatically calculate the sum of all wired payments up until the date of each entry, so I tried using DSUM:

    Paid: DSum("Amount",[KYOWAPayment],"[KYOWAPayment]![Wire Date]<= [Received]")

    But, what comes up is a prompt for "KYOWAPayment". I don't really understand why a prompt suddenly comes up, but I enter "[KYOWAPayment]" then click OK just to see what happens, and another dialog box comes up with following message: "The expression you entered as a query parameter produced this error: 'Microsoft Access cannot find the name 'Received' you entered in the expression'."

    This is the first time I am dealing with databases at all, and I've tried everything I can think of. I tried making a spreadsheet form with a calculated text box field as well, but a #NAME? error came up. I also tried making a split form and putting the KYOWAPayment table in the top section and tried reproducing the query in the bottom, spreadsheet section by adding a calculated text box field to no avail. Is there anything I could do? I have a large amount of data, and I'd hate to have to go back and edit an excel file over and over again every time I need to update. I'd be extremely grateful for any help!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Paid: DSum("Amount",[KYOWAPayment],"[KYOWAPayment]![Wire Date]<= [Received]")
    The Syntax for DSUM is:DSum(Expr, Domain, Criteria)

    Paid: DSum("Amount","KYOWAPayment","[Wire Date]<=#" & [Received] & "#")

    But what is Received? Is that a field in the table KYOWAPayment ? Presumably WireDate is a field in KYOWAPayment.
    You said that two tables are involved? Does the second table come into the DSUM at all?
    Regards
    John



Tags for this Thread

Posting Permissions

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