How to update/add records in a query based on two other queries? 1

[dwinokur]

I have a query that is based on 3 tables, as follows:

Industry Sectors
----------------
Sector ID
Sector
Sector Description

Companies
---------
Company ID
Sector ID
Company
Employees
Revenue
Growth Rate
Headquarters

Companies Investors Junction
----------------------------
Company ID
Investor ID

The query returns the Investor ID field from the last of these 3 tables combined with the three fields from the Industry Sectors table to give me a summary of the sectors represented by the companies in a particular investor's portfolio.

I have a separate query based on 2 tables:

Industry Sectors
----------------
Sector ID
Sector
Sector Description

Investors Sectors Junction
--------------------------
Investor ID
Sector ID

This query returns both fields from the second table combined with the Sector and Sector Description fields from the first table to provide a summary of the sectors in which a particular investor typically invests.

In other words, these two queries both return the same fields (Investor ID, Sector ID, Sector, Sector Description), but from a different combination of tables and with a different set of records.

I would like to create a query that I can use as the basis for a subform to a parent Investors form. Ideally, I would like this subform to represent all of the sectors in which a particular investor has invested or intends to invest. In other words, if an investor has a company in its portfolio, that company's sector should appear on the list. This is provided by the first query I described. However, I want the subform to also include and allow for the entry of additional sectors in which the investor may not have any actual investments yet. This is represented by the second query I described.

I cannot figure out how to combine these two queries in such a way that I can add new records. The closest I've come is in creating a UNION query that combines the results of the two queries to create a list distinct sectors by investor. However, this UNION query does not allow the entry of new records. I have tried building various other queries that combine the underlying tables of both queries, but cannot figure out how do so in a way that allows the entry of new records.

Logically, it seems there should be a way to add new records that, beause none of the Companies table fields are represented, default to only updating the second query and its underlying tables (Industry Sectors and Investors Sectors Junction). Put another way, the subform should be a way to enter sectors in which an investor invests, but it should automatically be updated to include sectors of companies in which the investor has made actual investments if any of those sectors are not already on the list.

Any help would be greatly appreciated, as I've been working on this for awhile and am stumped.

Thanks,
Danny

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwinokur]

Thanks for the pointer. While the article was educational, I have not been able to figure out how to solve my problem using joins, inner or outer. The problem is that I'm ultimately trying to achieve a *union* of tables/queries, to which new records cannot be added. However, I have come up with a solution, which is working. That is to make the 1st query an append query that adds Sector ID/Investor ID records to the Investor Sectors Junction Table every time the query runs. I do this through a little VB routine that turns off warnings, and duplicates are avoided because the Sector ID/Investor ID fields are together the primary key for Investor Sectors Junction Table. It's now working like a charm! Thanks again for your help.