Access Query from Multiple Tables

Posted on 2014-08-12
Last Modified: 2014-08-12
I have a database with three tables:
Category Table: contains 2 fields
  CatID  - autonumber primary field
  CatName - name of expense category (eg gas, travel, hotel, stationary, etc,  and a Split Transaction item)
Bank Transaction Table: contains 3 fields
  TranID - autonumber primary field
  AnalysisID - link to Cat ID in table above
  Amt - Value of transaction
  All bank receipts & payments are recorded in this table with the appropriate Analysis ID ref back to the Cat ID table, and the amount.  Some transactions are recorded as split transactions (eg when a single cheque is used to pay for both gas and hotel charges).
Split Bank Transaction Table: contains data for any split transaction in the Bank Transaction table to allow details of the split to be maintained.  It contains 4 fields
  TranID - autonumber primary field
  MainTranID - link back to the transaction in the   Bank Transaction table above that is a split transaction
  AnalysisID - link to Cat ID in table above
  Amt - value of each element of the split transaction (eg if the Bank Transaction table is showing $50 as a split transaction, the entries in this table may be Gas $30, Hotel $20)

Requirement:  I need to design a query that will return the lowest level of analysis and appropriate amounts.  So, if a transaction is recorded in the Bank table as $10 for stationary that should be returned, but where a $50 split transaction is recorded the data returned should be the split (eg gas $30 and hotel $20).

Thanks - K
Question by:Kevinovitch
    LVL 84
    Be a bit easier if you uploaded a sample database. Be sure to remove any sensitive data and such before doing so.


    Create a query for the Split table, which includes the Category table (to show the name of the Category)

    Then use that Query to create another, which is based on the main Transaction table. Use a LEFT JOIN to the first query - this will give you all records in the Transaction table, and will show records in the Split query that are related to the each Transaction.

    Author Comment


    Thanks for quick response.  Database attached; apologies I though I was supposed to upload only if absolutely necessary.

    Thanks - K
    LVL 84

    Accepted Solution

    Okay - but it looks like your qryBankTran does what you want, you just need to change a few things.

    SELECT tblCat01.Cat01ID, tblBankTransaction.BankTranID, tblBankTransactionSplit.BankTranID, tblBankTransaction.Amount, tblBankTransaction.Analysis, tblBankTransactionSplit.AnalysisID, tblBankTransactionSplit.Amount, tblCat01.Cat01Name
    FROM tblCat01 RIGHT JOIN (tblBankTransaction LEFT JOIN tblBankTransactionSplit ON tblBankTransaction.BankTranID = tblBankTransactionSplit.MainTranID) ON tblCat01.Cat01ID = tblBankTransaction.Analysis;

    Open in new window

    I changed the Joins on the table to show all records from tblBankTransaction, and only those records from tblBankTransactionSplit that matched something in tblBankTransaction. I also changed the Join on tblCat01.

    I also added the Amount field from tblBankTransaction, so you've got both there now. If you wanted, you could use an IIF or SWITCH statement to show one or the other - but that's really an interface convention, and not a query convention. A query should return the data needed, and your interface should show what is needed (and hide what is not).

    To test this, open qryBankTran in SQL view and paste the query into the box, then switch back to Design or Datasheet view.

    Author Comment

    Great thanks Scott.  As you can probably tell I'm just starting to play with Access; Excel is my thing really.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now