Build account tables for different companies (1 Viewer)

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
Hi all,

Im having difficulty trying to map out the ideal table composition for accounts of companies.

Basically a company usually has 4 accounts, and each account must have the following column headers:
Account 1: Manufacturing Facility in US dollars.
- date
- advances (positive amounts)
- payments (negative amounts)
- ID
- net (advances - payments)
- running outstanding balance

What I started to do was build a table with date, advance, payment and net and tried to build a query for the running outstanding balance but it caused me too much trouble. Firstly, I will enter transactions, I can have input a record with a value date in the past. I entered different variations of DSUMs field functions in my query and never got the right one. I was wondering if there would be an easier way of creating this whole database.

Any suggestions please?
 

plog

Banishment Pending
Local time
Today, 16:32
Joined
May 11, 2011
Messages
11,611
You kinda lost me with all your words, but from what I gather you are trying to set up a table to track a balance. The way this is done is with a simple table, a simple query and then the running balance is a semi-complex query. The table looks like this:

Transactions
Trans_ID, autonumber, primary key of table
ID_Account, number, foreign key to Accounts table
Trans_Date, date, date of transaction
Trans_Amount, number, will hold the amount of the transaction (debits will be negative numbers)


That's it for the table, it will now do everything you want. Then for current balance you do a simple aggregate query:

SELECT ID_Account, SUM(Trans_Amount) AS Balance FROM Transactions GROUP BY ID_Account

Then for a running balance on any gven day you can search this forum for the term 'running balance query'. It's been asked/answered thousands of times on here.
 

Mark_

Longboard on the internet
Local time
Today, 14:32
Joined
Sep 12, 2017
Messages
2,111
Hi all,

Im having difficulty trying to map out the ideal table composition for accounts of companies.

Just to be clear, you are NOT trying to work out journal entries for an actual accounting system, correct?
 

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
Thats exactly what I am trying to do. The companies can have between 2-10 different accounts and I need to have a table for each account. Each account is in essence a journal entry : debit = payment ; credit = advance. So for each table (i.e. account), there must be a query and a report displaying the outstanding balance every time there is a record. Problem is: people can enter records with a past date value. I couldnt get around it with my query... So im still incredibly stuck...

this is what it would look like:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:32
Joined
Aug 30, 2003
Messages
36,118
Post 5 was moderated, I'm posting to trigger email notifications to the people who will tell you not to have a table for each account. ;)
 

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
You kinda lost me with all your words, but from what I gather you are trying to set up a table to track a balance. The way this is done is with a simple table, a simple query and then the running balance is a semi-complex query. The table looks like this:

Transactions
Trans_ID, autonumber, primary key of table
ID_Account, number, foreign key to Accounts table
Trans_Date, date, date of transaction
Trans_Amount, number, will hold the amount of the transaction (debits will be negative numbers)


That's it for the table, it will now do everything you want. Then for current balance you do a simple aggregate query:

SELECT ID_Account, SUM(Trans_Amount) AS Balance FROM Transactions GROUP BY ID_Account

Then for a running balance on any gven day you can search this forum for the term 'running balance query'. It's been asked/answered thousands of times on here.

Unfortunately, I can not have it all in one table. I must have a table for each account:

For example : one for canadian $ , another for euro, another for USD and so on.
 

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
Just to be clear, you are NOT trying to work out journal entries for an actual accounting system, correct?

Thats exactly what I am trying to do. The companies can have between 2-10 different accounts and I need to have a table for each account. Each account is in essence a journal entry : debit = payment ; credit = advance. So for each table (i.e. account), there must be a query and a report displaying the outstanding balance every time there is a record. Problem is: people can enter records with a past date value. I couldnt get around it with my query... So im still incredibly stuck...

it should look like:
Canadian $ account: table, form , query , report
USD: table, form query, report
Euro: table, form query , report

table : date, id , payment, advance
form : input data
query: calculate outstanding balance for each record ( maybe use DSUM but my formula isn't working because records can be inputted with a past date value

Balance: FormatCurrency(DSum("[Advance] + [Payment]";"Revolving CAD";"[Transaction Date]<=#" & [Transaction Date] & "# AND [ID]<" & [ID]))
 

Mark_

Longboard on the internet
Local time
Today, 14:32
Joined
Sep 12, 2017
Messages
2,111
OK, journal entries would have
JournalID - Autonumber - Primary Key
EntryDate - Date - Date entered into system
EffectiveDate - Date - Date transaction is effective - allows for entering entries after the fact.
JournalNote - Text - textual description of Journal Entry

This will have CHILD RECORDS.
Each child record will have parent ID, Account, CreditAmt, DebitAmt.

To be GAAP compliant, All CreditAmt and DebitAmt for a given entry must equal.

For the child entries you may be advised to use a "Credit/Debit" flag. I'd not as accountants will be the ones doing audits and they will expect separate columns for each.

From your initial post I am guessing you are not yourself an accountant. I'd talk to one prior to going further. Your T-Accounts will be simple to set up, but setting up the chart of accounts (and how you want to reflect them in software) can get very complicated very quickly. Make SURE you know what you are trying to do prior to laying out the tables. Also make sure of your scope of work and any other systems this needs to work with.

Once you get into journal entries you step into a very different world than most programming is involved with. Journal entries lead to T-Accounts, T-Accounts lead to Charts of accounts, Charts lead to things like balance sheets and making sure that Assets = Liabilities + Owners Equity.

All of this also leads to people pointing fingers (and potential litigation) at you if something doesn't add properly.

NOTE. For amounts, you can have a "Currency" field for the COMPANY. This tells you which currency a given company bases its transactions off of. I would stay totally away from mixed-currency systems.
 

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
OK, journal entries would have
JournalID - Autonumber - Primary Key
EntryDate - Date - Date entered into system
EffectiveDate - Date - Date transaction is effective - allows for entering entries after the fact.
JournalNote - Text - textual description of Journal Entry

This will have CHILD RECORDS.
Each child record will have parent ID, Account, CreditAmt, DebitAmt.

To be GAAP compliant, All CreditAmt and DebitAmt for a given entry must equal.

For the child entries you may be advised to use a "Credit/Debit" flag. I'd not as accountants will be the ones doing audits and they will expect separate columns for each.

From your initial post I am guessing you are not yourself an accountant. I'd talk to one prior to going further. Your T-Accounts will be simple to set up, but setting up the chart of accounts (and how you want to reflect them in software) can get very complicated very quickly. Make SURE you know what you are trying to do prior to laying out the tables. Also make sure of your scope of work and any other systems this needs to work with.

Once you get into journal entries you step into a very different world than most programming is involved with. Journal entries lead to T-Accounts, T-Accounts lead to Charts of accounts, Charts lead to things like balance sheets and making sure that Assets = Liabilities + Owners Equity.

All of this also leads to people pointing fingers (and potential litigation) at you if something doesn't add properly.

NOTE. For amounts, you can have a "Currency" field for the COMPANY. This tells you which currency a given company bases its transactions off of. I would stay totally away from mixed-currency systems.

I absolutely agree. However im not trying to build an accounting software because the project is more of a data entry database which is not required to be compliant in anyway. Its purpose is more for recording transactions we made (money coming in and money coming out). So it doesn't need to have a debit/credit entry. Simply the amount, the date it was done and whether it was coming in or out...
 

June7

AWF VIP
Local time
Today, 13:32
Joined
Mar 9, 2014
Messages
5,423
I agree with Mark. One table for accounts and a CurrencyType field. Regardless if 1 or 4 tables, the issue is how to calculated balance. As plog said, been asked and answered. Calculating in query is tricky; however, fairly simple in a report. Textbox in report has RunningSum property.
 

Mark_

Longboard on the internet
Local time
Today, 14:32
Joined
Sep 12, 2017
Messages
2,111
Were I you, I'd make SURE there is nothing in your spec that requires you to be compliant for audits/interface with accounting system.

If this is a system for tacking receipts, great. If you need to balance actual money against it, make sure your specification is very clear on what you are doing/why.

IF you have no fiscal responsability, then

TableID - AutoNumber - Primary Key
AccountID - Number - Foreign Key to Account table (Account links back to Company in your scenario)
DatePosted - Date - When the transaction was entered into the system
DateEffective - Date - When the transaction occured
Currency - Number - Linked to lookup table for currencies
DebitAmt - amount for cash in
CreditAmt - amount for cash out

This allows you to easily total amount in VS amount out as they are in separate fields. You COULD use one with a positive/negative value, but that can make some of the other reporting you may need more difficult.

You would NOT keep either a "Net" or "Running balance" as both are date specific. Instead you would have queries that total Debit/Credit amounts and compute these as needed.

Please note, since you are tracking "CASH" (an ASSET account), using debit transactions to increase the balance matches to accounting practices. This will avoid some issues later should you need to have this reviewed by an accountant.
 

ismafoot

New member
Local time
Today, 17:32
Joined
May 16, 2018
Messages
6
Were I you, I'd make SURE there is nothing in your spec that requires you to be compliant for audits/interface with accounting system.

If this is a system for tacking receipts, great. If you need to balance actual money against it, make sure your specification is very clear on what you are doing/why.

IF you have no fiscal responsability, then

TableID - AutoNumber - Primary Key
AccountID - Number - Foreign Key to Account table (Account links back to Company in your scenario)
DatePosted - Date - When the transaction was entered into the system
DateEffective - Date - When the transaction occured
Currency - Number - Linked to lookup table for currencies
DebitAmt - amount for cash in
CreditAmt - amount for cash out

This allows you to easily total amount in VS amount out as they are in separate fields. You COULD use one with a positive/negative value, but that can make some of the other reporting you may need more difficult.

You would NOT keep either a "Net" or "Running balance" as both are date specific. Instead you would have queries that total Debit/Credit amounts and compute these as needed.

Please note, since you are tracking "CASH" (an ASSET account), using debit transactions to increase the balance matches to accounting practices. This will avoid some issues later should you need to have this reviewed by an accountant.

Thank you very much Mark
 

Users who are viewing this thread

Top Bottom