I'm out of my depth! Check register...table or query or? 2

[gusbrunston]

As they say, "Fools rush in where angels fear to tread." (At least they say that to me.)

I've a table with records of "Checks".
I've another table with a list of "Deposits" (based on a table called "DepositDetails&quot.

I need to create a report (?) as a "Register".

The checks are not a problem--one check, one record.

The deposits have merely an autonumber and a date.

The depositAmount (I suppose) must be calculated from the "depositDetails" (identified with the deposits autonumber key).

I have successfully saved the depositAmount in the input form with =[frmDepositDetails Subform].Form!DepositSum as the control source.

Can anyone steer me getting toward getting this value in a table or query to include in a check register (table or query?)

I'll be grateful for as long as I live, and who knows...maybe longer than that.

Gus Brunston
An old PICKer
padregus@home.com

 

[PamelaG]

Hi Gus!

The first question that comes to me is - what is it that unites the checks and the deposits? Is there an account number that ties everything together? Or is this a database for only one account?

Anyway, sounds like what you are trying to do is to produce a report. Your current table layout is going to make that a little difficult, if not impossible. I would like to suggest the following structure instead:

tblTransactionTypes
===================
TransactionTypeKey
TransactionTypes

The records in this table will be "Deposit" and "Check". You can also have transfers and fees if you like.

The next table would be:

tblTransactions
===============
TransactionKey
TransactionTypeKey (this is a foreign key)
TransactionDate
TransactionAmount
TransactionNumber (this might be a check number)

If you want to break down the deposits, then you need:

tblTransactionDetails
=====================
TransactionDetailKey
TransactionKey (this would be a foreign key)
TransactionDetailAmount
TransactionDetailSource

etc.

What do you think?

Pamela

http://www.accessinsight.com

 

[itauditor]

Hi Gus, I agree with Pamela's approach, except why not even put checks and deposits all in the same table? You can use a unique identifier (such as check number and random deposit number) that can be used to group items on a report (if deposit 1 had 3 items, then label them all "1" and the report will group them together). Also, you may have one column for the type of transaction (check, deposit, fees, etc.) and a column for the date or other items that Pamela discusses. If you need to report checks and deposits separately, then you can develop a query that only picks up the specific type of transaction. If you want to develop a form to populate the data, I also have some ideas. Just send me an email...Hope this helps.

 

[PamelaG]

Just to clarify, yes, all checks AND deposits AND transfers etc. will go into that tblTransactions. And then, as you said, you can print out the register grouping by date, month, type etc.

This is a common structural problem people run into. Two things appear to be different and therefore surely they must go into separate tables, when in fact they are simply two "kinds" of the same thing. The way to avoid this problem is to ask yourself if two tables could be grouped into a single category. If so, then you just need one table and a "type" indicator as in the example above!

Pamela

http://www.accessinsight.com

 

[gusbrunston]

Thank you so much, PamelaG and itauditor! Your comments are appreciated.

I'm kind of proud that I anticipated a couple of the things you suggested: today I decided to put checks and deposits in the same table, and enter them from the same form. I even got so far as to write a reasonable "checkbook" report, and another report that prints out the "deposit details" (really important for this database.) And so far, I've avoided storing a single calculated value!

I learned to append (by query) vendors, owners and tenants into a new "payees" table to get info for writing checks, etc., and another for "payers" to get the data for who is paying and for what.

I'm comin' along, and the help I get on Tek-Tips is no small part of the reason.

Thanks again!
Gus Brunston
An old PICKer
padregus@home.com

 

[PamelaG]

Hi Gus!

Sounds good! Except that you are about to head down the same road again if you have separated Payees and Payers into two tables. If you think about that for a minute, and consider that a Payee can ALSO be a Payer, I think you'll see what you need to do!

PamelaG

http://www.accessinsight.com

 

[gusbrunston]

Thank you, Pamela, again. Yep, I have to make the same mistake again and again before I see the light! Somewhere I heard that insanity is doing something the same way over and over and expecting different results.
Gus Brunston
An old PICKer
padregus@home.com

 

[PamelaG]

Good going, Gus! This is going to be super-obvious stuff to you eventually, so hang in there!

Pamela

http://www.accessinsight.com