Results 1 to 10 of 10
  1. #1
    Linda is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2009
    Posts
    10

    Question 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.

    Linda

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I would think a Left Join would give you the recordset you want.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Linda is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2009
    Posts
    10
    Thank you, RG. But, even with the left join, how do I automatically set the flag in the volunteer record if there exists a task record using the query? I want the flag to always be updated. Is there some way to put this check into a field in the volunteer record?

    thanks, Linda

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If you base your report on the Left Join query, doesn't that eliminate the duplicates?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    Linda is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2009
    Posts
    10
    In addition to the report, I still want to update the flag in the volunteer record; Yes if the volunteer has assigned tasks and No if they do not. How do I do that?

    thanks, Linda

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I'm not trying to be stubborn, just thorough. If the Left Join query gives you what you want in the report then why do you feel you need the extra field in the Master table?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    Linda is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2009
    Posts
    10
    You make a good point - a report could be all I need, but if I have the flag on the volunteer master, then it could be used for filtering on the form. I have a button on the volunteer form that produces a standard report on filtered records, which would be whatever the user wants.

    Also, I would still like to know how to set the flag. I think I used to do something like this in File Maker Pro, but I can't remember how I did it.

    Thanks, Linda

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If you have the field in the Master table it then becomes necessary to keep it accurate when you add or delete tasks from the Task table. Still, if you insist, the Left Join query can be used to update the Master table field. If IsNull([TaskField]) then no tasks.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  9. #9
    Linda is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2009
    Posts
    10
    Ok, thank you for your response. I will give what you say some more thought.

    Linda

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You're welcome Linda. Post back in the forum if you need any additional assistance.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-12-2009, 05:09 PM
  2. Programming Adjustable Pricing Fees
    By JDA2005 in forum Programming
    Replies: 2
    Last Post: 07-07-2009, 10:50 AM
  3. New to Access programming
    By pushpm in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 03:03 PM
  4. Access 2000 programming startup options
    By nosaj_ccfc in forum Programming
    Replies: 2
    Last Post: 10-17-2008, 02:18 PM
  5. Programming Language like Access
    By cwf in forum Programming
    Replies: 2
    Last Post: 05-17-2008, 03:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums