I have just been given a task of creating reports for a new database and have run into an issue. It may be an easy one but I am not understanding what is wrong. In my data model i have a sales orders table and a stock table which describes our products etc. However, when i try to create a relationship between the stock codes in each table i am given an error as seen in the photo. I have checked and removed for duplicates as well as nulls and blanks yet still get the error. Any idea what could be causing this issue? I didn’t want to do a bad work around since its a pretty base/ major thing to set up right.
Ok, I don’t think you should be creating a relationship like this at all.
What you should be doing instead is creating a products lookup table, with each unique product value. Then you create a one to many relationship down to each of those tables.
You’ll see that I detail an example of how the model should be in this tutorial
Still having trouble with this, even though when cleaning data i removed all duplicates and blanks, i wrote a quick formula to check if indeed there was no duplicates by doing a count against stock codes (Primary Key) as well as just checking for count vs distinct count and the numbers were different.
As you can see applying the formula in the photo it gives me numerous stock codes that appear more then once. However when i look into the data without that count there is no duplicate of it that i can find what so ever. what would be causing this to counting duplicates? especially since i have said to remove all duplicates? Even when deploying your technique as described above, it still cant get around this duplicate issue.
Data Table as is - nothing applied (Can see there is no duplicates - this is the exact same data that the formula above was applied to but with no formula against it)
One things that can cause an issue here is if some of your columns have lower case versus upper case.
Can you actually just use COUNTROWS( tablename) as your formula. That’s all you need to check this. (I do this quite often and have had this happen occassionally)
Also double check that you are in fact checking this with a table at the highest level of granularity. Is the table definitely at the level of context?
Another tip. In the query editor, maybe test this and filter the table for only one of these stockcodes. That will make auditing this way easier. If you find the issue for one, you’ll find the general problem. I find this all the time.
