Combine same IDs in report but with Different Values

[taimysho0]

Hello, is it possible to combine rows with same channel ID's but with different values? for ex: in pic, you see the ID B0175 with 11 rows. is it possible to make this ONE row, but retain the values from all eleven rows and put them in one line? so it would display a "1" in whatever month it belongs to

[June7]

Is that how the table is structured? That is not normalized. Try an aggregate query with GROUP BY on ChannelID and Sum or Last aggregate function under each of the date fields.

[taimysho0]

what do you mean by normalized? thanks

[taimysho0]

"Last" looks like it only takes the last value and not all, "Sum" does not work, says that jetdatabase could not read data type, which im assuming is reading my "1" values as string. However, my months are constructed like this: Jan '11: IIf([Month]="January, 2011",Val("1"),"") shouldnt this convert the string to a "Number value"?

[June7]

But there is an empty string if the condition is not met. Can't Sum empty string. Try this expression: Jan '11: IIf([Month]="January, 2011",1,Null). Can Sum with Nulls. Actually, I should have suggested Max. Show the complete SQL statement.

[taimysho0]

ahh it works!!! thanks Jun, you're the best. Heres my sql if interested:

SELECT qryHHFRequestsSKYTEL_ABS.[Channel ID], Sum(IIf([Month]="January, 2011",Val("1"),Null)) AS [Jan '11], Sum(IIf([Month]="February, 2011",Val("1"),Null)) AS [Feb '11], Sum(IIf([Month]="March, 2011",Val("1"),Null)) AS [Mar '11], Sum(IIf([Month]="April, 2011",Val("1"),Null)) AS [Apr '11], Sum(IIf([Month]="May, 2011",Val("1"),Null)) AS [May '11], Sum(IIf([Month]="June",Val("1"),Null)) AS [Jun '11], Sum(IIf([Month]="July, 2011",Val("1"),Null)) AS [Jul '11], Sum(IIf([Month]="August, 2011",Val("1"),Null)) AS [Aug '11], Sum(IIf([Month]="September, 2011",Val("1"),Null)) AS [Sep '11], Sum(IIf([Month]="October, 2011",Val("1"),Null)) AS [Oct '11], Sum(IIf([Month]="November, 2011",Val("1"),Null)) AS [Nov '11], Sum(IIf([Month]="December, 2011",Val("1"),Null)) AS [Dec '11], Sum(IIf([Month]="January, 2012",Val("1"),Null)) AS [Jan '12]
FROM qryHHFRequestsSKYTEL_ABS
GROUP BY qryHHFRequestsSKYTEL_ABS.[Channel ID]
ORDER BY qryHHFRequestsSKYTEL_ABS.[Channel ID];

[June7]

Use of Val function is unnecessary processing in the function, don't need it.