Database

[Kartik2]

I have different materialcodes in two different tables named as  "IdleInventory" and "WeeklyInventory" , but to link  these two tables together I have kept the "MaterialCode" field name same in both the tables.

I have a field name as "MonthWeek" but to avoid synthetic  key error  ,I have given different name to it in another table

But I want to use a single "MonthWeek" field as a filter in my dashbaord which will give proper data of both the Tables 

How can I do this ?

[vincent_ardiet_ · Accepted solution]

You can use AutoNumber, AutoNumberHash or Hash functions to create a key composed of MaterialCode and MonthWeek in each table.
Then, create a link table with the newly created key + MaterialCode and MonthWeek (of one of the table or both regarding if all combinations are available in all tables or not) and this will avoid the synthetic key.

[sidhiq91]

@Kartik2  Could you please provide a dataset which will help us understand the issue and help you to resolve it?

[Kartik2]

Hey @sidhiq91 , Thanks for asking ,but i have resolved the issue