Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    New York, New York, USA
    Thanked 0 Times in 0 Posts

    Stumped on Table Structure (Access 97)

    I am asking for suggestions on resolving a problem that I can't quite figure out. What I need to achieve is this: a report needs to be sent to many people who don't appear to have anything in common. These people need to have their names end up in the TO: field of a report and sent to them via email/fax which needs to be determined by code (yet to be written) that will look at the email field first and output to that value or if there is no email, look at the fax number field and output to that value (Outlook97).

    Now, here's the background on the db:
    The entities that I am tracking are legislators (tbllegislators). The people that need to recieve info about the legislators are doctors (tbldoctors). The data the docs are receiving pertains to fundraisers that the legislators hold (tblcurrentevents). Which doctors need to be sent (rptapproval) is too complex for me to incorporate into the db because its based on multiple criteria that is unrelated. Based on the dollar amount of the fundraiser, there are several business principles that come into play. Basically I am asking for these people, based upon their titles in our organization or based upon their membership in various committees to approve a fundraiser.

    For example, every event needs to be sent to the EVP always. If the contribution amount is $500 or less, the EVP is the only person who gets the report.

    If it's over $501, the following people need to see the report:
    the EVP,
    the Chair of our EXEC Committee,
    the chair of the Committee that corresponds to whether the legislator is state or federal (SC/FC these are seperate committees),
    other members of the SC or FC that share a district number except members that are considered alternates of their committees.

    ( The district number is already established in another table and a junction table has been made so each legislator and doctor are joined by the district number.)

    If the $ amount is greater than 1001:
    the EVP,
    entire EXEC Committee including the chair,
    the FC/SC Chair
    the FC/SC members who share districts with the legislator have to see the report.

    If the amount is $10,000 or over, members of another committee (MEXEC) need to see the contribution, in addition to everyone in the $1001 category.

    Also, duplicates must be removed as some members serve on more than one of these committees.

    Lastly, there is one other category of fundraisers that are held by committees rather than individual legislators, but they are entities in their own right. These committees have the same rules, except that we can exclude the SC/FC members as they don't apply here.

    I need to know what fields to incorporate in the table set up, so that If I give a dollar amount and name of the legislator, (This will come from the current event table) a query will return the names etc. of all the people who need to be contacted.

    I have tried so many variations but have not been able to achieve a solution that works on all levels.

    Hey if no one wants to touch this, I completely understand. Thanks in advance. Jenn. <img src=/S/puke.gif border=0 alt=puke width=60 height=15> Sorry about that!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 93 Times in 89 Posts

    Re: Stumped on Table Structure (Access 97)

    Frankly, it sounds like too much to do in 1 query. What I probably would do is create a little table that contains the information about the current event. Then write numerous queries relating to your specific criteria to "tag" each doctor record (simple Yes/No) for whether to invite, then assemble a macro to automate the production of a single list. Example:

    Amount: 750
    Legislator: XYZ
    Type: Individual or Committee

    Query 1: update all SendEmail fields to No
    if Type = individual Query 2: update (tag) all committee members in the district: if > 1000, yes, else if > 500 and not alternate, yes, else no
    Query 3: update Exec committee members: if > 1000, yes, else no
    Query 4: update Fc/Sc chair: If > 500 Yes, else No
    Query 5: update the EXEC Chair: If > 500 Yes, else No
    Query 6: update the EVP (yes)
    Query 7: SELECT DISTINCT ... WHERE SendEmail = "Yes"

    (Note: these are ordered to avoid overwriting a Yes with a No, but you also could do that in your expressions.)

    This method makes duplicates impossible. It allows you to adapt to changing circumstances with little bite-sized queries rather than one immensely complex monster. The one-record info-table approach lends itself to a form.

    Hope this helps.

Posting Permissions

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