New column in query

J

JOM

I have the following columns in my query empid, courseA, datecompA, courseB,
datecompB, courseC, datecompC. What I need is an extra column that will have
an indicator Yes if any of the 2 dates(i.e., datecompA, datecompB, datecompC)
have dates, blank if all 3 dates have dates and No if only of the dates has
a date.
 
J

Jeff Boyce

Based on your description of the fields you have in your query, it sounds
like you are working from ... a spreadsheet!

Is this a query based on a "linked table" (linked to an Excel spreadsheet)?
If not, and if this is an Access table, your database would benefit from
further normalization.

One way you could do this in a query, add a couple fields/expressions, and
do calculation/evaluation in those newly-added field. You might use one
field like:

DateCount: Abs(IsDate([DateCompA]) + IsDate([DateCompB]) +
IsDate([DateCompC]))

This should evaluate to 3 if all three have dates, 2 if two do, 1 if one
does, and 0 if none do.

Then the next "calculated field" could have something like:

YourNewField: IIF([DateCount] = 3, "", IIF([DateCount])=2, "Yes", ...

Extend this for each condition you wish to evaluate.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

JOM

Thanks that worked perfect

Jeff Boyce said:
Based on your description of the fields you have in your query, it sounds
like you are working from ... a spreadsheet!

Is this a query based on a "linked table" (linked to an Excel spreadsheet)?
If not, and if this is an Access table, your database would benefit from
further normalization.

One way you could do this in a query, add a couple fields/expressions, and
do calculation/evaluation in those newly-added field. You might use one
field like:

DateCount: Abs(IsDate([DateCompA]) + IsDate([DateCompB]) +
IsDate([DateCompC]))

This should evaluate to 3 if all three have dates, 2 if two do, 1 if one
does, and 0 if none do.

Then the next "calculated field" could have something like:

YourNewField: IIF([DateCount] = 3, "", IIF([DateCount])=2, "Yes", ...

Extend this for each condition you wish to evaluate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

JOM said:
I have the following columns in my query empid, courseA, datecompA,
courseB,
datecompB, courseC, datecompC. What I need is an extra column that will
have
an indicator Yes if any of the 2 dates(i.e., datecompA, datecompB,
datecompC)
have dates, blank if all 3 dates have dates and No if only of the dates
has
a date.
Click to expand...
Click to expand...
 
Top