Help with Select query to get single line instead of multiple lines

[carrie09]

Hi,

I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:

(refer attachment)

Is there any way to write this query so that I can get output as below:

Col1 Col2 Col3
10 20 10


Please advise.

 

[FoFa]

I would see if a crosstab query would meet your needs (use help).

 

[carrie09]

Thanks for ur response. However crosstab query won't work in this case. Can you suggest something else?

 

[Guus2005]

Use a group by clause. Click the sigma icon.

 

[carrie09]

Thanks Guus, I tried it but still no go. Just to make sure, i'm pasting my query below for reference. Maybe i'm doing something wrong:

SELECT [Table1].Col1, [Table1].Col2, [Table1].Col3, [Table1].CustNum
FROM [Table1]
GROUP BY [Table1].Col1, [Table1].Col2, [Table1].Col3, [Table1].CustNum
HAVING (([Table1].CustNum)=Forms!LOP!CustNum);

 

[Guus2005]

SELECT sum([Table1].Col1), sum([Table1].Col2), sum([Table1].Col3), [Table1].CustNum
FROM [Table1]
GROUP BY [Table1].CustNum
HAVING (([Table1].CustNum)=Forms!LOP!CustNum);

This should do the trick.

 

[carrie09]

Thanks Guus...
It does work for the numeric fields however I have some text fields as well so this expression won't work for that. Any other ideas? Maybe some way using the If statement...Not sure..

 

[Guus2005]

Ok. First convert them to an integer (or long or double or ...)

SELECT sum(cint(nz([Table1].Col1))), sum(cint(nz([Table1].Col2))), sum(cint(nz([Table1].Col3))), [Table1].CustNum
FROM [Table1]
GROUP BY [Table1].CustNum
HAVING (([Table1].CustNum)=Forms!LOP!CustNum);