Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Unanswered: friday queries....mannnn is it time to leave?

    I am working with Access2000. I have a volunteers table and a relations table in which I track the different relationships of volunteers. I am trying to compile a mailing list and combine the same volunteers with the same address/household. Below the first query gets volunteers who DO NOT share addresses. The second gets volunteers who do share addresses (I have a relations form that uses a checkbox to identify shared households).

    The third query is supposed to combine the two queries (UNION) but it seems that I'm getting duplicates still. For instance, Bob and Ginger Streeman show up and so does Ginger Streeman alone. This is what I'm trying to eliminate.

    I appreciate any thoughts or solutions!

    TIA


    Volunteers who DO NOT share Addresses
    Code:
    SELECT Volunteers.VolunteerID
    , Volunteers.FirstName
    , Volunteers.LastName
    , Volunteers.Address
    , Volunteers.Address2
    , Volunteers.City
    , Volunteers.State
    , Volunteers.Prefix & " " & Volunteers.FirstName & " " & Volunteers.LastName AS PreferredName, 
    Volunteers.PostalCode
    , VolunteerType.VolunteerTypeID
    , Volunteers.StatusNo
    , DeliveryType.DeliveryTypeID
    FROM VolunteerType 
    INNER JOIN (DeliveryType 
    INNER JOIN (Volunteers 
    LEFT JOIN qryAssembleallwithshared 
    ON Volunteers.VolunteerID = qryAssembleallwithshared.VolunteerID)
    ON DeliveryType.DeliveryTypeID = Volunteers.DeliveryTypeID) 
    ON VolunteerType.VolunteerTypeID = Volunteers.VolunteerTypeID
    WHERE (((VolunteerType.VolunteerTypeID)=1 
    Or (VolunteerType.VolunteerTypeID)=2) 
    AND ((Volunteers.StatusNo)=3 
    Or (Volunteers.StatusNo)=4) 
    AND ((DeliveryType.DeliveryTypeID)=3) 
    AND ((qryAssembleallwithshared.VolunteerID) Is Null));
    Volunteers who DO share Addresses
    Code:
    SELECT Relations.AddressShare
    , Relations.VolunteerID
    , Volunteers.Prefix
    , Volunteers.FirstName
    , Volunteers.LastName
    , Relations.PreferredName
    , Volunteers.Address
    , Volunteers.Address2
    , Volunteers.City
    , Volunteers.State
    , Volunteers.PostalCode
    , DeliveryType.DeliveryTypeID
    FROM DeliveryType 
    INNER JOIN (VolunteerType 
    INNER JOIN (Volunteers 
    INNER JOIN Relations 
    ON Volunteers.VolunteerID=Relations.VolunteerID) 
    ON VolunteerType.VolunteerTypeID=Volunteers.VolunteerTypeID) 
    ON DeliveryType.DeliveryTypeID=Volunteers.DeliveryTypeID
    WHERE (((VolunteerType.VolunteerTypeID)=1 
    Or (VolunteerType.VolunteerTypeID)=2) 
    AND ((Volunteers.StatusNo)=3 
    Or (Volunteers.StatusNo)=4))
    GROUP BY Relations.AddressShare
    , Relations.VolunteerID
    , Volunteers.Prefix
    , Volunteers.FirstName
    , Volunteers.LastName
    , Relations.PreferredName
    , Volunteers.Address
    , Volunteers.Address2
    , Volunteers.City
    , Volunteers.State
    , Volunteers.PostalCode
    , DeliveryType.DeliveryTypeID
    HAVING (((Relations.AddressShare)=True) 
    AND ((DeliveryType.DeliveryTypeID)=3));
    Combination of the Volunteers
    Code:
    SELECT VolunteerID
    , PreferredName
    , Address
    , [Address2]
    , City
    , State
    , PostalCode
    FROM qryAssembleallwithshared
    UNION SELECT VolunteerID
    , PreferredName
    , Address
    , [Address2]
    , City
    , State
    , PostalCode
    FROM qryAssembleFindNoShared;
    Last edited by ironchef; 01-30-04 at 18:12.

  2. #2
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    snapshot

    i have the mailing list working now...however the report/labels...i can't get them formatted correctly....because of the variableness of the labels. With some assistance from an associate, I used grouping in the report to group people in the same household. So....i get two different results....

    1. Billy Bob Mason
    Jenny May Mason
    123 Hillbilly Hay Road
    Cleveland, OH 44124

    and ....

    2. John Wilson
    123 West Bank Road
    Cleveland, OH 44124

    The first one has 4 lines and the second has 3 which messes up any formatting for the labels. The alignment specifically.

    Take a look at the snapshot to see what is happening.... any solutions????

    Regards,

    BAF
    Attached Thumbnails Attached Thumbnails snapshot.jpg  

Posting Permissions

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