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
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
ASKER
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
Thanks for quick response. Database attached; apologies I though I was supposed to upload only if absolutely necessary.
Thanks - K
Finance-V01.02.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks Scott. As you can probably tell I'm just starting to play with Access; Excel is my thing really.
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.