Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query design based upon results of 2 queries (97)

    I designed 2 queries. One query returned those records where field "ReceiveMailatAddress1" was "yes".
    This query returns the needed data for Address1 for labels.

    I designed another query which returned the records where field "ReceiveMailatAddress2" was "yes".
    This query returns the needed data for Address2 for labels.

    Both queries return appropriate/accurate results.(When Address1 is yes - I have coded it to make Address2 a no and vice versa.)

    Now I want to combine them into one query where I get All the Records from Query1 and All the records from Query2.

    I can't seem to get to work. I keep getting 48 records when there are only 14 records total.

    Any suggestions?

    Soc

  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 design based upon results of 2 queries (97)

    Are you trying to get all records where ReceiveMailAddress1 = yes OR ReceiveMailAddress2 = yes? Why not just create a third query and use that criteria to return the records you need?

    Try posting your SQL so we can tell exactly what you're trying to do.
    Charlotte

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query design based upon results of 2 queries (97)

    1. I have 14 records.
    2. 8 of those records have a "yes" in the field "receiveMailatAddress1. These same records have a "no' in the field ReceiveMailatAddress2.
    3. 6 records have a 'yes" in field ReceiveMailataddress2. These same records have a "no" in the field receiveMailatAddress1.

    So when I do mailing labels I need all the records. But some of them have to go to Address1 and some have to go to Address2.

    Each record has data in both Address1 and Address2. In the Winter some people go to Florida which is their Address2 and this is where I want to send their mail. This is when ReciveMailatAddress1 becomes a "no" and ReciveMailatAddress2 becomes "yes".

    I'm trying to get either Address1 or Address2 for maili labels. This is dependent on the value of the fields:
    receiveMailatAddress1 and ReceiveMailatAddress2. I did this with 2 different queries. The results work out fine. Now all I want to do is combine the results of those 2 queries into one query. This way I can sort them on Zip Code for the bulk mailing at post office.

    The SQL for Query1 is: SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Address1, tblPeople.[Apt#1], tblPeople.City1, tblPeople.State1, tblPeople.Zip1, tblPeople.ReceiveMail1
    FROM tblPeople
    WHERE (((tblPeople.ReceiveMail1)="YES"))
    ORDER BY tblPeople.Zip1;

    The SQL for query2 is: SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Address2, tblPeople.[Apt#2], tblPeople.City2, tblPeople.State2, tblPeople.Zip2, tblPeople.ReceiveMail2
    FROM tblPeople
    WHERE (((tblPeople.ReceiveMail2)="YES"))
    ORDER BY tblPeople.Zip2;



    I hope i'm being clear.

    Soc

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Query design based upon results of 2 queries (97)

    What I usually do is preface the matching (but differently named) fields with a common name in the query designer. For example:

    Addr: Address1 in the first query
    Addr: Address2 in the second query

    Then I would create a Union query that combines the two sets of results into one, in which case Addr is the address for all records. 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
  •