query - merging lines

[dastr]

Hallo all,
I have the following table:

Name Score 1 Score 2
Anna (blank) 10
Anna 8 (blank)
Inna 7 8
Ivan 9 2

For Annam, I have the score in two lines and blank fields, how may I get the above in one line, thus for Anna
Name Score 1 Score 2
Anna 8 10

Thanks in advance

 

[rammudali]

hi

you may use grouping. in this case name

 

[dastr]

hi there,

may I ask you to give me a quick example? what would be the sql?

Thx

 

[bob fitz]

May be this is what you want:

SELECT [B][COLOR=#ff0000]tblScores[/COLOR][/B].Name, Sum([COLOR=red][B]tblScores[/B][/COLOR].[Score 1]) AS [SumOfScore 1], Sum([B][COLOR=#ff0000]tblScores[/COLOR][/B].[Score 2]) AS [SumOfScore 2]
FROM [B][COLOR=#ff0000]tblScores[/COLOR][/B]
GROUP BY [B][COLOR=#ff0000]tblScores[/COLOR][/B].Name;

Change tblScores to the name of your table.
BTW "Name" is a reservered work and should not be used as the name of a field.

 

[dastr]

Hi there,

Thanks for the response, however I cannot get the sql right.

Here teh dbse - Query - Merging Lines

Could you have a look?

Thx

 

[pr2-eugin]

Why do you have a Record in Table D as 'Name', 'S1', 'S2'?? The column name is on top. I deleted the record it works fine..

As suggested by bob fitz, please change the column name to something like 'CustomerName' or 'AgentName' or 'StudentName'