Results 1 to 10 of 10
  1. #1
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9

    Query returns incorrect data when additional tables are added

    Hi....I have developed a financial database for a university continuing education program to reconcile with the Universities system.



    I have a table for forecast/annual budget and a transactions table that all the actual income and expense data is entered into.

    I want to create a query that compares "budget" to "actuals".

    The query works fine when it has "actuals" only

    When I add the table with the "budget" all the numerical results go crazy...figures appear multiple times etc.

    I would be glad to do this in a report, but can't get that to work either.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do Budget and Actuals have 1 to 1 relationship? Post the query SQL statement. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9

    Database attached

    Currently the actual and the budget table do not have a direct relationship.

    I tried creating one, and it returns no results.

    Any help/direction is greatly appreciated.

    Clair
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What are the 'actual' and 'budget' tables - Transactions and Forecast?

    That spiderweb of relationships will possibly be an issue. Review http://www.codeproject.com/Articles/...atabase-Design
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    The two tables you are drawing from will have the JOIN on some field or your query will return whacky data. Are you performing any calculations in your query or simply pulling data up next to eachother?

  6. #6
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9
    TransactionsFY16 is actual

    Subobject FY16 is Budget

  7. #7
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9
    No calculations in the query

  8. #8
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Which query in your db is it? There is many.

  9. #9
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9
    Here is a report where the budget numbers are incorrect....Forecast Summary by Budget code and subobject code vs actual
    Here is a query where the "actual" numbers are correct, but budget are wrong.....By Budget Code by subaccount vs actual

  10. #10
    clairdorsey is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    9

    Examples of issues with query

    I made two queries that indicate my issue.

    query Actual and Budget (both budget and actual numbers are being duplicated)
    and
    query Actual and Budget Actual summed (the budget numbers on this one are correct)
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-14-2015, 04:17 PM
  2. Do I need to create additional tables?
    By jamestford in forum Queries
    Replies: 3
    Last Post: 06-06-2014, 12:34 AM
  3. Query Data Incorrect
    By Two Gun in forum Queries
    Replies: 8
    Last Post: 01-31-2012, 06:07 PM
  4. to add additional tables or not?
    By phineas629 in forum Database Design
    Replies: 1
    Last Post: 09-30-2011, 12:23 PM
  5. Query returns null..based on two tables
    By shsh_shah in forum Queries
    Replies: 1
    Last Post: 03-08-2009, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums