Pulling data that only accompanies previous rows

[cmdahms]

I need some help on a problem I am running into for work. I think it has to do with the default set up of access to folderize columns. I already have 5 of the 6 columns set up the way I would like but as soon as i add the sixth I am seeing data that I don't want to see. For example as shown below I have 5 columns of year, version, minofbasis(total cost), type, itemnumber. But if I add the column for extended price of the specific item number it then adds a second set of data which is different from the first (as shown in the second picture). What I would like to happen is solely adding the data of the price for the already shown item numbers based upon the other data in each road. It should be specific and I am hoping to end with the same number of rows as before I add the 6th column. I would love some help. This problem has held me up for a day of work and I can't watch youtube to better understand access as I'm at work.

[aytee111]

You must be merging two sets of data, one which has multiple rows per keys. Each one gets calculated for each one, the first one has two records per one on the left, so the calculation gets done twice.

[cmdahms]

Yes I believe so. Is there any way to accomplish what I want?

There is a way to make it unique by using another column. Each extended price has a unique planholder id AND version but not one or the other could that work to my advantage?

[aytee111]

The way you are showing your data above it would need the extended price to be summed. Also there needs to be more joins, I think - not knowing what your other data is it is hard to tell. What do you want your output to look like? Multiple rows or one single one?

[NTC]

The method to resolve this depends on the structure of your data. If your query joins on just 1 field between the 2 tables; and both tables have multiple values then you'll end up with the results you have. It is possible that two join lines, possible even 3, could effectively result in a unique 1:1 relationship between records in both tables.

If not - then you'll have to set up a query of 1 table that correctly defines the record set needed so that you can then use this query in your next query rather than using one of the tables directly.