Export Reports Subtotals

[D]

Hi

Is there any way to export report's subtotals to Excel?

Thanks,

Dan

 

[Marshall Barton]

Is there any way to export report's subtotals to Excel?

Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[D]

Thanks Marsh!

How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));

 

[Marshall Barton]

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.
--
Marsh
MVP [MS Access]

How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));

Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[D]

Hi Marshall:

Yes, thanks! I did taht; but I would like to look like below - 2 rows and
subtotal and so forth; but I think is not possible??

Key1 OrgUnit Ccy Acct SubAcct Prod Cust journal_id journal_seq JnlCls TransType JnlDesc amount_book amount_tran
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 805 TRSF FX PNL RE INCORR COBSCO NOV-DEC BOOKING ($2,650,397.97) $0.00
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 803 TRSF PNL PER HE PORCESS $140,672.00 $0.00
83580-CAD-2801100-006-0802-09902 Total ($2,509,725.97) $0.00

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.
--
Marsh
MVP [MS Access]

How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));

D wrote:
Is there any way to export report's subtotals to Excel?

Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[Marshall Barton]

Yes, thanks! I did taht; but I would like to look like below - 2 rows and
subtotal and so forth; but I think is not possible??

Key1 OrgUnit Ccy Acct SubAcct Prod Cust journal_id journal_seq JnlCls TransType JnlDesc amount_book amount_tran
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 805 TRSF FX PNL RE INCORR COBSCO NOV-DEC BOOKING ($2,650,397.97) $0.00
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 803 TRSF PNL PER HE PORCESS $140,672.00 $0.00
83580-CAD-2801100-006-0802-09902 Total ($2,509,725.97) $0.00

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.

How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));


D wrote:
Is there any way to export report's subtotals to Excel?


:
Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[D]

Hi Marshall:

Is it possible, please?

Thanks,

Dan

Yes, thanks! I did taht; but I would like to look like below - 2 rows and
subtotal and so forth; but I think is not possible??

Key1 OrgUnit Ccy Acct SubAcct Prod Cust journal_id journal_seq JnlCls TransType JnlDesc amount_book amount_tran
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 805 TRSF FX PNL RE INCORR COBSCO NOV-DEC BOOKING ($2,650,397.97) $0.00
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 803 TRSF PNL PER HE PORCESS $140,672.00 $0.00
83580-CAD-2801100-006-0802-09902 Total ($2,509,725.97) $0.00

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.


D wrote:
How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));


D wrote:
Is there any way to export report's subtotals to Excel?


:
Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[Marshall Barton]

You should have the two rows, so that is no longer the
problem.

Getting the third row with the account total, looks like it
might be the problem now, but I really have no idea what you
mean by "subtotal and so forth."

Most likely, you can get what you want by creating another
query for the account total. THis query would be much the
same as the wone that works, but with fewer field in the
field list.

Then the results of the two queries can be combined by using
UNION ALL in a third query.
--
Marsh
MVP [MS Access]

Yes, thanks! I did taht; but I would like to look like below - 2 rows and
subtotal and so forth; but I think is not possible??

Key1 OrgUnit Ccy Acct SubAcct Prod Cust journal_id journal_seq JnlCls TransType JnlDesc amount_book amount_tran
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 805 TRSF FX PNL RE INCORR COBSCO NOV-DEC BOOKING ($2,650,397.97) $0.00
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 803 TRSF PNL PER HE PORCESS $140,672.00 $0.00
83580-CAD-2801100-006-0802-09902 Total ($2,509,725.97) $0.00

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.

How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));


D wrote:
Is there any way to export report's subtotals to Excel?


:
Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[D]

Thanks Marshall!

Please see below; it not working because the no. of col. is not the same??

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null))

union all

SELECT v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id,
Sum(v1frJnlIdNewFile.amount_book) AS SumOfamount_book
FROM v1frJnlIdNewFile
GROUP BY v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id
HAVING (((v1frJnlIdNewFile.journal_id) Is Not Null));

You should have the two rows, so that is no longer the
problem.

Getting the third row with the account total, looks like it
might be the problem now, but I really have no idea what you
mean by "subtotal and so forth."

Most likely, you can get what you want by creating another
query for the account total. THis query would be much the
same as the wone that works, but with fewer field in the
field list.

Then the results of the two queries can be combined by using
UNION ALL in a third query.
--
Marsh
MVP [MS Access]

Yes, thanks! I did taht; but I would like to look like below - 2 rows and
subtotal and so forth; but I think is not possible??

Key1 OrgUnit Ccy Acct SubAcct Prod Cust journal_id journal_seq JnlCls TransType JnlDesc amount_book amount_tran
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 805 TRSF FX PNL RE INCORR COBSCO NOV-DEC BOOKING ($2,650,397.97) $0.00
83580-CAD-2801100-006-0802-09902 83580 CAD 2801100 6 802 9902 HCOPCB00FC 803 TRSF PNL PER HE PORCESS $140,672.00 $0.00
83580-CAD-2801100-006-0802-09902 Total ($2,509,725.97) $0.00

Using a copy of that query, change it to a Totals type query
(using View menu). Then in the Totals tow of the query
design grid, select Group By for the fields that should come
through as is and select Sum for the fields you want to
total.


D wrote:
How do I do that in the below SQL: thans!

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null));


D wrote:
Is there any way to export report's subtotals to Excel?


:
Exporting a report is not a good idea. Instead, you should
create a query (similar to the report's record source
query?) that calculates the subtotals. Then export the
query to Excel.

 

[Marshall Barton]

Please see below; it not working because the no. of col. is not the same??

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null))

union all

SELECT v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id,
Sum(v1frJnlIdNewFile.amount_book) AS SumOfamount_book
FROM v1frJnlIdNewFile
GROUP BY v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id
HAVING (((v1frJnlIdNewFile.journal_id) Is Not Null));

Change HAVING to WHERE and add Null values for the unused
columns in the second query.

 

[D]

Thank you Marshall!

Dan

Please see below; it not working because the no. of col. is not the same??

SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS
Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4)
AS Prod, Mid([key1],28,5) AS Cust, v1frJnlIdNewFile.journal_id,
v1frJnlIdNewFile.journal_seq, Left([description],8) AS JnlCls,
Mid([description],9,6) AS TransType, Mid([description],16,35) AS JnlDesc,
v1frJnlIdNewFile.amount_book, v1frJnlIdNewFile.amount_tran
FROM v1frJnlIdNewFile
WHERE (((v1frJnlIdNewFile.journal_id) Is Not Null))

union all

SELECT v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id,
Sum(v1frJnlIdNewFile.amount_book) AS SumOfamount_book
FROM v1frJnlIdNewFile
GROUP BY v1frJnlIdNewFile.Key1, v1frJnlIdNewFile.journal_id
HAVING (((v1frJnlIdNewFile.journal_id) Is Not Null));

Change HAVING to WHERE and add Null values for the unused
columns in the second query.