Results 1 to 6 of 6
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    Update a field based on a value of another field

    Hi,


    I have a table with the following fields: ID, Computer, FamilyName, FirstName
    I added a new field named AuthorRank to the table and I want to update the values of this field as follows:
    If FamilyName or FirstName is not null then AuthorRank="P"

    What should I write in the criteria to do this?

    Khalil

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Why bother even having this field in table? That value can be calculated when needed in query or textbox. It is usually a bad design to store values dependent on other data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    I am migrating an old flat database to a new relational database.
    I will have a tblAuthors that has all the authors, another table tblBooks that has the other information.
    I am having a third table tblBooksAuthors - a junction table - that has a field AuthorRank. This field should indicate if the name is for a main author OR a Coauthor.
    I already have to different tables one for all main authors and a second table for all co authors. In each of these table I have a field which is actually the primary key of tblBooks.
    This AuthorRank field is a new field in the new database and does not exist in the old design.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I already have to different tables one for all main authors and a second table for all co authors.
    This doesn't sound correct. Should have 1 table with a field to indicate main author/co author.


    In each of these table I have a field which is actually the primary key of tblBooks.
    I would expect to have the Author PK field as a FK in junction table "tblBooksAuthors".

  5. #5
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    I added the AuthorRank field values in each table and then use a Union query and I obtained what I want.
    Yes, the Author pk field is the fk in the junction table.

    Thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    You say you have tblAuthors then you say you have two tables for authors. I am confused. Why these two tables and why do they have key for tblBooks? tblBooksAuthors is the junction table to associate authors and books.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-20-2016, 08:55 AM
  2. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  3. Replies: 14
    Last Post: 06-22-2016, 04:08 PM
  4. Replies: 0
    Last Post: 06-17-2016, 08:56 AM
  5. Replies: 3
    Last Post: 01-09-2015, 05:48 PM

Tags for this Thread

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