Query/Join Problem (1 Viewer)

[CNW]

I've set up a database that has three key tables and I can't figure out how to set up a query to get the correct combined information from the tables. I thought it was a problem with my relationships and I needed to create a junction table but that hasn't worked either. Can any one help?

The tables are:
FundCode
FundCode: PK
Other fields with text data about each fund code

Uses
UseID: PK
Other fields include:
FundCode
$AmountsofUses by year

Sources
SourceID: PK
FundCode
$AmountsofSources by year

I have a 1:M relationship set up between Uses & FundCode table and a 1:M relationship set up between Fundcode and sources.

I want to run a query that will allow me to total the $ amounts of all sources for a particular fundcode and total the $ amounts of all uses. I want a separate field that will calculate the bottom line for that fund code. I can't keep the query from repeating each source for every use or vice versa which results in a error in the totals since it calculates the same number twice.

Can anyone help me?-CNW

 

[Pat Hartman]

You can't join unrelated tables in a single query and expect to obtain a result that makes sense. While uses and sources each have a relationship to funds, they have NO relationship to each other. When you create a query with more than two tables, the tables MUST have a hierarchical relationship (except for lookup tables). A hierarchical relationship would be something like Company--Division--Branch or Customer--Order--OrderDetails.

If you don't need any details from the uses and sources tables, you can effectively reduce them to lookup tables. Create a totals query for the uses table. Create a totals query for the sources table. Create the final query that joins funds (using a left join) to each of the other two queries. Since there will be only one row per fund in the uses and sources queries, you will end up with only one row for each fund in the final recordset.