Multiple rows into one row via a query or code

[a1sjb]

Hi All


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 is 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

 

[CJ_London]

your table design is wrong you are including field values as a field names

table design should be

Partno...PriceType....Price
12345........3.................1.20
12345........1.................2.30
etc

then to get prices on one row, either use a crosstab query or research what is commonly called a 'concat related' function - e.g. see this link https://www.access-programmers.co.u...-multiple-rows-into-one-single-column.322682/

 

[arnelgp]

create a Total query:

select PartNo,
Sum(Price1) As [Price 1],
Sum(Price2) As [Price 2],
Sum(Price3) As [Price 3],
Sum(Price4) As [Price 4]
from yourTableName
Group By PartNo;

 

[GPGeorge]

Cross posted and answered at Microsoft Answers.

 

[Pat Hartman]

Creating a sum will work but it is a bandaid. Fix the table design issue to actually fix the problem.

 

[Gasman]

Cross posted and answered at Microsoft Answers.

Posted there first as well.