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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


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

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

Thanks - K
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinovitchAuthor Commented:
Great thanks Scott.  As you can probably tell I'm just starting to play with Access; Excel is my thing really.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.