Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    11

    Unanswered: Comparing Budget vs Actual DB

    I'm setting up a database that will compare Actual vs Budget expenditures by month. I've created one query that pulls data both from an "Actual" table and from a "Budget" table. I've set up two relationships between the two tables -one by activity code and another by account code, by establishing a master table for activity codes and another master table for account codes. Both of these master tables are related to the Actual and Budget tables in a one-to-many relationship. However, when I run the query, it does not display any accounts that are in the Actual table, but not the Budget table. Does anyone know what I'm doing wrong? Any help would be much appreciated. Thanks. GHK.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You probably need to set your links to get all records from the Actual table and only those that match the Budget table.

    One way I did an actual versus a budget type db, I had the budget field in the same table as the actual amounts (but there was always a budget amount for each actual amount). For example, some of the fields in the AccountingTable where:

    AccountID
    AccountNumber
    AccountDescription
    DateTransaction
    ReportingMonth
    ActualAmount
    BudgetedAmount
    ....
    ....

    This made it easier for when I wanted to create queries as well as reports on comparing actual amounts and budget amounts (I just summed each field). Just one suggestion....

    Another way I did it was once was where the actual amounts table had many records and the budget amount had more of a 1 line total for each Description item and both tables where linked by the DescriptionID which is probably more like you have it. When I created queries to return summed values, I linked the tables but had it set to show all records from the actual amount table and only those that matched in the Budget amount table (linked by DescriptionID) - since budget amounts were not always entered for each DescriptionID.

    And another way was where I did the same as above but made it so that the budget table always had a Description ID and the budget amounts field defaulted to 0. This could then be joined where they matched (both were equal ID).

    I can't go into details on all the fields/tables as it got a little more complicated but these were the basic concepts of how I had them setup and hopefully gives you some optional ideas.
    Last edited by pkstormy; 03-18-08 at 02:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Double click the line between the two tables and then click on Join Type. select the option that's "include all records from table actual and only those records...".
    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

  4. #4
    Join Date
    Sep 2007
    Posts
    11
    pkstormy/StarTrekker - Thanks for your comments/suggestions. Have already tried what you both suggested. It doesn't work because I have two joins (one for activity code and one for account) - when I selected the option to show all records (for the Account join) from the Actual table and those that match in the Budget table, I get a notice that states that the joins are "ambiguous" and that I should try and run one query first with one join and use that in another query ,which establishes a priority. Not sure what to do at this point, but thanks for taking the time and effort to respond.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A clustered type join - ouch. I might recommend reconfiguring your tables so that you only have a single field join between the 2 tables (ie. concatenating the data in 2 fields to 1 field and join on that one field.) Otherwise, you'll probably need to use nested queries to get the results you want (with your current design, you could still try concatenating the data in 2 fields in a query for each table and then a 3rd query to match (or unmatched) the concatenated field in the other 2 queries.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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