Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query many fields (Acess 2000/SR-1)

    I have a table with 35 fields containing a "Due" date for a list of activities. This same table has 35 corresponding "Completed" fields which contain the date the action was actually completed. These fields are NULL if there has been no value entered.

    I want to create a query (preferred) or build some code that will alert the user if any of the due dates have come and gone without a completed date being entered in its corresponding field. The alert is engaged based on the computer's system setting.

    I have built a query using the following criteria in each of the "Due" fields, but this does not address the fact that a "Completed" date has not been entered:

    <=Date() And Is Not Null

    This may not be the best way to do this, and I would appreciate any constructive suggestions you may have. I've been out of programming for years and my skills are VERY tarnished.

    Thanks for your help.

    Will

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query many fields (Acess 2000/SR-1)

    You will have a serious problem with that table design. What you need is a normalized table that has a field for the activity, a field for the due date and a field for the completed date, plus whatever other fields are needed to identify that single unique record (like, whatever the activity relates to or is assigned to or requested by, etc.). Then it would be relatively simple to query the table for all records with a due date but no completed date and filter by whatever other criteria you wish. In that case, all you would need would be <=Date under the Due date field and Is Null under the completed date field. You wouldn't need to repeat it 35 times for each.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query many fields (Acess 2000/SR-1)

    Here's my problem in more detail:

    Each client has a record with contact information (client table). Each client can have multiple transactions (transaction table). A single transaction requires that a checklist be performed. This checklist has 35 distinct actions that must be monitored throughout the course of the transaction. Example:

    A contact file is opened on Bob (ID=1). We begin a transaction for him so link the transaction with Bob's ID number, 1. We set the due dates of 3 time events: Initial deposit due=1/1/03, Other cash funds due=3/5/03, Balance due=4/8/03. As time progresses, we check off anything that was completed: Initial deposit received=1/1/03, Other cash funds received=2/17/03, Balance received=Null. In any case, each event has a specific purpose, due date, completed date, and a memo field containing info concerning the event. There are 35 such events in the checklist.

    My question now is how could this be normalized? To me, this requires a one-to-one relationship but I am unsure how to accomplish this without placing each event in the transaction table.

    Can you provide any further assistance?

    Thanks,
    Will

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query many fields (Acess 2000/SR-1)

    As Charlotte suggested, create an Event Log table that is a one-to-many with the Client table. Also create an Events table that lists all of the 35 events, and use a lookup function to determine what event you are dealing with in the Event Log table. Each entry in the Event Log table would probably have four (or more) fields - an autonumber field for updatability purposes, an EventID number pointing to the Events table, a Due Date, and an Actual Date. Other fields might be a DeletedFlag, an EventNotes, a Who and When Entered, and a Who and When Updated. We use this structure in several systems where there is a well-defined process that must be followed. Hope this clarifies things a bit.
    Wendell

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query many fields (Acess 2000/SR-1)

    Thank you, both. This helps a lot. I'll work on it. I appreciate your patience and assitance.

    Will

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •