Access Calculated Field

Question

I am having difficulty trying to make a calculated field that I need. So here is what I am trying to do: I have a query that combines the information based on three tables. The most important fields that for the application are as follows:

Family Income   Age    Patient
    15,000      18       Yes 
    28,000      25       No
    30,000      1        Yes

From here I want to make a calculated field that gives the correct program the patient was enrolled in. based on these fields ie:

Program      Minimum Income   Maximum Income   Minimum Age   Maximum Age Patient
Children's       0              20,000            1             19        Yes
Adult            0              12,000            19            65        No
Non Patient      0              20,000            1             19        No
Adult 2       12,000            50,000            19            65        No
Etc.

to create:

Family Income   Age    Patient    Program
15,000          18       Yes      Children's
28,000          25       No       Adult 2
30,000          1        Yes      Children's 2

I know I can use IIf to hard code it in to the field, but then it will be really difficult for other people to update the information as the guidelines change. Is it possible to have the information stored in a table? and use the information on the table form etc, or will I need to use IIf

Any Ideas? is it possible to dynamically create the IIf in SQL using VBA while pulling the information from the table?

EDIT::: Thank you for your response and for formatting my tables, I still have no idea how you changed it, but it looks amazing!

I tried to add the SQL you added down below, but I was not able to make it work. I'm not sure if I made a mistake so I included the SQL of my Query. The query currently returns 0 values, so I think I messed something up. (The real Query is embarassing...I'm sorry for that). Unfortunately, I have done everything in my power to avoid SQL, and now I am paying the price.

SELECT qry_CombinedIndividual.qry_PrimaryApplicant.[Application Date],
      qry_CombinedIndividual.qry_PrimaryApplicant.[Eligibility Rep],
      qry_CombinedIndividual.qry_PrimaryApplicant.Name,
      qry_CombinedIndividual.qry_PrimaryApplicant.Clinic,
      qry_CombinedIndividual.qry_PrimaryApplicant.Outreach,
      qry_CombinedIndividual.qry_PrimaryApplicant.[Content Type ID],
      qry_CombinedIndividual.qry_PrimaryApplicant.[Application Status],
      qry_CombinedIndividual.qry_PrimaryApplicant.Renewal,
      qry_CombinedIndividual.qry_Enrolled.EthnicityEnr,
      qry_CombinedIndividual.qry_Enrolled.GenderEnr, qry_CombinedIndividual.AgeAtApp,
      qry_CombinedIndividual.[Percent FPL], tbl_ChildrensMedical.MinPercentFPL, 
      tbl_ChildrensMedical.MaxPercentFPL, tbl_ChildrensMedical.MinAge,
      tbl_ChildrensMedical.MaxAge, tbl_ChildrensMedical.Program
 FROM qry_CombinedIndividual 
      INNER JOIN tbl_ChildrensMedical ON qry_CombinedIndividual.qry_Enrolled.Patient = tbl_ChildrensMedical.Patient
    WHERE (((qry_CombinedIndividual.AgeAtApp)>=[tbl_ChildrensMedical].[MinAge] 
    And (qry_CombinedIndividual.AgeAtApp)<[tbl_ChildrensMedical].[MinAge]) 
    AND ((qry_CombinedIndividual.[Percent FPL])>=[tbl_ChildrensMedical].[MinPercentFPL] 
    And (qry_CombinedIndividual.[Percent FPL])<[tbl_ChildrensMedical].[MaxPercentFPL]));

Also there are many different programs. Here is the real Children's Table (eventually I would like to add adults if possible)
*Note the actual table uses FPL (which takes family size into account, but is used the same as income). I am again at a total loss as to how you formated the table.

Program             Patient   MinPercentFPL MaxPercentFPL   MinAge  MaxAge  
SCHIP (No Premium)    No           0             210          1       19  
SCHIP (Tier 1)        No         210             260          1       19  
SCHIP (Tier 2)        No         260             312          1       19  
Newborn               No           0             300          0       1  
Newborn (Patient)    Yes           0             300          0       1  
Children's Medical   Yes           0             200          1       19  
CHIP (20 Premium)    Yes         200             250          1       19  
CHIP (30 Premium)    Yes         250             300          1       19

Do I have the correct implementation for the table I have? Or should I be changing something. I can also send more information/sample data if that would help.

Thank you again!

Do you already have the 2nd sample of data as a table? If so, you can set your query to match kinda like '((Age >= Min Age) AND (<= Max Age)) AND (Patient = Patientx) and ((Income >= Min Inc) and Income <= Max Inc)) — Wayne G. Dunn

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2014-12-23T19:58:28

I just created some tables with your sample data and used the following SQL. Your 3rd 'patient' doesn't match any of the ranges (Age 1, Income $30K)

SELECT tblPatient.PatName, tblPatient.FamInc, tblPatient.Age, tblPatient.Patient,
 tblPatientRange.Program, tblPatientRange.MinInc, tblPatientRange.MaxInc, tblPatientRange.MinAge,
 tblPatientRange.MaxAge, tblPatientRange.Patient
FROM tblPatient INNER JOIN tblPatientRange ON tblPatient.Patient = tblPatientRange.Patient
WHERE (((tblPatient.FamInc)>=[tblPatientRange]![MinInc] And (tblPatient.FamInc)<=[tblPatientRange]![MaxInc]) 
AND ((tblPatient.Age)>=[tblPatientRange]![MinAge] And (tblPatient.Age)<=[tblPatientRange]![MaxAge]));

Access Calculated Field

1 Answers