PDA

View Full Version : Counter Loop statements



magsroq
06-07-2004, 12:01 PM
I'm wondering if anyone can give me a hand in writing a counter loop statement. I have recently been given control of a database that needs some revisions.

Here's what I have:

A sample of loans (yep, I work at a bank)
A group of users who review these loans
A form that calculates how many reviews each user should complete each month
Forms into which the users enter review data every month (AKA Review forms)
The ability to populate the Review forms with the loan information

Since each user completes a different number of reviews each month, I have to calculate that number after I pull my sample. (Otherwise, I would have to redo the entire database, and that's just not going to happen.)

Here's what I need:

A way to tell the database to populate the Review forms with the user's name, but only the appropriate number of times without having to go in and change the code every month. For example, Kevin needs to complete 10 reviews next month out of 100 reviews. How do I tell the Review form to assign his name only 10 times? I was told by someone to try a counter loop statement in VBA, but I am not familiar enough with it to make it work. I have tried and tried, but I either get it to populate 1 time or 100 times, nowhere in between!

Thanks for any help you can give!!

Anne Troy
06-07-2004, 12:07 PM
Hi, Mags.
The coders will need to either have you upload your database (make a copy, delete all but a few records, change the names to protect the innocent), or you'll likely need to provide a basic table structure and form names.

magsroq
06-07-2004, 03:36 PM
Hi,

Thanks. Attached is a stripped-down version of my database. I have taken precautions to delete any customer-sensitive information from the examples. (Banking and privacy regulations, you know.) I removed just about everything except what pertains to my question, so it may look and perform strangely. The form has text boxes in which the user would insert a percentage to be used in the calculation to determine how many reviews they should do. It's the "populate form" button (command422) that has me stumped. What I need is for the users' ("reviewers'") names to populate the CRR Reviewer box.

Please let me know if you have any questions.

Thanks!!

Zack Barresse
07-20-2004, 11:23 PM
bump :)

SJ McAbney
07-21-2004, 01:21 AM
It would be pointless to work with the database sample that has been given as it doesn't even meet the First Normal Form (1NF) of database normalisation. Ergo, anything attempted on it would be tricky and would continually require ongoin maintenance.

GP George
07-21-2004, 08:15 AM
The main problem with this database is that it is not normalized. You have one flat table with many repeating or redundant fields. To properly address your reporting requirements, you need to resolve this table into the appropriate tables with relationships between them. Please take a step back from where you are and spend some time learning about normalization. Any time you devote to that effort will be paid back many times over in the future as it will make problems like this one so much easier to handle.

You can download chapter three from my book here. www.gpcdata.com/downloads/NormalizationGPGonAccess.pdf (http://www.gpcdata.com/downloads/NormalizationGPGonAccess.pdf) This chapter discusses the basics of normalization. Once you have a basic understanding of the first three rules of normalization, you'll be ready to resume work on this database.

HTH

George