Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filtering in Union Query (Access 2000)

    Hi,
    Can anyone point out to me why I am getting a parameter prompting for RelType when I run this Union Query? I'm obviously violating something of am not aware of some limitations regarding Union Queries!

    The SQL is:
    SELECT Customers.[First Name] AS FN, Customers.[Last Name] AS LN, Customers.Title AS TL, Customers.[Work Phone] AS PH, "C" AS RelType
    FROM Customers
    UNION SELECT Employees.FirstName, Employees.LastName, Employees.Title, Employees.HomePhone, "E"
    FROM Employees
    WHERE RelType = "E"
    ORDER BY TL, LN;

    Thanks
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering in Union Query (Access 2000)

    You're trying to define RelType and filter on it at the same time. If you only want employees, not customers, you don't need a union query anyway.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Union Query (Access 2000)

    >>> If you only want employees, not customers, you don't need a union query anyway.
    This is true!
    Thanx

    However (just out of interest!), is it possible by changing the SQL to filter only for Emp's, or must I create a second query?
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering in Union Query (Access 2000)

    You'd have to create a second query based on the union query.

    Note: there is a discrepancy between the SQL in your post and the one in the screenshot. One mentions "E" and the other "Emp".

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Union Query (Access 2000)

    OK!

    Yes, I changed the SQL slightly just before posting it, and the screenshot is of the original SQL. Thx, I am aware of that!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filtering in Union Query (Access 2000)

    The problem is unrelated to the fact that you're using a union query - any statement along the lines of SELECT A, B, C AS D FROM XYZ WHERE D = "" will result in the user being prompted for a value for "D". The reason is that D doesn't exist as a field in source tables to the query itself, it's merely a label applied to a field or expression on execution. In the same way, you can't use GROUP BY D either, you have to GROUP BY whatever it was you'd renamed as "D".

    As any WHERE, GROUP BY etc clause operates on the source data before the query begins its output, these labels don't really exist at that point.

    What you can do, of course, is something like SELECT "A" AS C, "B" AS D, C & D AS E FROM XYZ, because the expression is then working on the output values, not trying to apply it to the input values.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Union Query (Access 2000)

    Thanx Simon...you have put it very clearly!
    It now makes total sense!

    See ya around! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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