Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2018, 05:43 PM   #1
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Exclamation Build account tables for different companies

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?

ismafoot is offline   Reply With Quote
Old 05-16-2018, 04:40 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Build account tables for different companies

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
ismafoot (05-16-2018)
Old 05-16-2018, 06:35 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,210
Thanks: 123
Thanked 1,421 Times in 1,393 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Build account tables for different companies

Cross posted here http://www.accessforums.net/showthread.php?t=72072
with the final answers pointing the solution in the same direction.

To the OP - please have a read https://www.excelguru.ca/content.php?184

__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
ismafoot (05-16-2018)
Old 05-16-2018, 08:34 AM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,098
Thanks: 13
Thanked 204 Times in 202 Posts
Mark_ will become famous soon enough
Re: Build account tables for different companies

Quote:
Originally Posted by ismafoot View Post
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?
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
ismafoot (05-16-2018)
Old 05-16-2018, 09:07 AM   #5
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Re: Build account tables for different companies

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:
ismafoot is offline   Reply With Quote
Old 05-16-2018, 09:15 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,355
Thanks: 9
Thanked 3,733 Times in 3,676 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Build account tables for different companies

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
ismafoot (05-16-2018)
Old 05-16-2018, 09:21 AM   #7
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Re: Build account tables for different companies

Quote:
Originally Posted by plog View Post
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 is offline   Reply With Quote
Old 05-16-2018, 09:25 AM   #8
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Re: Build account tables for different companies

Quote:
Originally Posted by Mark_ View Post
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]))
ismafoot is offline   Reply With Quote
Old 05-16-2018, 09:40 AM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,098
Thanks: 13
Thanked 204 Times in 202 Posts
Mark_ will become famous soon enough
Re: Build account tables for different companies

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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
ismafoot (05-16-2018)
Old 05-16-2018, 09:56 AM   #10
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Re: Build account tables for different companies

Quote:
Originally Posted by Mark_ View Post
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...
ismafoot is offline   Reply With Quote
Old 05-16-2018, 10:44 AM   #11
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 238
Thanks: 0
Thanked 54 Times in 54 Posts
June7 is on a distinguished road
Re: Build account tables for different companies

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.
June7 is offline   Reply With Quote
Old 05-16-2018, 11:30 AM   #12
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,098
Thanks: 13
Thanked 204 Times in 202 Posts
Mark_ will become famous soon enough
Re: Build account tables for different companies

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.
Mark_ is offline   Reply With Quote
Old 05-16-2018, 01:16 PM   #13
ismafoot
Newly Registered User
 
Join Date: May 2018
Location: Canada
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
ismafoot is on a distinguished road
Talking Re: Build account tables for different companies

Quote:
Originally Posted by Mark_ View Post
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

ismafoot is offline   Reply With Quote
Reply

Tags
#dsum , #query , #table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Correct way to build tables? MattAnderson Tables 10 06-24-2014 06:18 AM
Build unique tables HCB Modules & VBA 2 11-22-2012 10:14 AM
Required Build Qty vs. Actual Build Qty - multiple tables new_2_prog Reports 0 06-10-2011 06:13 AM
Problem Query Wizard Build a select query by using tables with a many-to-many relatio fboehlandt Queries 15 09-08-2008 06:57 AM
Using SQL to build tables and set up constraints Chunk General 3 02-08-2005 11:13 AM




All times are GMT -8. The time now is 11:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World