Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2006
    Posts
    26

    Exclamation Unanswered: Create report for Available funds. Change expense amt subtracted based on a checkbox

    I have created a database that keeps a realtime record of available funds. In my "ExpenseDetails" table I have a field for "ProjectedCost" which is used when an item is submitted for purchase. After the item has been purchased there is another field for "Actual Cost" where the actual dollar amount is entered. I also have a yes/no (Checkbox) field that is left unchecked until the actual cost has been entered.

    I need my report of available funds to use the dollar amount present in the "ProjectedCost" field when the checkbox is unchecked (No) and the "Actual Cost" field when the checkbox is checked (Yes).

    In my report the formula for my Available Funds is written as:

    =[AwardAmount]+[TotalSubAwardAmount]-[SumOfAwardAmountSpent]

    The [AwardAmount] and [TotalSubAwardAmount] values are from queries that simply total the amount of money that I started with in two separate tables.

    The [SumOfAwardAmountSpent] is calculated from the "ExpenseDetails" table via the Query [SumOfAwardAmountSpent].

    I'm not sure if I can do this by creating two queries one query that searches based on the "Yes" value and one on the "No" value. I'm stuck

  2. #2
    Join Date
    Oct 2006
    Posts
    26
    Here is the code for my query:

    SELECT DISTINCTROW ExpenseDetails.HJFProjectNumber, Sum(ExpenseDetails.AwardAmountSpent) AS SumOfAwardAmountSpent
    FROM ExpenseDetails
    GROUP BY ExpenseDetails.HJFProjectNumber;


    I need something along the lines of this

    If([YES NO] = -1,0) DoCmd GoToControl "AwardAmountSpent, ProjectedCost"

    to go in place of the Sum(ExpenseDetails.AwardAmountSpent) but I am not sure how to format it correctly within a Sum.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you're saying you want "Spent" when available, otherwise "Projected"? For starters, it doesn't seem like the yes/no field serves any purpose. The presence of a value in the "spent" field gives you the same information. Thus, depending on your structure (if the field could contain zero...):

    nz(Spent,Projected)

    or

    IIf(Spent>0,Spent,Projected)

    would give you the appropriate values.
    Paul

  4. #4
    Join Date
    Oct 2006
    Posts
    26
    That seems like a good idea. One important point to bring up is the fact that sometimes the Actual Cost is higher and other times lower than the projected cost.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does that affect which you want displayed? It sounded like you wanted actual unless it wasn't there. Those will do that.
    Paul

  6. #6
    Join Date
    Oct 2006
    Posts
    26
    so would my query look like this?

    SELECT DISTINCTROW ExpenseDetails.HJFProjectNumber, Sum(nz(ExpenseDetails.AwardAmountSpent,ExpenseDeta ils.ProjectedCost)

    or

    IIf(AwardAmountSpent>0,AwardAmountSpent,ProjectedC ost)) AS SumOfAwardAmountSpent
    FROM ExpenseDetails
    GROUP BY ExpenseDetails.HJFProjectNumber;

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Seems like you could have tested in less time than it took to post the question, but my guess would be:

    SELECT HJFProjectNumber, Sum(IIf(AwardAmountSpent>0,AwardAmountSpent,Projec tedCost)) AS SumOfAwardAmountSpent
    FROM ExpenseDetails
    GROUP BY HJFProjectNumber;
    Paul

  8. #8
    Join Date
    Oct 2006
    Posts
    26

    Thumbs up

    Sorry, I did test it but still did not have the syntax exactly perfect.

    It appears that your code works perfectly. I really appreciate the help and cannot thank you enough for you patience and expertice!

    I've only been using SQL for about 30 hours so I'm still a good ways away from mastering it. Thanks again.

  9. #9
    Join Date
    Oct 2006
    Posts
    26
    Oh, I decided to go ahead and leave the checkbox field in the table. It will serve to show users that the record has been updated and verified with information from our accounting company. My idea is to make a condition so that whenever the AwardAmountSpent field is Null the [YES NO] value will be 0 and when not=Null will be -1. That will allow me to create a query that will bring up all of the records that need updated once an actual cost is available for the AwardAmountSpent field. I guess I still don't need it to create the query, oh well.

    It would be great if other users on this site could use this thread!
    Last edited by tlaurick; 10-12-06 at 22:11.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yeah, it's your call but it really serves no purpose and is extra work to keep updated. I'd dump it. You can always display a checkbox on a report or form that's based on the AwardAmountSpent field.
    Paul

Posting Permissions

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