Looking for Treasurers database

[arabiflora]

Hope this is the appropriate forum for this post. I'm looking for an Access db to work from that I could use to track finances of my local Lions club. Presently using Excel spreadsheets but integration of various expense categories and cash flow is a nightmare. A true db app would seem a better solution and I'm guessing that others have already tackled this problem, any pointers appreciated.

TIA

 

[Sergeant]

That's a really good question. I was recently elected treasurer of my Squadron's Booster Club. I created my accounting file in excel, using a user form to post debits and credits. I could post you an example if you want, but I'd much rather see how someone would handle this in Access.
If no one has one in Access, give me some ideas and I'll build it (I just can't get my head around the approach).

Sarge.

 

[Sergeant]

So what do you all think? Should debits and credits be stored in the same field (as - & + amounts), or one field for each?

Sarge.

 

[Crilen007]

Yes, same table. Just have a type flag.


IE:

tblTrans
-----------
TransID (AN,PK)
PersonID (FK)
TypeID (FK)
Amount (Number)


TransID   PersonID   TypeID   Amount
------------------------------------
   1          1         1      $  100
   2          2         1      $  100
   3          2         1      $  200
   4          7         2      $ -200
   5          3         2      $ -300

tblType
----------
TypeID (AN,PK)
Type

TypeID   Type
--------------
   1      Debit
   2      Credit

 

[Rich]

But then if you have separate Credit Debit fields it's easier to sum them individually

 

[sportsguy]

Quicken

the soccer club that i was a director, had six figures in assets,
just used quicken to do all the bookkeeping. . .

simple enough

sportsguy

 

[Sergeant]

That's not a bad idea, just use Quicken.
It's just that I'm always looking for ways to use (and defend) my Office skills, you know.

Sarge.

 

[MacFil]

I have one

I have a small database that I created for my wife for her small business. It's only for expense. I still need to add the deposit part.

I use Quickbook 2005 but it was not working for me. 95% of expenses were paid cash. I will be posting a copy of the database over the weekend.

JP

 

[Sergeant]

Thanks, I'd like to take a look at it.

Sarge.

 

[MacFil]

Just keep in mind that's I'm just a beginer. I'm learning sooooo much from this forum.

 

[Sergeant]

Me too!

Sarge.

 

[Sergeant]

I've been giving this some more thought.
As far as using debit and credit fields, or just one field with positive or negative numbers...
At first, I thought using one field would make report creation a nightmare, but then it struck me that I could put two of the "TransactionAmount" text boxes on the report and use conditional formatting to show the appropriate text for the column.

Does this sound logical?

 

[Sergeant]

Scratch that idea. How would you do conditional sums on the report? You would want to be able to sum all the debits and credits separately.

Thoughts?

 

[Pat Hartman]

I would use a slightly different structure than the one previously suggested by Crilen007:

tblTrans
-----------
TransID (AN,PK)
PersonID (FK)
TypeID (FK)
TransDate (date/time)
Amount (Number)

tblType
----------
TypeID (AN,PK)
Type
DebitCD ("D" = Debit, "C" = Credit)
Sign (+1 or -1) to indicate true sign. This allows for a straight multiplication of the amount rather than an IIf() to perform calculations. (Amount * Sign)

TypeID   Type     D/C  Sign
--------------------------
   1      Deposit     D     +1
   2      Expense    C     -1
   3      Debit Adj   D     -1
   4      Credit Adj  C     +1

This method allows all calculations to be controlled by the data stored in the Type table.

 

[Sergeant]

Intriguing!

Clever use of the +1 and -1, as you don't need two amount fields where one would always be empty. (Bad form, right?)
You could use calculated fields for reports' debit and credit columns... iif(Sign > 0,TransactionAmt * Sign, "") for credit column.
Is that the hard way? (I have a way of finding it.)

Sarge.

 

[Pat Hartman]

Nope, that's what I would do when you want to show debits and credits in separate columns.