Intermediate relationship and Many to Many

[Techno]

While joining two tables on columns that are not unique in either tables but represent same thing, the join type created is Intermediate and it is working fine for Many to Many relationship here. Then why do we need to create an extra joining table to create a many to many relationship (and in which case do we need it if any), can't we just keep on using Intermediate relationships?
Any help greatly appreciated.

[June7]

I have never head the term "intermediate relationship" - only 1-to-1, 1-to-many, many-to-many.

What is your data? What do you mean by 'represent the same thing'?

Can provide your db for analysis. Follow instructions at bottom of my post.

[Techno]

Attachment 50586

I am sorry...all this while I was misspelling "Indeterminate" as "Intermediate"

Represent the same thing - It means here Emp ID is the same data in both tables but both columns are not unique hence in this relationship there is no Primary key. Both foreign keys joined by an indeterminate relationship. And it's working fine.

[June7]

Your attachment link errors as "invalid".

You can join on those two fields but if the values are not unique in one you will probably get undesired results.

Try it. Do you like it?

[Techno]

Thank you

[June7]

Okay, you like the output but I don't understand why if neither field has unique values.

[Techno]

Both tables are fact tables. These tables are not manually written but system generated. These are listing spendings for employees for IT costs and entertainment, employee ID is the identification for an employee but a single employee can spend more than once on IT as well as entertainment. So Emp ID is a non unique field in both tables.
Now there was a need to pull some columns of data from entertainment table and add to IT costs via a query (those columns will have same values in every row for a single employee, means it would be correct to return the value found for the first match only for all matches) and hence relationship between these two tables was required. A table with unique Employee ID is not available to join these two. But simply drawing an indeterminate relationship with a left join between these two tables seems to have worked.

[Welshgasman]

You could create a query with unique values very simply?
Then probably UNION both tables with the Employee query.

[Techno]

Yes...thank you...I'll try this approach also...the Employee data in the query will keep on changing as per the changes in the table data it is drawing from.