Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Sum function on report from table

    I have a report that is tied to a table. Is it possible to write a sum function that will get the values from a different table? Something like =sum(IIF[tbl Main]![Acct Status] like "11" and [Date_in_inv] Between #5/1/2004# and #5/31/2004#)).

    This doesn't work but that's the type of format I need. Thanks.

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by tjarvas
    I have a report that is tied to a table. Is it possible to write a sum function that will get the values from a different table? Something like =sum(IIF[tbl Main]![Acct Status] like "11" and [Date_in_inv] Between #5/1/2004# and #5/31/2004#)).

    This doesn't work but that's the type of format I need. Thanks.
    Hi tjarvas,
    To start off with my little experience, I would say the best thing to do first is make sure your tables are Normalized. Then, link them using a CommonField...(Primary/ForeignKey) Next, the best way is to create a Query and then pull all associated tables into that. That way you have access to the data in all the tables related to the information you need.
    Is there a way to do it as you're trying to do it???? I have no idea. Just giving you the bit of knowledge that I have in this matter.

    hope this helps...and if you need more, the BEST way to get help easily is to Zip your database and Attach it here for someone to get a hands on look at your situation. Remove all pertinent data of course.

    have a nice one,
    Bud

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Your statement makes perfect sense. Unfortunatly, I made my query into a make table query b/c it was too complex. I tried adding more fields to the query but that failed. So I am left with getting the value from a different table. Is that even possible?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    use a subselect.

    SELECT yourTable.stuff, (SELECT SUM([Acct Status]) FROM wherever WHERE [Date_in_inv] BETWEEN #5/1/2004# and #5/31/2004#) AS sum_of_stuff
    FROM yourTable

    It is of note that you may reference fields from the "primary" select statement in the sub-select as well. For instance, you could include "WHERE subselecttable.table_id = primaryselecttable.table_id" in the subselect statement.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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