| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Build a financial Databace
Expert: Scottgem
Date: 9/6/2008
Subject: Build a financial Databace
Question
QUESTION: Hi Scottgem!
I have made 3 tables in access:
members:
IDmember (PK)
Fname
Lname
Transactions:
IDmember
IDtransaction (PK-Autonumber)
TransDate
TransDetails:
IDtransaction
TransKind (1: issue, 2:deposite)
TransAmount
Balance
I want to make a form like below:
IDmember:5
IDtransaction TransDate TransKind TransAmount Balance
1 08/09/03 1 100 100
2 08/09/04 2 20 80
3 08/09/05 1 200 280
Would you please tell me how should I make it?
Thanks in advanced,
christina
ANSWER: Start off with a Main form bound to the Members table. Then create a subform bound to a query on the transactions table in continuous form mode and linked on IDMember.
You should not have a Balance field since Balance is a calculation and, as a general rule, we don't store calculated values. Look up Running Sum in Access Help to see how to create a query that will display the balance. That's what you use for your subform.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Hi Scottgem,
Thank you very much for your help.
I have confused on this simple project, because I am thinking about it more than 1 month.
It may seems silly but I could not solve the problem. I could not find what is the correct design for this project and what tables or queries (with relationships) should I make?
Would you please design a database for me that :
1- save personal information of customers like table Members
2- save transactions information like table Transactions
3- save transactions details like table TransDetails
4- show a form that when I enter the member ID it shows member personal information and a auto transaction ID and some blank textboxes for entering Transaction date, transaction Type and transaction Quantity. after entering transaction Quantity and pressing tab button and lost focus from transaction Quantity textbox on this form, it shows balance of the member.
5- make a report that shows a member transactions history for a certain number of his transactions.
(As you told I Looked up Running Sum in Access Help to see how to create a query that will display the balance but I could not find about Balance in Access help)
Sample Tables and report that I mean:
Tables:
1) Members:
IDmember (PK)
Fname
Lname
2) Transactions:
IDmember
IDtransaction (PK-AutoNumber)
TransDate
3) TransDetails:
IDtransaction
TransType (1: Receipt, 2: Issue)
TransQuantity
Report:
IDmember:5
Member First Name:X Member Last Name:Y
List of n last Transactions
IDtransaction TransDate TransType TransQuantity Balance
1 08/09/03 1 100 100
2 08/09/04 2 20 80
3 08/09/05 1 200 280
Thanks in advance,
Christina
Answer
Are you asking to contract for my services in designing this database? If so, I would be happy to quote you a cost for doing so. But that's beyond the scope of a site like this.
If you want a quote, please send me an e-mail with the info you posted above to scottgem@diamondassoc.com. Right now, I'm pretty busy and probably couldn't get to work on this project until next month. But I can get you a quote in a few days.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer
Ask a Question
|
|
