Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: can union queries be updateable?

    I have two tables, sales and purchases, which are quite similar. For one particular purpose, I would like them listed in one table, with data from each table on their own rows, first the purchases then the sales. I can do that with a union query, but it is (by default?) not updateable. Is it possible to edit/change records from two tables displayed like this?

    Each row contains fields/data from only one table.

    The first column contains the ID, listing first the purchases then the sales records.

    The fields behind it is ID_buy and ID_sell, so in the union I have specified "ID_buy as Transact-ID" in order to have the title of the field show Transact-ID instead of ID_buy, since there is sales in the list too. Can this be the problem?

    I have specified all fields from both tables in one and the same union query. Perhaps I should have only taken the ID fields in one and then made another one? I have seen some tricks on combining queries, but does not know enough about advanced queries.

    I need to use the query as basis for a form where the user should be able to change the value of a couple of the fields.

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Re: can union queries be updateable?

    ok, I see that union queries is not updateable.
    Anyone with tips on how to get arond it?

    I can have a update query based on the union query on a third table which can be updated, then programatically in the form where users update the third table, I can ensure that the changes are propagated to the first two, any simpler ways to handle this?

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I am affraid no, but why would you build a 'hopelessly' complex union query for users to edit data in.
    Either your DB is not properly/optimaly constructed or your query can be done another way so your data IS updateable....

    Regards

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by namliam
    I am affraid no, but why would you build a 'hopelessly' complex union query for users to edit data in.
    Either your DB is not properly/optimaly constructed or your query can be done another way so your data IS updateable....

    Regards
    Well, it seems that union queries can be quite simple as well, as in my case. But actually I am looking for a way to have just two tables joined in for example a query or set of queries.

    I am uncertain about the alternatives. Hence, my question is just about that:

    What is the alternative when one wants to display two tables in an updateable list, or say - an updateable and convenient way for the user, regardless what goes on behind the scenes? The the whole point is to avoid having to update two places/lists.

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    select * from table1
    union
    select * from table2

    is a simple enough query but if table1 and table2 have the same sort of data which you want to update at 1 go, they should be in 1 table.
    Mosttimes these tables excist to populate listboxes or menus. So they can be filled by Select * from table1

    However if you add a number field to the table you can have
    Select * from table1 where number = 1
    Have table2 (and possibly table3,4 and 5) in the same table, updating it in 1 form, showing it in 10 different places....

    Thus.... my thinking.....
    What is your reasoning for having 2 tables?
    What are your tables like?
    What are the dependencies?
    What is your goal?

    Regards

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Hi kedaniel,

    As you are probably aware, there are some rules about why a particular query is updateable or not. This has to do, of course, with the relationships between the tables in the query; this means that some types of query, like Union queries and Aggregate queries, can not be edited by the user.

    Because of this, it is usually general practice to give the user separate forms where they can update tables. This gets around some of the problems you can face when trying to update tables in a query. For example, in a query that has one simple one-to-many relationship, you would want to update the "many" table and not the "one" table. Probably.
    If you include a field from the wrong table, you can achieve complete disaster.

    That is not to say, of course, that queries cannnot usefully be used to update data. They are at their most useful when inputting data into the "many" side of a relationship, where they look up data in table(s) on the "one" side of a relationship. For example, when the user enters a customer ID into the query for a new sales record, the query automatically displays the Customer's name, address etc. But this, of course, is only updating one table and merely using the other to display lookup information.

    With regard to a Union query, one solution might be to append all the records from the query into a temporary table and then let the user edit that. You can then run an Update query on both tables in the Union query (the record will only exist in one of them) to update the data. It's a messy solution and not recommended for multiple users.

    Anyway, I hope all this helps. The best solution is to edit the data for each table through a form and leave it at that.

    Lastly, I live in Spain: where are you?
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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