Tricky query

[tommeekers]

I have 2 tables with client data, and 1 table with all the data about the orders these clients make. Now I'm trying to make a query which will show me all orders (ID and status) and the client name (which can be from any one of those 2 tables).

Here is what the database kinda looks like, but when I make this query, I get no results (cause the clientID in the order table only matches a record in clientsA OR in clientsB, never both)

                                     ------------
                                     | clientsA |
------------      -------------      ------------
|  status  |      |  orders   |    __| clientID |
------------      -------------   /  |   name   |
| statusID |__    |  orderID  |  /   ------------
|  status  |  \   | clientID  |_/   
------------   \__| statusID  | \    ------------ 
                  -------------  \   | clientsB |
                                  \  ------------
                                   \_| clientID |
                                     |   name   |
                                     ------------

I tried making a query which returns a list of ALL clients first, then using this query as a source for the order query. This way I do get the results I want, but I cannot edit those results (change the status) which is necessary. Anyone got an idea ?

 

[JerryKlmns]

Create a table clientsAB, containing data from both tables and add an extra column to destinguish if client belongs to clientsA or clientsB. Replace the usage of tables clientsA and clientsB with this new table, clientsAB.

 

[tommeekers]

I can't really do that, cause the data in clientsA gets downloaded from an Oracle database every night, while the data in clientsB is inserted directly from the application I'm developing. If I do it this way, I'll have to update the clientsAB table everytime a user inserts data into clientsB and every night during the download into clientsA.

I did create a query which joins the two tables, but when I use this as a source for my order query, I cannot make changes.

 

[JerryKlmns]

I meant : Don't use two tables.

And what keeps you from deleting all clients in clientsAb, where in that extra column are defined as Oracle and then import the data from Oracle into that table?

union query
A query that combines corresponding fields from two or more tables or queries into one field. It's the equivalent of appending one table to another.

For example, a union query of the Customers table and the Suppliers table returns a result set that contains all the specified records from both the Customers table and the Suppliers table. The result set isn't updatable.

 

[mp9]

Can't you UNION the two client tables together? (See

http://www22.brinkster.com/accessory/queries/002.shtml

for more on UNION)

Then use the Union in your query?

http://www22.brinkster.com/accessory/

 

[tommeekers]

I changed the query so that I will create a new temporary table and store the records I need (orderID, status and client name) in this table. That solved my problem. Thx.

 

[Prattaratt]

I'm still confused as to why you would want to have what is now THREE tables just to track the same data??? Just create one table, ClientData, and add a field that denotes whether the customer is a-list or b-list, default it to no, and update the table from the oracle using the same process you currently use. (unless you are overwriting the old clientsA table instead of updating?)

The simplest solution is the best!

 

[JerryKlmns]

mp9,
The union query resultset is not updatable, which is his goal.

Prattaratt,
That was my suggestion from the beggining. But tommeekers runs his bussiness and the choice is his.

Any way... he found a solution to works for him! At least for now.

I rest my case.-