MS access - Unique table for several cash & bank accounts

[Amos]

Dear Sirs

I am trying to build a cash flow software, first I thought to build one
table for each cash and bank account, but talking to some people they
suggested me to build one unique table for all bank and cash accounts,
putting one more column to identify each bank or cash account.
The unique table has this columns structure:

ID Date B/C account FinCode Description Debit
Credit


I would like to know:

ID Column is an automatic meter from 1 to "99...", that has to get to the
end of each year for each bank and cash account identified from the B/C
column.
Do you think I can manage it without create one table of each bank and cash
account?
How I can build the B/C account in order to get the above said result.



Thank you for your cooperation.




Amos

[MGFoster]

Re: MS access - Unique table for several cash & bank accounts

Amos wrote:[color=blue]
> Dear Sirs
>
> I am trying to build a cash flow software, first I thought to build one
> table for each cash and bank account, but talking to some people they
> suggested me to build one unique table for all bank and cash accounts,
> putting one more column to identify each bank or cash account.
> The unique table has this columns structure:
>
> ID Date B/C account FinCode Description Debit
> Credit
>
>
> I would like to know:
>
> ID Column is an automatic meter from 1 to "99...", that has to get to the
> end of each year for each bank and cash account identified from the B/C
> column.
> Do you think I can manage it without create one table of each bank and cash
> account?
> How I can build the B/C account in order to get the above said result.[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The people you talked to are correct, you should have only one table for
the transactions. I'd suggest you have a table like this (JET DDL):

CREATE TABLE Journal (
JournalID COUNTER , -- Autonumber. In SQL'r IDENTITY(1,1)
JournalDate DATETIME NOT NULL , -- transaction date
-- perhaps the time will be stored, so there
-- can be more than one xaction per day.
AccountNo INTEGER NOT NULL , -- Can be a FK to the Accounts table
FinCode INTEGER NOT NULL , -- Not sure what this is, but
-- probably can be a FK to a lookup table
DR_CR SMALLINT NOT NULL , -- 1 for CR & -1 for DR
Amount MONEY NOT NULL , -- the transaction amount, always a + value
Descrip VARCHAR(50) ,
CONSTRAINT PK_Journal PRIMARY KEY (JournalDate, AccountNo, FinCode,
DR_CR) ,
CONSTRAINT FK_AccountNo FOREIGN KEY (AccountNo) REFERENCES Accounts ,
CONSTRAINT FK_FinCode FOREIGN KEY (FinCode) REFERENCES FinancialCodes
)

Don't use the word "Date" as a column name - it is also an SQL keyword.

The Accounts table would hold the AccountNo and the AccountName (&
possibly, if the account is an asset or liability account).

The FinancialCodes table would hold the financial codes (letter or
number) and their descriptions.

If you choose to include the Time in the JournalDate (really a
Transaction Date/Time value) you will be able to have more than one
entry per day for each AccountNo, FinCode and DR_CR value.

Creating a G/L db (in essence - what you're doing) is a big project.
You may be able to find demo G/L software on the web. Below is a URL I
found by Googling (search strings: "general ledger" "MS Access"):

http://www.accountingsuccess.com/gl.html

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHdQXIechKq OuFEgEQLJuACfTS zCjiV1Wwzyz+IKk y5AnYPWPCgAnRGw
I1yMe7c/MEk8Usce5H9WxLl w
=zPaz
-----END PGP SIGNATURE-----