Query for Report, display only if condition is met, multiple conditions

[laavista]

I have a reminder table, tbl_reminders
fields:
Empl_ID, number (long)
Rem_1_on, text ( 1 char)
Date1, date (short)
Rem_2_on, text ( 1 char)
Date2, date (short)
Rem_3_on, text ( 1 char)
Date3, date (short)

Table's data:
Empl_ID
Rem_1_on
Date1
Rem_2_on
Date2


Rem_3_on
Date3
123
Y
3/4/10
N
5/6/10
Y
6/7/10
456
N
12/12/10
Y
6/1/10
Y
1/12/09


I need a report that would display, for each empl_ID, ONLY the dates that have rem_#_on= 'Y'

Example report
Empl_ID
Rem_1_on
Date1
Rem_2_on
Date2
Rem_3_on
Date3
123
Y
3/4/10


Y
6/7/10
456


Y
6/1/10
Y
1/12/09

I've tried various query types, but nothing is working. Your help would be greatly appreciated!

[is49460]

not sure why your table is set up the way it is. Seems like it would be more efficient to have a table that looks like this: EmployeeID, Reminder, Date, ReminderType. Where reminder type determines various reminder categories. so you would have 3 records for each one you have now. Anyway, with the setup you have described you can include an entire table as your data source and use condition statement on each textbox to check if corresponding reminder exists and if so display it, otherwise display blank. I hope that helps.

[laavista]

I inherited the database, and unfortunately cannot change the structure of this particular table.

I'd like to do the conditions, but can't seem to get it to work.

Any suggestions?

[is49460]

For each reminder textbox (I assume you should have 3 of them) change control source property to something like this:

=iif([Rem_1_on]="Y", [Date1], "")

Make sure Date1 is the name of the field in the data source of the report, not the name of a textbox.
It should give you the date only if your Rem_1_on field is set to Y, otherwise it will leave textbox blank. Let me know if you need more details. good luck!

[laavista]

You are great! This worked like a charm. I was trying to do this with a query, and it was just not working. This is EXACTLY what I needed.

Thank you so much for taking the time to help me with this!!!!

[is49460]

glad to help!