Programming a result field in a table

--------------------------------------------------------------------------------

I would like to set a flag in a record if the record key exists in another
table. Can anyone tell me how I can do this?

Specifically, I have a volunteer master table and a task table. If the
volunteer number exists in the task table, I would like to set a field in the
volunteer table to "yes" so that I can run a quick report or extract of all
volunteers who have been assigned tasks. I only want the volunteer to show up
once on the report, regardless of how many tasks they are assigned. I tried a
query joining the volunteer table with the task table, but could not suppress
duplicates using the option in query design.

Thanks in advance for any help on this.

You don't need to setup a field.

You query could be:

select id,FirstName, LastName from tblVolunteers
where (select count(id) from tblTasks where volunteer_id =
tblVolunteers.id) 0

in the above I assume each table has a primary key of "id" (which is the
default).. I also assume that the field in tblTasks is volunteer_ID.

Note that for your report if you need to display their tasks, base the
report on the above query, but to display tasks, place in a sub-report which
will display their tasks.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Thank you, Albert, this is helpful. I would still like to set a flag in the
volunteer table if the volunteer has assigned tasks. I want to see the flag
on the volunteer form. Is there a way to do that? Thanks - Linda

"Albert D. Kallal" wrote:

You don't need to setup a field.

You query could be:

select id,FirstName, LastName from tblVolunteers
where (select count(id) from tblTasks where volunteer_id =
tblVolunteers.id) 0

in the above I assume each table has a primary key of "id" (which is the
default).. I also assume that the field in tblTasks is volunteer_ID.

Note that for your report if you need to display their tasks, base the
report on the above query, but to display tasks, place in a sub-report which
will display their tasks.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Linda1485" wrote in message
...
Thank you, Albert, this is helpful. I would still like to set a flag in
the
volunteer table if the volunteer has assigned tasks. I want to see the
flag
on the volunteer form. Is there a way to do that? Thanks - Linda


Sure, just place a check box on the form.

For the data source of the check box place:

=(DCount("*","tblTasks","volunteer_id = = " & [id])0)

The above will thus always automatic show a check box.

in fact, if you don't want a check box, place a text box on the form and
use:

=(DCount("*","tblTasks","volunteer_id = = " & [id]))

The above will display the number of active tasks. (you could use
conditional formatting to make the box red, or green to improve visual
feedback if you want).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert, thank you very much. This is exactly what I needed.

On your earlier post about the Select statement, I recognize it as SQL, but
am not sure how to create an Access query, specifically on how to join the
two tables, volunteers and tasks, in the Access query designer. Could you
point me in the right direction.

Thanks again,

Linda

"Albert D. Kallal" wrote:

"Linda1485" wrote in message
...
Thank you, Albert, this is helpful. I would still like to set a flag in
the
volunteer table if the volunteer has assigned tasks. I want to see the
flag
on the volunteer form. Is there a way to do that? Thanks - Linda


Sure, just place a check box on the form.

For the data source of the check box place:

=(DCount("*","tblTasks","volunteer_id = = " & [id])0)

The above will thus always automatic show a check box.

in fact, if you don't want a check box, place a text box on the form and
use:

=(DCount("*","tblTasks","volunteer_id = = " & [id]))

The above will display the number of active tasks. (you could use
conditional formatting to make the box red, or green to improve visual
feedback if you want).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Linda1485" wrote in message
...
Albert, thank you very much. This is exactly what I needed.

On your earlier post about the Select statement, I recognize it as SQL,
but
am not sure how to create an Access query, specifically on how to join the
two tables, volunteers and tasks, in the Access query designer. Could you
point me in the right direction.


Actually, I thought you said you did this already, and the problem was that
you get multiple rows for one volunteers.

As I mentioned, if you building a report, you don't need to join the two
tables anyway.. You build the report based on volunteers, and use that
sample query to ONLY display those volunteers with tasks. If you need the
task information to be included in the report, then you build a sub-report
based on tasks. That why I saying you don't need to "join" in the task data
with a query...you use sub-reports to accomplish this task. (just like you
perhaps used a sub-form to allow the child data to be edited...you did not
have to build a query with both tables joined for displaying and editing of
the data in a form...you use forms + sub-forms). For reports, same idea, but
you now use reports + sub-reports.

However, if you do for reasons need to join the two tables, then simply drop
in volunteers, and drop in tasks...and then drop a join line from volunteers
to tasks. You might have to give additional information as to what you need
this query, or what it going to be used for. As mentioned, if this query is
going to be used for a report, then you really don't need to join both
tables as the tasks can be displayed by use of a sub-report placed in the
detail section of the report along with the fields from volunteers that you
want displayed for each record.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada