Combining Similar Rows

[Microsoft News]

I am trying to combine the Alles170* rows into one row that shows the totals
for each column.


ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?

 

[Guest]

I would suggest first creating a query to get a rollup title for the like
items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums, group by the
rollup column and sum the rest.

Hope this helps,
Jane

 

[Barry]

Jane,

Thanks, that worked fine, but I suspect the performance probably isn't too
good. I now need to total the rows across and show it on the right like
this:

Expr1 Rent Fees Council Electric Gas
Misc Rates Repairs Supplied TOTALS
Alderm83 £815.00
£815.00
Alles170
£1,384.80 -£235.00 -£86.92 -£380.00 -£193.88 £489.00
Bede9 £786.70
£786.70
Benson £675.00
£675.00
Blythe48 £725.00
£725.00
BlytheR8
-£44.00 £431.00
Bolin134 £584.00 -£6.45 -£50.00 -£107.00
£420.55


I can't get the sum to work correctly across the rows.

SELECT
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
) AS Expr1, Sum([Sub Consol2].Council) AS Council, Sum([Sub
Consol2].Electric) AS Electric, Sum([Sub Consol2].Fees) AS Fees, Sum([Sub
Consol2].Gas) AS Gas, Sum([Sub Consol2].Misc) AS Misc, Sum([Sub
Consol2].Rates) AS Rates, Sum([Sub Consol2].Rent) AS Rent, Sum([Sub
Consol2].Repairs) AS Repairs, Sum([Sub Consol2].Supplied) AS
Supplied,[Council]+[Rent]+[Fees] AS Expr2

FROM [Sub Consol2] INNER JOIN Property ON [Sub Consol2].Property =
Property.Code

GROUP BY
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
), [Council]+[Rent]+[Fees];

Summing only three fields as part of the select (as above) does not give the
right answer.

Suggestions?

Barry.

I would suggest first creating a query to get a rollup title for the like
items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums, group by the
rollup column and sum the rest.

Hope this helps,
Jane

I am trying to combine the Alles170* rows into one row that shows the totals
for each column.


ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?

 

[Guest]

Are you trying to do the row totals in the same query as the account name
rollup? If so, I would not recommend that. I would create a third query to
do it. Notice the column names change when you base the third query on the
second -- SumOfRent, etc. This is why Access gets confused trying to do
everything in one query. Your TOTAL formula in the new query should just be
SumOfRent + SumOfFees + etc. You can also include all the columns from the
second query, so everything is in one place.

Hope that helps,
Jane

Jane,

Thanks, that worked fine, but I suspect the performance probably isn't too
good. I now need to total the rows across and show it on the right like
this:

Expr1 Rent Fees Council Electric Gas
Misc Rates Repairs Supplied TOTALS
Alderm83 £815.00
£815.00
Alles170
£1,384.80 -£235.00 -£86.92 -£380.00 -£193.88 £489.00
Bede9 £786.70
£786.70
Benson £675.00
£675.00
Blythe48 £725.00
£725.00
BlytheR8
-£44.00 £431.00
Bolin134 £584.00 -£6.45 -£50.00 -£107.00
£420.55


I can't get the sum to work correctly across the rows.

SELECT
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
) AS Expr1, Sum([Sub Consol2].Council) AS Council, Sum([Sub
Consol2].Electric) AS Electric, Sum([Sub Consol2].Fees) AS Fees, Sum([Sub
Consol2].Gas) AS Gas, Sum([Sub Consol2].Misc) AS Misc, Sum([Sub
Consol2].Rates) AS Rates, Sum([Sub Consol2].Rent) AS Rent, Sum([Sub
Consol2].Repairs) AS Repairs, Sum([Sub Consol2].Supplied) AS
Supplied,[Council]+[Rent]+[Fees] AS Expr2

FROM [Sub Consol2] INNER JOIN Property ON [Sub Consol2].Property =
Property.Code

GROUP BY
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
), [Council]+[Rent]+[Fees];

Summing only three fields as part of the select (as above) does not give the
right answer.

Suggestions?

Barry.

I would suggest first creating a query to get a rollup title for the like
items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums, group by the
rollup column and sum the rest.

Hope this helps,
Jane

I am trying to combine the Alles170* rows into one row that shows the totals
for each column.


ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?

 

[Barry]

Hi Jane,

I've created a separate query to work on for the totals column but it still
won't total the column. My query at this level is now:

SELECT [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied, Sum([Sub Consol3].[Rent]+[Sub Consol3].[Fees]) AS Expr2
FROM [Sub Consol3]
GROUP BY [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied;

For Expr2 I've also tried:

[Sub Consol3].[Rent]+[Sub Consol3].[Fees] AS Expr2

And:

Sum([Sub Consol3].[Rent])+Sum([Sub Consol3].[Fees]) AS Expr2

All I can get is the correct number in the Totals column if any number
exists in both the Rent and Fees columns; all other columns are blank.

Wasgoinon?

Are you trying to do the row totals in the same query as the account name
rollup? If so, I would not recommend that. I would create a third query to
do it. Notice the column names change when you base the third query on the
second -- SumOfRent, etc. This is why Access gets confused trying to do
everything in one query. Your TOTAL formula in the new query should just be
SumOfRent + SumOfFees + etc. You can also include all the columns from the
second query, so everything is in one place.

Hope that helps,
Jane

Jane,

Thanks, that worked fine, but I suspect the performance probably isn't too
good. I now need to total the rows across and show it on the right like
this:

Expr1 Rent Fees Council Electric Gas
Misc Rates Repairs Supplied TOTALS
Alderm83 £815.00
£815.00
Alles170
£1,384.80 -£235.00 -£86.92 -£380.00 -£193.88 £489.00
Bede9 £786.70
£786.70
Benson £675.00
£675.00
Blythe48 £725.00
£725.00
BlytheR8
-£44.00 £431.00
Bolin134 £584.00 -£6.45 -£50.00 -£107.00
£420.55


I can't get the sum to work correctly across the rows.

SELECT
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
) AS Expr1, Sum([Sub Consol2].Council) AS Council, Sum([Sub
Consol2].Electric) AS Electric, Sum([Sub Consol2].Fees) AS Fees, Sum([Sub
Consol2].Gas) AS Gas, Sum([Sub Consol2].Misc) AS Misc, Sum([Sub
Consol2].Rates) AS Rates, Sum([Sub Consol2].Rent) AS Rent, Sum([Sub
Consol2].Repairs) AS Repairs, Sum([Sub Consol2].Supplied) AS
Supplied,[Council]+[Rent]+[Fees] AS Expr2

FROM [Sub Consol2] INNER JOIN Property ON [Sub Consol2].Property =
Property.Code

GROUP BY
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
), [Council]+[Rent]+[Fees];

Summing only three fields as part of the select (as above) does not give the
right answer.

Suggestions?

Barry.

I would suggest first creating a query to get a rollup title for the like
items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums, group

by
the

rollup column and sum the rest.

Hope this helps,
Jane

:


I am trying to combine the Alles170* rows into one row that shows

the
totals

for each column.


ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?

 

[Guest]

My first guess is that you should take the row sums off this query. I notice
it's still grouping, but you should be down to only unique records now from
the previous query and the grouping/summing rows is unnecessary. Once those
are removed, [Sub Consol3].[Rent]+[Sub Consol3].[Fees] AS Expr2 should work
fine.

If you're still having problems, the system may be getting confused by the
blanks. Try:

Iif([Sub Consol3].[Rent]="",0,[Sub Consol3].[Rent])+Iif([Sub
Consol3].[Fees]="",0,[Sub Consol3].[Fees]) AS Expr2

Also, in my previous reply, I noted that once you do sums in Query 2, and
base query 3 off of query 2, the column names should change to SumOfRent,
SumOfFees, etc. I don't see that in your SQL below. You may wish to make
sure all that is in line as well.

Jane

Hi Jane,

I've created a separate query to work on for the totals column but it still
won't total the column. My query at this level is now:

SELECT [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied, Sum([Sub Consol3].[Rent]+[Sub Consol3].[Fees]) AS Expr2
FROM [Sub Consol3]
GROUP BY [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied;

For Expr2 I've also tried:

[Sub Consol3].[Rent]+[Sub Consol3].[Fees] AS Expr2

And:

Sum([Sub Consol3].[Rent])+Sum([Sub Consol3].[Fees]) AS Expr2

All I can get is the correct number in the Totals column if any number
exists in both the Rent and Fees columns; all other columns are blank.

Wasgoinon?

Are you trying to do the row totals in the same query as the account name
rollup? If so, I would not recommend that. I would create a third query to
do it. Notice the column names change when you base the third query on the
second -- SumOfRent, etc. This is why Access gets confused trying to do
everything in one query. Your TOTAL formula in the new query should just be
SumOfRent + SumOfFees + etc. You can also include all the columns from the
second query, so everything is in one place.

Hope that helps,
Jane

Jane,

Thanks, that worked fine, but I suspect the performance probably isn't too
good. I now need to total the rows across and show it on the right like
this:

Expr1 Rent Fees Council Electric Gas
Misc Rates Repairs Supplied TOTALS
Alderm83 £815.00
£815.00
Alles170
£1,384.80 -£235.00 -£86.92 -£380.00 -£193.88 £489.00
Bede9 £786.70
£786.70
Benson £675.00
£675.00
Blythe48 £725.00
£725.00
BlytheR8
-£44.00 £431.00
Bolin134 £584.00 -£6.45 -£50.00 -£107.00
£420.55


I can't get the sum to work correctly across the rows.

SELECT
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
) AS Expr1, Sum([Sub Consol2].Council) AS Council, Sum([Sub
Consol2].Electric) AS Electric, Sum([Sub Consol2].Fees) AS Fees, Sum([Sub
Consol2].Gas) AS Gas, Sum([Sub Consol2].Misc) AS Misc, Sum([Sub
Consol2].Rates) AS Rates, Sum([Sub Consol2].Rent) AS Rent, Sum([Sub
Consol2].Repairs) AS Repairs, Sum([Sub Consol2].Supplied) AS
Supplied,[Council]+[Rent]+[Fees] AS Expr2

FROM [Sub Consol2] INNER JOIN Property ON [Sub Consol2].Property =
Property.Code

GROUP BY
IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])
), [Council]+[Rent]+[Fees];

Summing only three fields as part of the select (as above) does not give the
right answer.

Suggestions?

Barry.


I would suggest first creating a query to get a rollup title for the like
items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums, group by
the
rollup column and sum the rest.

Hope this helps,
Jane

:


I am trying to combine the Alles170* rows into one row that shows the
totals
for each column.


ColA ColB ColC ColD ColE
ColF
Alles170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE
ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?

 

[Barry]

Jane,

Yes, you're right, the SumOf.... was aliased as Expr1, Council, Electric
etc. in the next level query. So those sums are all calculated OK.

However, you correctly spotted that the blanks/nulls were causing the
problem!

This was the final answer that worked:

Expr2: IIf(IsNull([Sub Consol3].[Rent]),0,[Sub
Consol3].[Rent])+IIf(IsNull([Sub Consol3].[Fees]),0,[Sub Consol3].[Fees]) +
etc.

Thanks Jane. You're a star!

My first guess is that you should take the row sums off this query. I notice
it's still grouping, but you should be down to only unique records now from
the previous query and the grouping/summing rows is unnecessary. Once those
are removed, [Sub Consol3].[Rent]+[Sub Consol3].[Fees] AS Expr2 should work
fine.

If you're still having problems, the system may be getting confused by the
blanks. Try:

Iif([Sub Consol3].[Rent]="",0,[Sub Consol3].[Rent])+Iif([Sub
Consol3].[Fees]="",0,[Sub Consol3].[Fees]) AS Expr2

Also, in my previous reply, I noted that once you do sums in Query 2, and
base query 3 off of query 2, the column names should change to SumOfRent,
SumOfFees, etc. I don't see that in your SQL below. You may wish to make
sure all that is in line as well.

Jane

Hi Jane,

I've created a separate query to work on for the totals column but it still
won't total the column. My query at this level is now:

SELECT [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied, Sum([Sub Consol3].[Rent]+[Sub Consol3].[Fees]) AS Expr2
FROM [Sub Consol3]
GROUP BY [Sub Consol3].Expr1, [Sub Consol3].Council, [Sub Consol3].Electric,
[Sub Consol3].Fees, [Sub Consol3].Gas, [Sub Consol3].Misc, [Sub
Consol3].Rates, [Sub Consol3].Rent, [Sub Consol3].Repairs, [Sub
Consol3].Supplied;

For Expr2 I've also tried:

[Sub Consol3].[Rent]+[Sub Consol3].[Fees] AS Expr2

And:

Sum([Sub Consol3].[Rent])+Sum([Sub Consol3].[Fees]) AS Expr2

All I can get is the correct number in the Totals column if any number
exists in both the Rent and Fees columns; all other columns are blank.

Wasgoinon?

Are you trying to do the row totals in the same query as the account name
rollup? If so, I would not recommend that. I would create a third

query
to

do it. Notice the column names change when you base the third query

on
the

second -- SumOfRent, etc. This is why Access gets confused trying to do
everything in one query. Your TOTAL formula in the new query should

just
be

SumOfRent + SumOfFees + etc. You can also include all the columns

from
the

second query, so everything is in one place.

Hope that helps,
Jane

:

Jane,

Thanks, that worked fine, but I suspect the performance probably

isn't
too

good. I now need to total the rows across and show it on the right like
this:

Expr1 Rent Fees Council Electric Gas
Misc Rates Repairs Supplied TOTALS
Alderm83 £815.00
£815.00
Alles170
£1,384.80 -£235.00 -£86.92 -£380.00 -£193.88 £489.00
Bede9 £786.70
£786.70
Benson £675.00
£675.00
Blythe48 £725.00
£725.00
BlytheR8
-£44.00 £431.00
Bolin134 £584.00 -£6.45 -£50.00 -£107.00
£420.55


I can't get the sum to work correctly across the rows.

SELECT

IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])

) AS Expr1, Sum([Sub Consol2].Council) AS Council, Sum([Sub
Consol2].Electric) AS Electric, Sum([Sub Consol2].Fees) AS Fees, Sum([Sub
Consol2].Gas) AS Gas, Sum([Sub Consol2].Misc) AS Misc, Sum([Sub
Consol2].Rates) AS Rates, Sum([Sub Consol2].Rent) AS Rent, Sum([Sub
Consol2].Repairs) AS Repairs, Sum([Sub Consol2].Supplied) AS
Supplied,[Council]+[Rent]+[Fees] AS Expr2

FROM [Sub Consol2] INNER JOIN Property ON [Sub Consol2].Property =
Property.Code

GROUP BY

IIf([type]=1,Left(Trim([Property]),Len(Trim([Property]))-2),Trim([Property])

), [Council]+[Rent]+[Fees];

Summing only three fields as part of the select (as above) does not

give
the

right answer.

Suggestions?

Barry.


I would suggest first creating a query to get a rollup title for

the
like

items. For example, assuming that all of your items in column A are 8
characters plus the extra stuff, a rollup function could be =Left([column
a],8). Put all of the rest of the columns in the query as well.

Then, create a new query based on the last one, include your new column
instead of column A, along with all the other columns. Do sums,

group
by

the
rollup column and sum the rest.

Hope this helps,
Jane

:


I am trying to combine the Alles170* rows into one row that

shows
the

totals
for each column.


ColA ColB ColC ColD ColE
ColF
les170 -£86.92 -£380.00 -£193.88
Alles170-2 -£58.75
£280.00
Alles170-3 -£58.75
£280.00
Alles170-5 -£58.75
£300.00
Alles170-6 -£58.75
£524.80

I want this:

ColA ColB ColC ColD ColE
ColF
Alles170 -£86.92 -£235.00 -£380.00 -£193.88 £1,384.80


Can anyone help with the SQL?