Lookup forms

I am programming a MS access database to qualify for a program.  I am having great difficulty and just started VBA classes.  But still need a lot of help!

One table has qualifying data and other table will receive data entry with family income.  Want to enter data in field [HHsize] on frmIEChild and enter data in fields [HIncome] & [Frequency] on subform frmHHI.  Once data is entered want a function or subroutine to do the following:
 If family has multiple forms of income and same frequencies, to total income.
 If family has multiple forms of income and different frequencies, to annualize income (ie:weekly =income x 52, Bi-weekly=income x 26, 2x month = income x 24, monthly =income x 12) and total.
The "Qualify" button will "Lookup" or compare in frmIEGDate & frmIEG with the [HHsize] and Total income by frequency to determine if income is within limits.  IF it is, then qualify as "F' or "R".  If income is over limits for [HHsize], then qualify as "N"

Can you please assist me with these forms ?Application.zip
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Took a peek at your database.

1) The table IEGDate probably needs to be better normalized. It will make what you want to do a lot easier.

2) Where are your queries?  A lot of what yoiu want to do probably can be done with queries. How easier will depend on the how well the table design follows the rules of data
Jeffrey CoachmanMIS LiasonCommented:
I'm with THTC,

Also you do not have any relationships established between the tables, so it is hard to get a handle on what the tables mean with regard to one another...

As is typically seen with new Access developers, you need to first make absolutely sure that the table design, keys and relationships are rock solid *before* you ever worry about forms, functions or subroutines, or even VBA.


Don't be put off by those highly skilled experts.

They simply don't like having a separate cup for each drink. It will be hard to clean (maintain) them. They like re-usability. Easy to maintain.

If you are starting, I suggest asking many questions, each dealing with one issue,  starting from table design. Many people did, and most of them succeeded.

Good luck!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

softsupportAuthor Commented:

Not sure what normalize the IEGDate table means.  Can you give me some pointers on your comment?
Here is the classic tutorial on normalizing data

MS may want to pretend that everyone can build an Access database with no code! and no prior skills! -- but normalization is something that every good database has to have done correctly
Hi SoftSupport

First, you appear to be storing large amounts of data that can be easily calculated.  For example, if the annual income eligibility is $14,937 then it is easy to calculate the monthly, bimonthly, fortnightly and weekly values by dividing by 12, 24, 26 and 52 respectively.  I suggest you store only the annual values.

Secondly, you should not store HHIncome and HHFrequency in the IEChild table.  These belong only in the HHI table.

Third, you should set up relationships between your tables to maintain the integrity of your data and also to help Access to help you by automatically joining related fields in queries and subforms.

I suggest you create a Frequency table with three fields:
frqCode - single character, primary key
frqName - short text
frqAnnualPeriods - numeric (double)

Populate this with the codes and names for the income frequencies, and the number of periods in a year - for example:
A, Annual, 1
M, Monthly, 12
B, Bi-Monthly, 24
F, Fortnightly, 26
W, Weekly, 52

Now you can create a query based on HHI and Frequency to translate any period income into annual income:
SELECT HHI.*, Frequency.frqAnnualperiods*HIncome as AnnualIncome
FROM HHI inner join Frequency on HHI.Frequency=Frequency.frqCode;

Base your HHI subform on this query and include a textbox named txtAnnualTotal in the form footer:

You can then easily look up the annual eligibility value from IEG corresponding to HHSize, and compare this with the actual total annual income.

Best wishes,
Graham Mandeno [Access MVP since 1996]
Jeffrey CoachmanMIS LiasonCommented:

As you can see from all of our replies, ...you design is not optimal for what you are trying to do.
If you are new to database design, then your first question should always be:
"Have I designed and related the tables properly?"
...*Before* ever worrying about Forms, Look-ups, calculations or VBA.

So I will step aside now to avoid confusion, ...you have several top experts who, I am sure, can help you with this issue...

softsupportAuthor Commented:
Thank you so much for your input and suggestions, however I cannot only store annual values.  These incomes are mandated each year and cannot be changed.  I must use the table in its entirety.  The Monthly, Twice A Month, Biweekly and Weekly are fixed incomes that DO NOT calculate into the Annual Income exactly.  If you take the Monthly incomes of  $1,245 and multiply by 12, equals $14,940, which is over the annual income of $14,937, thereby disqualifying this individual.  If the income all income supplied is Monthly, I must calculate and qualify by that frequency, otherwise individual would be disqualified and not be determined properly.  The only way to annualize the income is if the income frequency is mixed (ie: Monthly & weekly) Then I must annualize.

I thought the same thing, however, when inquiring with my superiors, we must use the table submitted.

Secondly, I really do not want to store the HHIncome and HHFrequency in the IEChild Table... I want to display the results of the qualifying totals....(totalling of size, income and frequency) on the IEChild table for future reference.

Can I still use your suggestion with the frequency table and include the entire income table?
Hi SoftSupport

I agree with you that it makes no sense that you must annualize if there is mixed-period income, but you may not annualize if all income is for the same period.  However, it seems we must bow to the wisdom of your superiors ;-)

I suggest you stick with the idea of the Frequency table.  I see you are using "T" for "Two-Weekly" rather than "F" for "Fortnightly", so change that in your table.

Next, add the following code to your HHI subform:
Option Compare Database
Option Explicit

Private Sub Form_AfterDelConfirm(Status As Integer)
End Sub

Private Sub Form_AfterUpdate()
End Sub

Private Sub CalculateHHITotal()
Dim rs As DAO.Recordset
Dim curIncome As Currency
Dim strFrequency As String
Dim fMixed As Boolean
  Set rs = Me.RecordsetClone
  If rs.RecordCount = 0 Then
    ' no records - set income to zero
    strFrequency = "A"
    curIncome = 0
    ' get values from first record
    strFrequency = rs!Frequency
    curIncome = rs!HIncome
    ' loop through any other records
    Do Until rs.EOF
      If rs!Frequency = strFrequency Then
        ' same frequency - simply add income
        curIncome = curIncome + rs!HIncome
        If strFrequency <> "A" Then
          ' annualize what we have so far
          curIncome = AnnualizeIncome(curIncome, strFrequency)
          strFrequency = "A"
        End If
        ' add the annualized income for this record
        curIncome = curIncome + AnnualizeIncome(rs!HIncome, rs!Frequency)
      End If
  End If
  ' set the values on the parent form
  Me.Parent!HHIncome = curIncome
  Me.Parent!HHFrequency = strFrequency
  Set rs = Nothing
End Sub

Private Function AnnualizeIncome(curIncome As Currency, strFrequency As String) As Currency
  AnnualizeIncome = curIncome * DLookup("frqAnnualPeriods", "Frequency", "frqCode='" & strFrequency & "'")
  ' need to handle case where invalid frequency is given
End Function

Open in new window

Whenever you add or modify or delete a record in the subform, the total income and frequency will be updated in the main form (and annualized if necessary).

Next, to make the eligibility lookup easier, redesign your IEG table so that it has only one income value per record, instead of looking like a spreadsheet.  It needs to have these fields:
Effective (same as you have now)
HHSize (same as you have now)
Frequency (a frequency code value - "A", "M", "W", etc)
IncomeType (either "F" or "RP" - it seems you have two tables rolled into one???)
IncomeThreshold (the income value)

Some sample records from your existing data might look like this:
1, 1, "A", "F", $14,937
1, 1, "M", "F", $1,245
1, 1, "A", "RP", $21,257
1, 2, "W", "F", $388

Make the first four fields a composite primary key, to ensure there can be no duplication of the same combination.

Now, all you need to do to ascertain an eligibility threshold is a simple lookup, given the four key values.

Best wishes,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
softsupportAuthor Commented:
Thank you Graham....  Appreciate your assistance and explanation!!!

Your comment... "Make the first four fields a composite primary key".... would be "Effective", "HHSize", "Frequency" and "IncomeType"....  correct?
Yes, that is correct.  You want to ensure that you cannot have two records with the same combination of those values, and making them a primary (and therefore unique) key is the easiest way of doing that.
softsupportAuthor Commented:

Thank you, all is working according you code above.  

One last question regarding your statment "Now, all you need to do to ascertain an eligibility threshold is a simple lookup, given the four key values."  

Can the lookup display on the IEChild an "F" or "RP" if eligibility falls within the four key values of the IEG Table?  And display an "N" if outside both?
softsupportAuthor Commented:
How can I ask additional questions after accepting this solution?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
How can I ask additional questions after accepting this solution?
Create a new question.

You can link the new question to this one using the section:

Select Topics

Please choose topics relevant to your question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.