Co-Operative Housing Society Records

Tables.

1 Banks
2 Cash Opening Balance
3 Expense Types
4 Fixed Deposits
5 Members
6 Opening Balance
7 Transaction Types
8 Transactions
9 Year Month

Banks

Cash Opening Balance

opening Balance Autonumber long Integer Unique Primary Key
Date Date/Time Short Date  Input Mask
Balance Currency Currency Auto
Year month Number Query derived
       

Expense Types

Expense Type Number Autonumber Long Integer Primary Key
Expense Name Text    

Fixed Deposits

Field Names are to be used according to the needs of the information.  The Primary Key is essential.

Members

Opening Balance (Banks)

Opening Balance Autonumber Long Integer Primary Key  
Bank Name Number Lookup Wizard Table Banks  
Date Date/Time Short Date Input Mask Use first of month
Year month Number Lookup Wizard Table Year Month  
Balance Currency Currency   To be input

Transaction Types

2 fields.  Autonumber

      and Tr. Type.

Transactions

What is important in the selection of the fields is the type of information the user may need.   Not everything need to added at first.   In trials, use less information, and get to know the use of the program and its intricacies.  You will be able to refine it later.

Combo Box

A drop down list is contained in a combo box.  It is generated by the Look Up Wizard.

In the above picture you see: Bound Column = 1 which means only one field is to be stored.

Count Columns are 2.   One which will be stored, the number, and the other which will be displayed, the Bank Name.

Column Widths: (of the counted columns).   First is 0"   and the second one is  2" separated by a semi colon.

The List Rows and  Width determine, how many rows will be displayed and how broad these have to be.

Year Month Table

yearmonth Autonumber Primary Key Unique
yearmon text    
Monthyear text    

Queries

1 balance query Table: Banks and Opening Balances
2 Cash Opening Balance Query Table: Cash Opening Balance
3 Expense Type Query Table: Expense Types
4 members name query Table Members
5 Opening Bank Balance Table Banks, Opening Balance
6 transactions query 2 Table Transactions with criteria
7 transactions query 3 Table Transactions with criteria

bank balance query

openbankbalance  query

Cash Balance query

expense type query

membersname query

Note the field Name is derived from two fields of the members table.  There should be a color after the derived name.  the table fields should be in square brackets.  They should be joined with &" " &

The Quotation marks create a distance of one space between the names.

transactions query 2

By selecting Transaction ID 2 or 3, we take Cash/Bank type and Bank type.

Transactions query 3 is same as above, but with criteria is 1 or 3.   This way you select Cash and Cash/Bank transactions only from the Transactions Table.

Forms

Design a form in Design view with "openbankbalance" query as the record source.

In the lower have design a sub form in data sheet view from query: transactions   query 3.

In the top add the command buttons - Close Form = Form Operators, Close Form, Text Display = "Close Form"

Calculator: = Form Operators, Open Form = "Calculator"

Find Difference = Form Operators, Open Form = "Calculator 2"

Sub Form

Record Source: transactions query 3

Two unbound text boxes in the Form Footer, calculate the sum of the currency totals Breceipt and Bpayment.

They are given "other" name to identify them in the main form.  Breceipt has deptot as its other name and Bpayment has withtot as its other name.  Right click on the text box, click on Other Tab and give the name.

Click on the form properties square on top left corner, and get this property card

Change:  Default View. Views allowed, record selectors, navigation buttons.  as in the above picture.

you should get