Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162

    Data Not Showing on Sub Form

    Hi guys

    I have this subform problem

    I import all the invoices for the client

    ID Site Code Invoice Date PO Invoice Contractor Invoice Amount Expense Code Exp Description Lease Code Status A B C D E F
    771 0038S 14/05/2013 34795 MAY-LD1595 Living Decor 1000.00 65
    A Authorized on 20/05/2013 by LD, Paid on 24/05/2013 0.00 0.00 1000.00 0.00 0.00 0.00
    772 0038S 30/09/2012 99276 9001399 Rouse Partners Llp 2880.00 91
    A Authorized on 06/03/2013 by MAB, Paid on 11/03/2013 0.00 0.00 2880.00 0.00 0.00 0.00

    in another table I have previous Year Expenses and Budgets by Expense Code

    ID Site Code Group Code Expense Code Expense Discription Group Total Budget
    43 0038S A 91 Accountant & Solicitors Fees 950.00 700.00
    44 0038S A 92 RCS Legal Fees 407.00 415.00
    45 0038S A 65 Repairs & Day to Day Maint 1523.00 3500.00
    46 0038S A 90 Ringley Management Fees 3837.00 4500.00


    But on the Subform its only showing the group totals where there is an invoice for that expense code, and not showing Previous Years and Budgets where there is no expense in the current year

    Expense Discription Exp Total P/Y Total Budget
    Repairs & Day to Day Maint 1000.00 1523.00 3500.00
    Accountant & Solicitors Fees 2880.00 950.00 700.00

    Any idea how I can correct this?



    Thanks in Advance

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Do you still have prior raw invoice data in the database? Why save aggregate data to a table? Why not calculate when needed?

    I presume you have a table of all expense codes? Create queries that summarize the expenses then join those queries to the all expense codes table.
    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
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    The Invoice Table is an import of raw data, (which I need not just for the forms but for report to list all the invoices) no calculations are done here

    The Previous year/Budget table is put together using drop down of the Group Code and Expense Code and then I enter the amount that's in the accounts for the previous year, so again no calculation

    The subform is a Query that brings together the invoice table and previous year/budget table to the relevant Client Code.

    Hope that makes sense

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    You do not have prior year raw invoice data?

    Customary practice is to save raw data and calculate summary data when needed. Archiving raw data and saving summary data might be required if data exceeds Access 2GB size limit.

    Did you try the query I suggested?
    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
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    No we don't always have previous year data, this might come from another accountant, and all we have is what is filed.

    The queries are already linked to expense codes and group codes.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Provide query SQL statement(s) for analysis.

    Or provide the db. 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.

  7. #7
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    SELECT [Invoices 2013].[Site Code] AS [Site Code], Sum([Invoices 2013].[Invoice Amount]) AS [SumOfInvoice Amount], [Invoices 2013].[Expense Code] AS [Exp Code], [Invoices 2013].[Lease Code] AS [Group Code], [Expense Codes].[Expense Discription], [Previous Year Totals Expenses].[Group Code], [Previous Year Totals Expenses].[Group Total], [Previous Year Totals Expenses].Budget
    FROM ([Expense Codes] INNER JOIN [Invoices 2013] ON [Expense Codes].[Expense Code] = [Invoices 2013].[Expense Code]) INNER JOIN [Previous Year Totals Expenses] ON ([Expense Codes].[Expense Code] = [Previous Year Totals Expenses].[Expense Code]) AND ([Invoices 2013].[Site Code] = [Previous Year Totals Expenses].[Site Code])
    GROUP BY [Invoices 2013].[Site Code], [Invoices 2013].[Expense Code], [Invoices 2013].[Lease Code], [Expense Codes].[Expense Discription], [Previous Year Totals Expenses].[Group Code], [Previous Year Totals Expenses].[Group Total], [Previous Year Totals Expenses].Budget
    HAVING ((([Invoices 2013].[Lease Code])="A") AND (([Previous Year Totals Expenses].[Group Code])="A"));

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    INNER JOIN requires related records in all datasets. Use LEFT or RIGHT ("Include all records from Expense Codes and only those from {other source} that match").
    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.

  9. #9
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Hi June

    Sorry was out for a while, I am not totally sure I understand what you mean by this?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    The query is using INNER JOIN to link the datasets. Need to use RIGHT or LEFT - just not sure which it will be. Do you know how to change the join type of the links in query designer?
    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.

  11. #11
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Yes I changed the join type so it arrows left or right tried both it errors with the message ambiguous outer joins.

    I tried it on all of them in the query box

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    That query doesn't look like what I suggested.

    I will have to review your db. If you want to provide, 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.

  13. #13
    Historypaul is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    I think it's telling you that you have ambiguous outer joins because you are trying to join more than 2 tables in your query. I'm a rookie, so the simplest way I've found to fix this is to first join two of the tables in a query, then use that query in another query joining the last table. Watch your join types, making sure that you select "all" of the table you need and "only the matching records" from the other tables.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    It is possible to have more than two tables in a query with multiple outer joins, depends on how the tables are related. Just hard to interpret the sql, especially without knowing the db.
    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.

  15. #15
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Hi June

    Please find attached db

    (Main Site Table)Form) search for site number 0038S as you can see despite the budget having 4 (Previous Year Totals Expenses)Table) lines its only showing 2 lines these have invoices and showing but the other 2 have no expenses in the current year but the budget still needs to show (Invoices Group A Totals)Query)

    Hope you can help its pretty much the last piece in the jigsaw for this database

    Thanks Again

    Keiath
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 04-16-2014, 01:15 PM
  2. Showing Related Data in a Form
    By GregWatling in forum Forms
    Replies: 20
    Last Post: 06-11-2013, 07:42 AM
  3. <div> tags showing in form data
    By bar891 in forum Forms
    Replies: 13
    Last Post: 05-20-2013, 12:04 AM
  4. Replies: 4
    Last Post: 02-27-2012, 10:29 AM
  5. Data is not showing up in the form
    By zuerin in forum Access
    Replies: 1
    Last Post: 07-13-2011, 09:16 AM

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