sum matching records with one different field

[hawg1]

I created a query that finds duplicate records based on the NIIN field from excel data tthat is imported into access. The result looks like this.

FSC --- NIIN ------- SD --- Net QTY -- NRTS QTY -- Credits -- Net ------ Noun
1650 -- 10281115 -- MSD -- 6 --------- 6 ---------- $0 ------- $90,247 -- MANIFOLD ASSEMBLY,H
1650 -- 10281115 -- MSD -- 3 --------- 3 ---------- $15,041 -- $45,123 -- MANIFOLD ASSEMBLY,HYDRAULIC



I don't control how the data is entered into the excel worksheets. They come from a myriad of external sources. Here is what I am alluding to; both of these entries, based on the NIIN field, are identical items (the dashes represent column breaks); the Noun field in one record is truncated in the excel sheet for some reason I can't explain:

What I wish to do is combine these two records into one, with the Net QTY, NRTS QTY, Credits and Net columns summed up but also insert one of the two Noun fields (the longer string) so that it would look like this:

FSC --- NIIN ------- SD --- Net QTY -- NRTS QTY -- Credits --- Net ------- Noun
1650 -- 10281115 -- MSD -- 9 --------- 9 ---------- $15,041 -- $135,370 -- MANIFOLD ASSEMBLY,HYDRAULIC

Any Ideas?

[evander]

Use Microsoft Excel PivotTable. I'm absolutely sure this will work for you. But I think you should be looking for Excel forums instead.

[hawg1]

Use Microsoft Excel PivotTable. I'm absolutely sure this will work for you. But I think you should be looking for Excel forums instead.

evander,

this is not an excel table but an access table. The data originated excel but is then imported into access.

[evander]

Then create a Group By query. It basically achieves the same purpose as a PivotTable.

[hawg1]

Then create a Group By query. It basically achieves the same purpose as a PivotTable.

I have already done that. But due tothe differences in teh NOUN field of the two records, access doesn't not think they are teh same record when in reality they are.

[evander]

So basically, if the two records have the same FSC, NIIN, and SD values, such records will be "grouped" as one, regardless if they have different Noun's?

Is this a one-time operation, or do you need to automate this task every time you import data from Excel?

[hawg1]

So basically, if the two records have the same FSC, NIIN, and SD values, such records will be "grouped" as one, regardless if they have different Noun's?

That is correct!

Is this a one-time operation, or do you need to automate this task every time you import data from Excel?

This would need to be an automated function as the excel data would be imported each month.

[evander]

How will the automated procedure know which "Noun" should a particular row take up, given a list of similar "Noun"s. Is it always the longest?

I'm on my way to solving your problem.

[evander]

This solution may not be the most elegant, but at least it works for me.

[hawg1]

This solution may not be the most elegant, but at least it works for me.

Thanks for the code, I'll look it over and see if that will resolve my situation.

Thanks for the help