473,842 Members | 1,481 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5006
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:::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-----

Nov 12 '05 #2

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

Similar topics

4
5383
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 new table with a string composed by the "Bank name"&"Bank a/c", How I could get it? Using the following Sub I get always the same name "NEWBANKAC". Any Tip is welcome. Thank you.
1
495
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 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
16
4889
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 databases. Essentially I have two questions; 1) Does the system.mdw file have any significance to multi-user sharing of an Access 97 database other than security? 2) Can any number of users open an Access 97 database using the same
3
5473
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 networking and there will be no networking between the branches. The branches have maximum 10000 (ten thousand) customer accounts, maximum 200 daily (60000 yearly) transactions and maximum 3 to 4 concurrent users. The solution will provide voucher...
2
2295
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 networking and there will be no networking between the branches. The branches have maximum 10000 (ten thousand) customer accounts, maximum 200 daily transactions (120000 yearly including processed transactions i.e. interest, charges etc.) and maximum 3...
1
2675
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. how can i either insert data, so my unique id number is continuing or, how can i access my MySql database with MS Access without a "Unique Record Identifier"?
10
2117
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 Security # or on Last Name. I've created two different tables with the following fields in each table: ClientInfo Client# (primary key) First Name Middle Name Last Name
4
12456
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 this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
2
1848
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 moved into a directory named “processed” . all using vba. this is the pretty complex vb problem that i need some expert solution to. overview. i have created a cash reconciliation application in excel saved by month for each of my bus...
5
3213
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. The row that gets added will need to contain 2 columns, 1 which contains an IFRAME, and the other will contain a Javascript Menu which will be used to select the content displayed in the IFRAME. The purpose of the tool is to be used as a video...
0
9876
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10950
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10617
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10681
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10318
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7862
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7045
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5699
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4096
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.