DanijelP
asked on
Flagging if a record doesn't exist
I have client records and attached to them I have 0 to many achievement records.
I want to flag any client records where there is no achievement record attached that meets my criteria or no achievement record at all.
Criteria
" category ID = 1
" date > 01/05/09 and < 31/05/09
I thought I would simply group my report by client and put an if statement field in the group header. The If statement would then give me a True if it found an achievement record that met the criteria and False if NO record was found that met the criteria. False being the flag. I have since discovered that a formula will only consider the first record if it is placed in the group header(and the last record if in the footer). If I place my "if statement" field in the details section it does work but it then creates a line for every achievement record belonging to that client. This is not what I'm after.
What I really want is a list of clients with a simple true or false to flag if that client has an achievment record that meets the criteria or not.
I want to flag any client records where there is no achievement record attached that meets my criteria or no achievement record at all.
Criteria
" category ID = 1
" date > 01/05/09 and < 31/05/09
I thought I would simply group my report by client and put an if statement field in the group header. The If statement would then give me a True if it found an achievement record that met the criteria and False if NO record was found that met the criteria. False being the flag. I have since discovered that a formula will only consider the first record if it is placed in the group header(and the last record if in the footer). If I place my "if statement" field in the details section it does work but it then creates a line for every achievement record belonging to that client. This is not what I'm after.
What I really want is a list of clients with a simple true or false to flag if that client has an achievment record that meets the criteria or not.
if {vwAchievements.DateSubmitted} in DateTime (2009, 05, 01, 00, 00, 00) to DateTime (2010, 05, 1, 00, 00, 00) and {vwAchievements.PaymentTypeID} = 1
then "1"
else "0"ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
but..
How can I ensure Client records with no Achievement records attached are not eliminated
I have used a left outer join to Achievements.
Do I have to add something to my original "if"?