473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

MS access - Unique table for several cash & bank accounts

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
Nov 12 '05 #1
1 4954
Amos wrote:
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.


-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQHdQXIechKqOuFEgEQLJuACfTSzCjiV1Wwzyz+IKky5AnY PWPCgAnRGw
I1yMe7c/MEk8Usce5H9WxLlw
=zPaz
-----END PGP SIGNATURE-----

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Amos | last post by:
I built the following Sub, in order to create tables automatically. The button is placed on a mask where there are 3 txt box, two of them contain the "Bank name" and "Bank a/c". I want to name the...
1
by: Amos | last post by:
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...
16
by: Rob Geraghty | last post by:
I've just spent some time looking through FAQ sites and searching the google archives of this newsgroup, but I still haven't been able to find a clear explanation of an issue with multi-user...
3
by: mazubair | last post by:
I am going to develop a Banking Solution for very small branches of a Bank. The branches are standalone and no remote transactions will be made i.e. only the individual branch will be under...
2
by: mazubair | last post by:
I am going to develop a Banking Solution for very small branches of a Bank. The branches are standalone and no remote transactions will be made i.e. only the individual branch will be under...
1
by: dimitri | last post by:
i try to copy data from excel into my MySql database which runs on a linux. i use MS Access to access the database. i need to optain the unique id number, and this is where i have some troubles....
10
by: Robert | last post by:
I am an attorney in a non-profit organization and a self-taught programmer. I'm trying to create a client db that will allow me to search for potential conflicts of interest based either on Social...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
2
by: cbs81 | last post by:
hi all, problem: transferring data from workbooks that are stored in a particular directory from excel to a table in access. when done, the workbooks that have been processed are automatically...
5
by: Liquidtouch | last post by:
I'm not much of a HTML or Javascript programmer but have a little experience just hacking away at it. I am creating a HTML application and would like to be able to add or remove rows of a table....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.