How to link 2 tables with more than one common fields

[Anonymous]

I have 2 excel sheets project and Sale

Both Project and Sale excel sheets have the field ProjectID and saleID. I need to link the two tables avoiding a synthetic key.

Is it suggested that I comment SaleID in the Sale file and link both Project and Sale file using ProjectID.

Please give me a solution so that there are no data issues.

[swuehl]

We can't tell you if it's enough to only use ProjectID as linking key field in your model.

That's depending on if it's enough to analyze your data on project level (so depending on your analysis requirements).

In general, you can avoid the synth key by using a combined key, made of ProjectID and SaleID, e.g. by concatenating the two field values

LOAD

     ProjectID &'-' &  SaleID as CombinedKey,

     ....

or by using e.g.  one of the AutonumberXXX() functions:

LOAD

     AutonumberHash256(ProjectID, SaleID) as CombinedKey,

     ...

You then need to remove /rename the original key fields from the tables where there are used as foreign keys, e.g.

rename SaleID to SaleID_foreignKey in your Project table (or any other name not building a common key).

Or just live with the synth key, it's not necessarily a bad thing:

Synthetic Keys