Populate Results From Several Selections (1 Viewer)

[Chespirito]

Hello, I need some help with the following situation. I need to display the results from a number of selections to a text box if some selections are made.
I have included a demo DB as a sample. I have about 7 lines with 4 options each. Example: Heat = Natural Gas, Electric, Oil, Propane and a drop down box = Tenant/Landlord. so if I select a check box electric and paid by Landlord. I would like to display: Landlord Pay for Electric Heat and so on and so forth.
I am using a function = =IIf([NaturalGas]=True And [Paidby]="T","Tenant pays for propane heat") but it only works for the first line and when i try to nest more functions it does not work. I am not sure if there is a better way to do it. Thank you for your help in advance.

 

[arnelgp]

try your demo db.

 

[plog]

You are not storing your data correctly. You have used data values as field names. Instead those field names need to go into a new table as data values. Let's call this new table tblUtilites. It's structure will be this:

tblUtilities
utl_ID, autonumber, primary key
ID_Tenant, number, foreign key to Tenants table
utl_Group, short text, this will determine if its for Cooking or Heating
utl_Type, short text, this will be the value which is currently a field name (eg. Propae, Oil, etc.)
utl_Payor, short text, this will dtermine who pays for it

That's it, those 4 fields will hold all your existing utility data and if you ever need more will do that too without adding new fields.

Then, when you want to create a string that displays what utilities the tenant and the landlord pay you would use the ConcatRelated function:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[isladogs]

For info, I moved this thread from sample databases which is a moderated area not intended for questions and approved all replies that had been made

 

[Chespirito]

try your demo db.

try your demo db.

Arnelgp, that is exactly what I was looking for, but i am new to access. can you give me some instructions how to implement this to me DB ? since I uploaded a sample DB. Thank you very much.

 

[Chespirito]

You are not storing your data correctly. You have used data values as field names. Instead those field names need to go into a new table as data values. Let's call this new table tblUtilites. It's structure will be this:

tblUtilities
utl_ID, autonumber, primary key
ID_Tenant, number, foreign key to Tenants table
utl_Group, short text, this will determine if its for Cooking or Heating
utl_Type, short text, this will be the value which is currently a field name (eg. Propae, Oil, etc.)
utl_Payor, short text, this will dtermine who pays for it

That's it, those 4 fields will hold all your existing utility data and if you ever need more will do that too without adding new fields.

Then, when you want to create a string that displays what utilities the tenant and the landlord pay you would use the ConcatRelated function:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

Thank you for your well observed suggestion. I will follow your advise.

 

[Chespirito]

try your demo db.

arnelgp please help, how do I put the result for label38 on a report. I would like to display the result on a report. Please help. Thank you.