Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union query (Access 2000)

    I have an union query that works excellent.I want to add two more fields to the query but i cannot do it. Can you help ? The fields are called Notes and Subject from two additional tables CallsClients and CallsCustomers.
    Attached Files Attached Files

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

    Re: Union query (Access 2000)

    Your union query doesn't work since it includes fields such as kindid and city that are not present in either the customers or the clients table.

    You haven't defined any relationships between (for example) the customers and customer calls tables, so it is impossible to know how you want to combine the tables.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union query (Access 2000)

    Sorry i have added the missing fields. After that i defined the relationships as you san see in the db now.My problem is whether i could add the fields callid,subject and notes to the query. The query does not accept them, and i do not know is it possible at all, may be i am violating some rules ?
    Attached Files Attached Files

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

    Re: Union query (Access 2000)

    You will have to join the customers and customer calls tables in the query, and also the clients and client calls tables.

    Create a new query in design view.
    Add the customers and customer calls tables to the query grid.
    Double click the join line between the tables.
    Select the option to include all records from the customers table, then click OK.
    Add the fields from the customers table that you want in the union query, and also those from the customer calls table.
    You should not use the customerid field from the customer calls table, only that from the customers table.
    Switch to datasheet view to test the query.
    Then select View | SQL to see the SQL text of the query.

    Leave this window open, and create a new query in design view.
    Add the clients and client calls tables to the query grid.
    Double click the join line between the tables.
    Select the option to include all records from the clients table, then click OK.
    Add the fields from the clients table that you want in the union query, and also those from the client calls table.
    You should not use the clientid field from the client calls table, only that from the clients table.
    Switch to datasheet view to test the query.
    Then select View | SQL to see the SQL text of the query.

    Leave this window open, and open the union query in design view.
    Delete the SQL statement.

    Switch to the first new query.
    Copy the SQL.
    Paste it into the union query.
    Delete the ; at the end.
    Type the word union below the SQL, then press Enter.

    Switch to the second new query.
    Copy the SQL.
    Paste it at the end of the union query, below the existing text.

    Switch to datasheet bview to test.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union query (Access 2000)

    Thank you for your clear instructions. I am struggling to build it but i still cannot do it. I have built the 2 queries but there i cannot understand how could i paste it since i cannot find the union query on which to paste it
    Adding something always gives me some error.How could i open a union query in design view ?
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Union query (Access 2000)

    Create a new query based upon nothing. Put this query into SQL view, a union query cannt be viewd in design mode.
    Open the first of the queries you wish to make as part of the union query adn copy/paste the SQL of this query into the newly created query which should still be in SQL view.
    At the end of the pasted SQL code delete the ; if there is one. Put the word UNION ALL at the end of the SQL code, open your 2nd query in SQL view and copy/paste it into the union query.
    This is then a union query.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union query (Access 2000)

    Success ! Thank you so much both of you, following your advices i succeeded at last to build my union query, i am really so much grateful to you. My query works excellent now

Posting Permissions

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