Results 1 to 7 of 7
  1. #1
    chessico is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    20

    Usikng update query to exclude specific groups

    I want to run this query which works but I have members in the S_Members_Table that are Life members and Honorary Members
    i.e the fields are HONORARY MEMBER; HONORARY VP and LIFE MEMBER.
    These members never become expired so I want to run the query but not include them so in effect they do not become marked as expired. How can I change this query to accommodate this?

    UPDATE S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID SET S_Members_Table.Expired = "UPDATE S_Members_Table SET Expired"=True
    WHERE (((S_Payments_Table.PaymentDate)<DateSerial(Year(D ate())-IIf(Month(Date())<7,1,0),7,1)));

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have separate fields for member type? These are Yes/No fields? This is not a normalized structure. Can a member be only one type? Should be a single field with optional values.

    With current structure:

    AND IIf([Honorary Member]=True Or [Honorary VP]=True Or [Life Member]=True, True, False) = False

    With normalized structure:

    AND MemberType Not In ("HONORARY MEMBER", "HONORARY VP", "LIFE MEMBER")
    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
    chessico is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    20
    Neither added to my query works. I get a query box asking for member type.

    I have S_ Members_Type joined to S_Members_Table joined to S_Payments_Table.
    When I first designed the DB, I was only told a member had one type. Each year they can change the category they re-join in but for the year they can only belong to one category and they pay the appropriate fee for that category. Now I know it wasn't an ideal design and knowing what I know now, I would do it differently but that's all the info I had at that time. It works fine. I set the expired box via my update query to expired(True) however I then have to manually untick 28 records for Life/Hon category records.
    Obviously the groups -Life/Hon Types hold that status for life or in the case of the 'Hon*' categories until they are nominated to become a Life member which supersedes the Hon type. So technically they don't expire they are always members year after year. These categories don't have any fee to pay because they have provided some outstanding service to the club.
    So:-
    S_Members_Type has fields MembTypeID(Autonumber): Type(Different types like Hon;Life;VP;Associate etc.); Fee(Different Fee for Each category); This is the '1' join and the S_Members_Table is the 'many' join.
    In the S_Members_Table Key field is : MemberID(Autonumber);MembTypeID(Number)Foreign Key. I need to add or change the SQL of the update query so that all members become 'expired after a year on a specific date EXCEPT the LIFE/HON* types. I tried using both pieces of code but they made no difference.

    My updated code became
    UPDATE S_Members_Table INNER JOIN S_Payments_Table ON [S_Members_Table].[MemberID]=[S_Payments_Table].[MemberID] SET S_Members_Table.Form = "UPDATE S_Members_Table SETForm"=False AND MemberType Not In ("HONORARY MEMBER", "HONORARY VP", "LIFE MEMBER")WHERE ((([S_Payments_Table].[PaymentDate])<DateSerial(Year(Date())-IIf(Month(Date())<7,1,0),7,1)));

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use your field names. If the field name is Type and not MemberType, change code. Why is there 2 UPDATE clauses? Why are you trying to SET a form? What is the name of field to UPDATE? What do you want to update it with?

    The phrase I posted is filter criteria and therefore goes in the WHERE clause.

    UPDATE S_Members_Table INNER JOIN S_Payments_Table ON [S_Members_Table].[MemberID]=[S_Payments_Table].[MemberID] SET [fieldname] = something

    WHERE ((([S_Payments_Table].[PaymentDate])<DateSerial(Year(Date())-IIf(Month(Date())<7,1,0),7,1))) AND [Type] Not In ("HONORARY MEMBER", "HONORARY VP", "LIFE MEMBER");
    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.

  5. #5
    chessico is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    20
    I don't know why there are two sets of SET. I did the query in the Design view using the wizard rather than making my own SQL.
    I have a tick box on my membership form. So when the user enters members details if a payment is made they become a current member until the following year and the user removes the tick in the box labelled 'Expired' on the form.
    I run a lot of queries based on this 'Expired' field. I can search for current members by filtering those not 'Expired' etc.
    My membership form is controlled by the members table - S_Members_Table. whichincludes a field 'Expired' which is as I said represented by a simple True/False tick box on the membership form.
    I have a sub form of payments on the membership form which is controlled and uses all fields in the S_Payments_Table.
    I use an update query once a year from an admin menu which runs the update query and then this resets the Expired field in the S_Members_Table to True which is then shown on the membership form for the user to see and as they enter a new updated payment for a new season they remove the tick on the 'Expire' box on the form.
    I will retry using your suggestion - thank you. Will reply tomorrow if solved.

  6. #6
    chessico is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    20
    Quickly tried the code:
    UPDATE S_Members_Table INNER JOIN S_Payments_Table ON [S_Members_Table].[MemberID]=[S_Payments_Table].[MemberID] SET [Expired] = False
    WHERE ((([S_Payments_Table].[PaymentDate])<DateSerial(Year(Date())-IIf(Month(Date())<7,1,0),7,1))) AND [Type] Not In ("HONORARY MEMBER", "HONORARY VP", "LIFE MEMBER");
    This again asks me for to enter a parameter value “Type” and
    if I don't enter a value just click ok it says no rows will be updated. I don't want to have to use a box and enter a parameter value.

  7. #7
    chessico is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    20
    Tried this code and it works
    UPDATE S_Members_Type INNER JOIN (S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID) ON S_Members_Type.MemberTypeID = S_Members_Table.MemberTypeID SET S_Members_Table.Expired = "UPDATE S_Members_Table SET Expired"=True
    WHERE (((S_Payments_Table.PaymentDate)<DateSerial(Year(D ate())-IIf(Month(Date())<7,1,0),7,1)) AND ((S_Members_Type.Type) Not Like "Hon*" And (S_Members_Type.Type) Not Like "Life*"));

    Thank you

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

Similar Threads

  1. Exclude TOP N records from query
    By gemadan96 in forum Queries
    Replies: 4
    Last Post: 06-15-2014, 10:11 AM
  2. Replies: 12
    Last Post: 02-25-2014, 08:32 AM
  3. Replies: 1
    Last Post: 09-06-2012, 01:40 PM
  4. Replies: 1
    Last Post: 05-23-2012, 10:05 AM
  5. exclude field in query
    By chrisy in forum Queries
    Replies: 2
    Last Post: 10-28-2011, 09:53 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