Combining multiple rows into 1

[mach27]

Hello Experts,

I am having a hard time understanding other forums if it has any relation to what I want to happen.

I have a table which is a result of an append query where it appends the value of each month. 1 'Submission' may have multiple entries.

Table 1
Submission	MType		Jan	Feb	Mar	Apr	...	Dec
CB00002402	OOP Growth			1
CB00002402	OOP Growth						10
CB00002538	OOP Growth				 13
CB00002538	AOP Growth		  13
CB00002538	AOP Growth						4

What I want to do now is combine multiple rows into 1 where 'Submission' AND 'MType' appear more than once, just like below:

Table 2
Submission	MType		Jan	Feb	Mar	Apr	...	Dec
CB00002402	OOP Growth		   1			10
CB00002538	OOP Growth				 13
CB00002538	AOP Growth		  13			4

I have tried the UPDATE function but wondering why it is not capturing everything. I don't know what did I miss in the syntax.

Thanking you all in advance

Mach

 

[lespaul]

It looks like you want a cross tab or pivot query....what does your raw data look like (not the table created from the append, but the real data)

Leslie

Have you met Hardy Heron?

 

[PHV]

INSERT INTO [Table 2] (Submission, MType, Jan, Feb, ..., Dec)
SELECT Submission, MType, Sum(Jan), Sum(Feb), ..., Sum(Dec)
FROM [Table 1]
GROUP BY Submission, MType

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mach27]

hi lespaul

Table 1 (above) came from the comparison of 2 tables

Table AOP
Submission    Jan    Feb    Mar    Apr    ...    Dec
CB00002402    10
CB00002538                  13      2

Table MPO
Submission    Jan    Feb    Mar    Apr    ...    Dec
CB00002402            1            10
CB00002538            13    13      4

I used the append query after comparing the monthly data of each submission. There are so many conditions to take into consideration before arriving to the right MType tag.

For example, comparing the data of "CB00002402" will result to:

Table 1
Submission    MType        Jan    Feb    Mar    Apr    ...    Dec
CB00002402    OOP Growth            1
CB00002402    OOP Growth                        10

Is there an easier way of putting up these things? Also, I can't see the option for the pivot query, where can I find it, or rather, how can it be done?

Thanks,

 

[mach27]

Thanks PHV
Your solution works.

 

[dhookom]

Mach27,
Not that PH needs any more stars ;-) but you should award valuable posts.

Duane
Hook'D on Access
MS Access MVP