• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Access Query from Multiple Tables

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
0
Kevinovitch
Asked:
Kevinovitch
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be a bit easier if you uploaded a sample database. Be sure to remove any sensitive data and such before doing so.

Basically:

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.
0
 
KevinovitchAuthor Commented:
Scott,

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

Thanks - K
Finance-V01.02.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
KevinovitchAuthor Commented:
Great thanks Scott.  As you can probably tell I'm just starting to play with Access; Excel is my thing really.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now