New kid on the block

[clerktreas]

Hi-

I am a self taught beginner in Access applications, so excuse my lack of expertise and terminology. From reading through this forum, I realize how far I have to go. I am not a techy by any means.

My question: I have a database that keeps track of the delinquent taxes for our Town. One of the reports is set up from 2 queries, then 2 related tables (beginning balances and payments made). The query calculates the balance of each account based on the beginning balance minus the payments made, and returns a report which shows each account balance, and then another query returns a report showing the total balance of all the accounts due for the date the report is generated.

I am wondering if there is a way to generate the balances due on a previous date. For example today is July 13, and I want to know what the balances were on July 1. Both tables have a date field- beginning balance has a start date, and the payments table has the dates of any payments.

Thanks in advance for any advice

 

[stephen81]

In your first query just add a parameter to the date field.
If you put

<=[Date:]

in the Criteria box for the 'payment made' date it will prompt for a date when you run the report and only return payments made before or on the date you enter.

 

[neileg]

First of all, it sounds like you've got the basic design set up, avoiding the mistakes most beginners make. Congratulations.

You need to add a criteria to your query that pulls up the payments records. Open the query in design view. In the column that holds the date, in the criteria row, type:

<= #[Please enter balance date]#

This will cause Access to prompt the user to enter the date they want the balance calculated.

A more elegant way is to create a user form to capture the date and display the data, but try the above first.

 

[clerktreas]

Thanks

Just wanted to thank neileg and stephen81-

I haven't been able to work on it yet but will let you know how it goes

 

[ScottGem]

Actually, there is a design issue that should be corrected. You should NOT have a separate beginning balance or payments table. What you should have is a transactions table like this:

tblTransactions
TransactionID (PK Autonumber)
TaxpayerID (FK)
TransDate
TransAmount
TransTypeID (FK)

You would then add a lookup table for Transtype:

tluTransType
TransTypeID (PK Autonumber)
TransType (Opening Balance, Tax Assessment, Payment)

With that structure you can get the record for any taxpayer as of any date.

 

[neileg]

Scott, I agree that using strict normalisation rules you are correct. However, separate tables are often used for opening balances, as well as adjustments and non-normal transactions. The reasoning behind this is often security (restricting changes to opening balances to supervisor level, for example). The only consequence is that you may need to union the tables to produce some information.

It's a design trade off, in practice.

 

[ScottGem]

Scott, I agree that using strict normalisation rules you are correct. However, separate tables are often used for opening balances, as well as adjustments and non-normal transactions. The reasoning behind this is often security (restricting changes to opening balances to supervisor level, for example). The only consequence is that you may need to union the tables to produce some information.

It's a design trade off, in practice.

I would also agree that in cases of security it might be easier to use a separate table. But there are ways to get around that as well. For example only allowing people with supervisory authority to add transtypes of Opening Balance or Adjustment. Regular data entry people would be restricted to entering assessments and payments.

I'm not the strictest normalizationist. But this is one area where I feel normalization is paramount. Any place where you need to calculate a running balance I would recommend the use of a transactions table with transaction types that identify the nature of the transaction. This is similar to an inventory application.