combining records(rows)

[pradeep.sands]

I am working on a task and I am stuck at a point where I need to combine two or more records(rows).


I have a query table designed with SQL coding and derived/linked from several other tables...it looks like this..

Attachment 12686

Now I need to combine those rows, so that my query table looks like this:
Attachment 12685

Can some one suggest me what I can do?

[pradeep.sands]

Forgot to mention, I am working with SQL design in Access2010...and Not using VBA !!!
Thanks

[ssanfu]

If a check box is FALSE, the value is zero. If a check box is TRUE, the value is "Not Zero". Therefore, a TRUE check box can be -1, 100, 54.2, -21.87, ...

In your case, you can the sum of the Machine1 field will be -1 (TRUE). So you can use a totals query.

Here is the query:

SELECT Sands.Number, Sands.desc, Sands.Parameter1, Sands.Parameter2, Sands.Parameter3, Sands.Parameter4, Sum(Sands.Machine1) AS SumOfMachine1, Sum(Sands.Machine2) AS SumOfMachine2, Sum(Sands.Machine3) AS SumOfMachine3, Sum(Sands.Machine4) AS SumOfMachine4
FROM Sands
GROUP BY Sands.Number, Sands.description, Sands.Parameter1, Sands.Parameter2, Sands.Parameter3, Sands.Parameter4;

****Change the table name to your name.

Also, "Number" is a reserved word in Access and shouldn't be used as object names.
(Here is a list of reserved words http://allenbrowne.com/AppIssueBadWord.html )

[pradeep.sands]

Hi ssanfu,
Firstly thanks for your reply.
Actualy I already have designed a query with SQL. The part you told me to do is actually my sub-query part (I should make it something like a sub-select). So when I do it, it says something like my sub-select has more than one parameter to return and it's not possible.

[ssanfu]

The query is a totals query. The Totals row (in design view) for the field "Desc" changed from "Group By" to "Expression", which caused the error.

See attached zip (A2000 format)