Access table - bringing data from different rows into one row

Question

MS Access table problem

I can't quite work this one out - if this can be done in a query if would be best, but code would be fine to.

Current Table info:-

PartNo Price1 Price2 Price3 Price4

12345 0.00 0.00 1.20 0.00

12345 2.30 0.00 0.00 0.00

12345 0.00 3.20 0.00 0.00

12345 0.00 0.00 0.00 5.20

PartNo is the same, but different prices in each column, what I want if the following:-

PartNo Price1 Price2 Price3 Price4

12345 2.30 3.20 1.20 5.20

So multiple rows of the same PartNo into one row showing all the prices - there will never be more than one price in each column per PartNo

Can this be done?

Thanks in advance

Answer 1

On the Create tab of the ribbon, click Query Design.

Add the table.

Add the PartNo, Price1, Price2, Price3 and Price4 fields to the query grid.

Click Totals in the Show/Hide group of the Query Design tab of the ribbon.

Leave the Total option for PartNo set to Group By, and change that of the other four fields to Max.

Answer 2

Thanks Grover - I agree, this has been a bit of a nightmare to sort - it was originally a spreadsheet that was imported, hence the spreadsheet style, now that the information is a little more structured, I intent to change to a more Access friendly design.

Thanks again

Answer 3

Thanks HansV - Did the job perfect

Answer 4

As George said you have a design problem. You said; "there will never be more than one price in each column per PartNo". Is there some other data in the record that indicates which price column should be used? If not there should be. That means you should have a table like this:

PriceID (PK Autonumber)

PartNo (FK)

PriceTypeID (FK)

Price

Then have a separate table that lists the different types or reasons why a different price would be used.

If you need to see all the different prices for a part, a crosstab query would then list them.

Answer 5

An aggregate query can return the required results, yes.

SELECT PartNo, Sum(Price1) , Sum(Price2), Sum(Price3) , Sum(Price4)

FROM YourTableNameGoesHere

GROUP BY PartNo

However, the real problem is the table design itself.

This is what is usually referred to as a "Spreadsheet Style" table and is a very common table design flaw.

Here is a link to a series of three or four blog articles explaining this problem and the proper way to correct it.

This query would only be a work-around, not a solution.