Multiple rows to single row separate fields

[craigcam]

I have multiple rows of data for a specific number and I want to show it all on one line as a result.

table as follows

Article	Case weight	Inner weight
123	4
123		6

Result should be

Article	Case weight	Inner weight
123	4	6

I have access 2003.


Thanks,

[JoeM]

This can be done easily with an Aggregate (Totals) Query.
Simply do the following:

1. Create a new query based on this table
2. Add the three fields above to the query
3. Click on the Totals icon (looks like a Sigma)
4. Under the two weight fields, change the phrase "Group By" to "Sum".

View your results

It "groups" the records by the Article number, and sums the other two fields.

[pbaldy]

This would work on your example:

SELECT Article, Max([Case Weight]) As CaseWeight), Max([Inner weight]) As InnerWeight
FROM TableName
GROUP BY Article

[alansidman]

You can also load your table to Power Query/Get and Transform and reorganize your data there.