UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Basia Accounting Tables, Access 2007    
 
   
LeicsChris
post Mar 11 2018, 06:47 PM
Post#1



Posts: 120
Joined: 23-May 17



HI All

Since my project database records the sales and the staff wages the next stage would be to set it up to do the basic accounts.

For normalisation purposes, do you define the sales invoices and purchase invoices into separate tables or use the same table with a field in each record specifying if it is a sales/purchase?

What is your preferred option and why?

Thanks as ever

Chris
Go to the top of the page
 
GroverParkGeorge
post Mar 11 2018, 07:07 PM
Post#2


UA Admin
Posts: 33,013
Joined: 20-June 02
From: Newcastle, WA


One Invoice table. An InvoiceType field in that table.

That's the normalized way to do it. Normalization is the key to a useful datatbase.

--------------------
Go to the top of the page
 
nvogel
post Mar 12 2018, 12:46 PM
Post#3



Posts: 852
Joined: 26-January 14
From: London, UK


I guess this is an academic exercise rather than a real project? If it is for real life use then you should buy an accounting system and not build one yourself. Accounting systems typically have one common table for the common fields across all ledgers, including both accounts payable and receivable.

Your question isn't strictly a normalization problem at all. If table X satisfies Fifth Normal Form then 2,3 or 100 identical copies of X under different names will also satisfy Fifth Normal Form. Even though it isn't a normalization problem, having multiple tables that are identical or "overlapping" (partly the same) is usually a bad idea because you'll find it more difficult to get accurate results out of your database and you may require multiple copies of any logic that operates on the data.

Chris Date and David McGoveran defined something called the Principle of Orthogonal Design (POD). POD is a formal design rule that proscribes table designs that overlap each other.



Go to the top of the page
 
GroverParkGeorge
post Mar 12 2018, 01:58 PM
Post#4


UA Admin
Posts: 33,013
Joined: 20-June 02
From: Newcastle, WA


Thanks for the clarification.

--------------------
Go to the top of the page
 
LeicsChris
post Mar 12 2018, 02:53 PM
Post#5



Posts: 120
Joined: 23-May 17



Hi guys

Thanks for the answers

It wasn't academic, I currently use my own access application and quickbooks in my small business.

Quickbooks is simpler to use than sage I understand but being built to accomadation many types of business and having many functions we don't need I was considering extending my application to also incorporate the accounts.

I asked the question as I was thinking through the process of adding accounts should I decide to try this route.

On the other hand, great research and experience has gone into commercial products

Thanks guys
Go to the top of the page
 
projecttoday
post Mar 12 2018, 09:10 PM
Post#6


UtterAccess VIP
Posts: 9,818
Joined: 10-February 04
From: South Charleston, WV


I'm going to put in 2 cents worth. How often are invoices and purchase orders used together? (I think not much.) If you combine them, every time you read an invoice or a purchase order you have to check the type field. Every time. To what avail? If you do need them together you can always UNION them or combine them some other way.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
gemmathehusky
post Mar 13 2018, 05:40 AM
Post#7


UtterAccess VIP
Posts: 4,434
Joined: 5-June 07
From: UK


Personally, I would have separate tables

If you want to relate the invoice tables to other tables, then the relationships will be completely different for each type, and you will struggle if all invoices are in one table. eg I doubt you will be able to enforce RI. Also, although similar, you are likely to need some different fields for purchase and sales invoices.

QUOTE
@leicschris
On the other hand, great research and experience has gone into commercial products


I'm not sure about that, Chris. Accounting isn't hard to do. I wrote a fully featured general ledger (complete with reversing journals etc) in about 2 days.
I didn't need a SL or PL, but that wouldn't take any longer.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
projecttoday
post Mar 13 2018, 06:50 AM
Post#8


UtterAccess VIP
Posts: 9,818
Joined: 10-February 04
From: South Charleston, WV


Some good points.

SL - sales ledger? PL - purchase ledger?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
gemmathehusky
post Mar 13 2018, 07:51 AM
Post#9


UtterAccess VIP
Posts: 4,434
Joined: 5-June 07
From: UK


@projecttoday

yes. you say AR, AP, in the US, don't you?
receivables, payables.


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
projecttoday
post Mar 13 2018, 07:52 AM
Post#10


UtterAccess VIP
Posts: 9,818
Joined: 10-February 04
From: South Charleston, WV


Yes.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
TinyGiant2010
post Mar 28 2018, 03:58 PM
Post#11



Posts: 172
Joined: 1-June 10



2 days Dave? Wow - you get my respect for sure!

I'm contemplating working up a billing system. If I get to take over as Treasurer of my association, I will need to send out about 300 bills a year for member dues; all at once naturally. It is just item description (five/six line items of categories of charges), quantity always =1, Price, and to whom I send it. Naturally, I will need a system to "check register" it as the members pay up; maybe create a deposit slip.

Additionally, I would charge an initiation fee, orientation fee, and maybe a couple of other surprise fees (or the capabilities to add them). My accounting is pretty rusty; an understatement! lol.... anyway

I tried to do all of this several years ago, but determined that sub-forms were over my head.

After creating that; I'd probably have to make some reports showing receipts by region.

I've just re-opened Northwind and I'm mucking thru that morass. Evidently it won't let me bill for something I don't have in inventory. I can't add to inventory without having someone to buy from. I can't buy from them unless I know what they are selling; can't enter a thing unless I am an employee, can't approve unless I get approval permissions --- not necessarily in that order. Not sure how 5/6 categories of professional services billings works into it; but maybe I'll figure theirs out first. I just get impatient with Northwind due to it's lack of a good switchboard and progression thru the steps in logical order. How you might figure that out in two weeks, let alone 2 days, blows me away.

Well done!

.... as I think on this; maybe I will take the simple solution! I will just use a pre-printed invoice since they would all be pretty much identical; and just merge the member name data to the top of the page. As funds get paid, I can create a check register easily enough. They don't get to pick and choose how much to pay or make fractional payments or get re-billed for outstanding amounts (as far as I know). So how hard could this be? (That's one of my private jokes - the answer is always harder than it looks!)
This post has been edited by TinyGiant2010: Mar 28 2018, 04:03 PM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
tina t
post Mar 28 2018, 04:21 PM
Post#12



Posts: 5,451
Joined: 11-November 10
From: SoCal, USA


QUOTE
They don't get to pick and choose how much to pay or make fractional payments or get re-billed for outstanding amounts (as far as I know).

there's a phrase that's notorious for coming back to bite you in the tushie. if you're going to do it, and your organization is going to depend on it, then take the time to do a process analysis and get it right - who knows what else you may be assuming without confirming, even without really realizing it.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 10:26 AM