Problem Query Wizard Build a select query by using tables with a many-to-many relatio

[fboehlandt]

Hi everyone,
I have the following three tables in my database:

ADMIN.

,[Name],[Country]
PERFORMANCE.[Code],[Name],[Date],[Return]
ASSETS.[Code],[Name],[Date],[Assets]
 
The first table contains general information about an investment fund, the primary key is Code (unique). The other two tables contain performance data (returns and assets-under-management) for different dates. The primary keys for both are composite keys ([Name]-[Date]). The combinations are unique. The relationship is one-to-many between ADMIN and PERFORMANCE and ADMIN and ASSETS respectively. [Code] is the foreign key in the two child tables.
 
I would like to run a search query that combines the information of the three databases. So far, I can combine ADMIN with PERFORMANCE or ADMIN with ASSETS, but not all three. PERFORMANCE contains shlightly more datapoints than ASSETS does (I expect that this causes some problems). I want my query to look like this:
 
[Code],[Name],[Country],[Date],[Return],[Assets]
 
Code1, Name1, Country1, Date1, Return1, Assets1
Code1, Name1, Country1, Date2, Return2, Assets2
Code1, Name1, Country1, Date3, Return3, Assets3
Code2, Name2, Country2, Date1, Return1, Assets1
Code2, Name2, Country2, Date2, Return2, Assets2
Code2, Name2, Country2, Date3, Return3, Assets3
etc.
 
Where nothing is displayed in an Assets field when Return datapoint exists but Assets entry does not.
 
The query gives me:
 
Code1, Name1, Country1, Date1, Return1, Assets1
Code1, Name1, Country1, Date2, Return1, Assets2
Code1, Name1, Country1, Date3, Return1, Assets3
Code1, Name1, Country1, Date1, Return2, Assets1
Code1, Name1, Country1, Date2, Return2, Assets2
Code1, Name1, Country1, Date3, Return2, Assets3
Code1, Name1, Country1, Date1, Return3, Assets1
Code1, Name1, Country1, Date2, Return3, Assets2
Code1, Name1, Country1, Date3, Return3, Assets3
etc.
 
Can anyone help?
Thanks
Florian

 

[DCrake]

One problem you are going to come accross is naming conventions. You have given field names words like Code, Date, Name that Access recognises as reserved words. I suggest you corect these in the first instance before readdressing your first issue.

 

[fboehlandt]

Thanks for the quick reply, the field names are sponsored by the database provider but I will alter them manually and see what the results are.

 

[fboehlandt]

okay, the table fields look like this now:
ADMIN.[Coding],[Fund],[Country]
PERFORMANCE.[Coding],[Fund],[MM_DD_YYYY],[Return]
ASSETS.[Coding],[Fund],[MM_DD_YYYY],[Assets]

the query:
[Coding],[Fund],[Country],[MM_DD_YYYY],[Return],[Assets]

still no change,
any further suggestions?

 

[boblarson]

If you want combined data, use a Union Query to pull what you need.

 

[fboehlandt]

Used the Union query with the following sql-thread:

Select [Coding,[MM_DD_YYYY],[Performance]
From RETURNS
UNION Select [Coding],[MM_DD_YYYY],[Assets]
From ASSETS;

Result as follows
[Coding],[MM_DD_YYYY],[Performance]

Coding1, MM_DD_YYYY1, Performance1
Coding1, MM_DD_YYYY1, Assets1
Coding1, MM_DD_YYYY2, Performance2
Coding1, MM_DD_YYYY2, Assets2
Coding1, MM_DD_YYYY3, Performance3
Coding1, MM_DD_YYYY3, Assets3

When it should look like this:
[Coding],[MM_DD_YYYY],[Performance],[Assets]

Coding1, MM_DD_YYYY1, Performance1, Assets1
Coding1, MM_DD_YYYY2, Performance2, Assets2
Coding1, MM_DD_YYYY3, Performance3, Assets3
Coding2, MM_DD_YYYY1, Performance1, Assets1
Coding2, MM_DD_YYYY2, Performance2, Assets2
Coding2, MM_DD_YYYY3, Performance3, Assets3
etc.

Apparently I am doing something wrong when trying to unify the two tables

 

[DCrake]

When creating union queries with tables that have mismatched field names that you want to combine vertically, you need to give them the saem name. such as:

Select [Coding,[MM_DD_YYYY],[Performance] As AnyName
From RETURNS
UNION Select [Coding],[MM_DD_YYYY],[Assets] As AnyNameFrom ASSETS;

Then the results will appear in three columns with the contents of both the Performance and the Assets appearing underneath the AnyName column.

BTW you also have a field name the same name as the table name (Assets).

CodeMaster:

 

[fboehlandt]

Okay,
the union query works, but not as intended. What I would like to have is a query with four columns where Performance and Assets are adjacent. The fields Coding and MM_DD_YYYY are identical for Performance and Assets. The union query now looks as follows (example):
Coding

 

[fboehlandt]

Coding MM_DD_YYYY AnyName
9 31/1/1994 -2.56% --> Performance
9 31/1/1994 9.653 --> Assets under Management
9 28/2/1994 1.23%
9 28/2/1994 9.705
9 31/3/1994 -0.05%
9 31/3/1994 9.706

It is supposed to look like this:
Coding MM_DD_YYYY Performance Assets Managed
9 31/1/1994 -2.56% 9.653
9 28/2/1994 1.23% 9.705
9 31/3/2994 -0.05% 9.706

Maybe this is not possible?

 

[DCrake]

Then you do not need a union query

What you want is a Select query

Bring both the Assests and the Performace tables into the QBD pane and join together using the date and coding fields. Then drag down the assets field and the Performance field into the grid.

Run the query to viwew the results.

 

[fboehlandt]

I'm afraid I'm going around in circles. A select query is what I started out with. I had RETURN and ASSETS table joinded by a junction table (the relation is one-to-one). I assigned identical composite primary keys to RETURN and ASSETS in order to merge the information contained in both tables (note here that RETURN has 395985 records and ASSETS only 322878, is this a problem?). The result is displayed in my first post. I also tried to run the query without the junction table, but it didn't help. Also, Access does not accept a one-to-one relationship between RETURN and ASSETS. I'm an Access newbie and know very little about SQL, so please be patient with me!

p.s. code-date combination are unique, but RETURNS contains more performance observations than ASSETS does (some funds choose to report their returns but not their assets-under-management in a given month)

 

[neileg]

You problem is the join. Access defaults to an inner join (type 1 in Access terms). This only returns data if there is a match on both sides of a join. You need a left or a right join (type 2 or 3) so that you return all the data from one table and any from the other table that matches.

 

[fboehlandt]

I agree! This is what I wrote as a query (as I expect the default query would be an inner join):

SELECT RETURNS.Coding, RETURNS.MM_DD_YYYY, RETURNS.Performance, ASSETS.Assets_Managed
FROM RETURNS LEFT JOIN ASSETS
ON RETURNS.Coding = ASSETS.Coding AND
ON RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY;

This code fails on line "ON RETURNS.Coding": Syntax error missing operator in query expression". Once again, Coding - MM_DD_YYYY are the composite primay keys for the two tables. RETURNS table contains all Coding - MM_DD_YYYY combinations that ASSETS table contains but not vice versa. Must I include the junction table in the query as well to specify the relationship (one-to-one) between the RETURNS and ASSETS table (nested join statement)?

 

[fboehlandt]

If I integrate the junction table the query should look like this:

SELECT ADMINISTRATIVE.Coding, ADMINISTRATIVE.Name, RETURNS.Coding, RETURNS.MM_DD_YYYY, RETURNS.Performance, ASSETS.Assets_Managed
FROM ADMINISTRATIVE INNER JOIN(RETURNS LEFT JOIN ASSETS
ON RETURNS.Coding = ASSETS.Coding AND
ON RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY)
ON ADMINISTRATIVE.Coding = RETURNS.Coding;

Same story though

 

[fboehlandt]

Eliminating the extra "On" does the trick. Now I have the results I want with:

SELECT RETURNS.Coding, RETURNS.MM_DD_YYYY, RETURNS.Performance, ASSETS.Assets_Managed
FROM RETURNS LEFT JOIN ASSETS
ON RETURNS.Coding = ASSETS.Coding
And RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY;

 

[fboehlandt]

Thanks everyone!