Use Relate() Function

[flaxman64]

Hi,

I have 2 tables that are related. One table has all data available for all post entered in the database. The other table has some of the post, but not everyone.

I want to get some data (one Colum) from the second table to the first. As I said they are related and I thought the Relate() function should work.

The problem I get is that there are about 800 values in the second table with a specific value. When I use the relate function to the first table I count the specific values and there are 340 more values than the original table (1140)

I thought that the rows that did not have any value because there where not present in the second table should be blank in the first table. For some reason I have more values?

I have been struggling with this issue for some days now and thought I ask somebody that knows! I hope this makes sense......

Mats

 

[scottsen]

I am kinda wondering if the direction of your relationship is correct? Sounds like you might have a 1:1 relationship (typically you have a 1:many) and might have the direction reversed, but... just a guess. hard to say w/o seeing more of the model.

 

[flaxman64]

Hi,

Thanks for you answer. I have been investigated some more. Even though there are no doubles in any of the tables ID field I noticed that when working with the data some data that was for as an example 2012 was present when filtering data on only the year 2013. Strange I thought!
In regards to your answer I turned my focus on the relation. I tested some and added the year to the "file Number" that is the unique ID field, "File number - Year" (12345-2014). Then I used this for the relation and then everything worked as it should. The values from the table with not all fields available only imported the correct values and the count on row was correct.

I don't know why it was not possible to perform the relation on the ID filed alone without the year part.

Maybe there is an obvious reason that I'm not aware of. If anybody has a theory please let me know.

 

[scottsen]

Hard to say w/o seeing the model and data, but any time I see "the filter didn't work as I expect..." I expect somebody is not properly understanding the "direction" of the relationship. Power Pivot Relationships | Power Pivot | Tiny Lizard