To Sharkbyte and all: Calculate a total values in group level

[Ally]

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Sharkbyte]

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Ally]

Sharkbyte,

As you sugguested, I was trying to add two queries qryPOTotal and
qryCapTotal to the TABLE1 to build the record source for the report using
wizard. However, after selecting the fields that I would like to print on the
report, a message window came out saying that: "You have chosen fields from
record sources which the wizard can't connect. You may have chose fields from
a table and from a query based on that table. If so, try choosing fields from
only the table or only the query." I don't know how I should proceed now..
The TABLE1 is a big table that I got from appending the result of one query
to the result of another query so that I can print out all information on the
report that shows the different Capital information (CapAmoutn, vendor,
capital requistion description, etc.) related to the corresponding project,
under each CaReq#, there might have multiple POs (PO#, POAmt, PODate, etc)
related to that CapReq#; Under each PO#, there might have different Invoice
information. So the TABLE1 created is a table that may have repetitive field
information in different rows. I guess I am not sure how to add references to
the query and create the relationship here between the TABLE1 and the two
queries I mentioned to calculate the sum of POAmt and CapAmt. Would you
please give me more advices? I really appreciate your help!

Ally

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte




:

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Sharkbyte]

Ally:

I was able to create exactly what you wanted. However, your problem may be
coming from your method of grouping.

Does your report only contain Project#, Cap#, and PO# from Table1, or is
there other information you are bringing in?

If it is just these 3 fields, then use your Record Source property, for the
form, and add Totals (the funny looking E), with all 3 set to Group By. When
I tested this, all the sum fields only totaled displayed values.

If you are adding other fields, I'm not exactly sure how to proceed. I
suppose the first thing I would try would be to create a SQL statement - for
your total field - using the Cap# field, from the report, as a criteria (or
PO#, as needed). Maybe someone else can offer more.

Good luck.

Sharkbyte

Sharkbyte,

As you sugguested, I was trying to add two queries qryPOTotal and
qryCapTotal to the TABLE1 to build the record source for the report using
wizard. However, after selecting the fields that I would like to print on the
report, a message window came out saying that: "You have chosen fields from
record sources which the wizard can't connect. You may have chose fields from
a table and from a query based on that table. If so, try choosing fields from
only the table or only the query." I don't know how I should proceed now..
The TABLE1 is a big table that I got from appending the result of one query
to the result of another query so that I can print out all information on the
report that shows the different Capital information (CapAmoutn, vendor,
capital requistion description, etc.) related to the corresponding project,
under each CaReq#, there might have multiple POs (PO#, POAmt, PODate, etc)
related to that CapReq#; Under each PO#, there might have different Invoice
information. So the TABLE1 created is a table that may have repetitive field
information in different rows. I guess I am not sure how to add references to
the query and create the relationship here between the TABLE1 and the two
queries I mentioned to calculate the sum of POAmt and CapAmt. Would you
please give me more advices? I really appreciate your help!

Ally

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

:

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte




:

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Ally]

Sharkbyte,

The report is grouped first by Project#, then by CapReq#, and by PO# and
Invoice#.

You mentioned to use Record Source Property. I would like to try it myself
using this way. but I guess I am not sure what exactly you mean. How did you
test it?Did you tested in a form? How did you add Totals to these fields in a
form? Does report work the same way as form also? Please give me more hints.

Thanks,

Ally

Ally:

I was able to create exactly what you wanted. However, your problem may be
coming from your method of grouping.

Does your report only contain Project#, Cap#, and PO# from Table1, or is
there other information you are bringing in?

If it is just these 3 fields, then use your Record Source property, for the
form, and add Totals (the funny looking E), with all 3 set to Group By. When
I tested this, all the sum fields only totaled displayed values.

If you are adding other fields, I'm not exactly sure how to proceed. I
suppose the first thing I would try would be to create a SQL statement - for
your total field - using the Cap# field, from the report, as a criteria (or
PO#, as needed). Maybe someone else can offer more.

Good luck.

Sharkbyte

Sharkbyte,

As you sugguested, I was trying to add two queries qryPOTotal and
qryCapTotal to the TABLE1 to build the record source for the report using
wizard. However, after selecting the fields that I would like to print on the
report, a message window came out saying that: "You have chosen fields from
record sources which the wizard can't connect. You may have chose fields from
a table and from a query based on that table. If so, try choosing fields from
only the table or only the query." I don't know how I should proceed now..
The TABLE1 is a big table that I got from appending the result of one query
to the result of another query so that I can print out all information on the
report that shows the different Capital information (CapAmoutn, vendor,
capital requistion description, etc.) related to the corresponding project,
under each CaReq#, there might have multiple POs (PO#, POAmt, PODate, etc)
related to that CapReq#; Under each PO#, there might have different Invoice
information. So the TABLE1 created is a table that may have repetitive field
information in different rows. I guess I am not sure how to add references to
the query and create the relationship here between the TABLE1 and the two
queries I mentioned to calculate the sum of POAmt and CapAmt. Would you
please give me more advices? I really appreciate your help!

Ally

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte



:

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

:

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte




:

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Sharkbyte]

Ally:

First, my test was on a report. Sorry about the slip.

What I did was, using the report wizard, created a report with the first 2
grouping (Project#, Cap#) levels. No need to group the PO#, as it was all
that was left. (This may cause you a small problem, as you are also
including Invoice#, but you should be fine.)

I then added Sum for the remaining field.

After the Wizard created the report, I copied/pasted the Sum control in the
Report Footer section, and changed it from PO# to Cap#. Now I have both my
Sums, but everything shows, on the report, and everything adds up.

So I go to the Report Record Source property, and click on the SQL Builder
(three little dots). This works just like a query, but it should already
have Table1 showing. Select the appropriate fields: Project#, Cap#, PO#,
(Invoice#)...and then set the Totals function. Save, and close the builder.

Now, when I view the report, I see only the unique data, and the totals only
reflect the unique data.

HTH

Sharkbyte

Sharkbyte,

The report is grouped first by Project#, then by CapReq#, and by PO# and
Invoice#.

You mentioned to use Record Source Property. I would like to try it myself
using this way. but I guess I am not sure what exactly you mean. How did you
test it?Did you tested in a form? How did you add Totals to these fields in a
form? Does report work the same way as form also? Please give me more hints.

Thanks,

Ally

Ally:

I was able to create exactly what you wanted. However, your problem may be
coming from your method of grouping.

Does your report only contain Project#, Cap#, and PO# from Table1, or is
there other information you are bringing in?

If it is just these 3 fields, then use your Record Source property, for the
form, and add Totals (the funny looking E), with all 3 set to Group By. When
I tested this, all the sum fields only totaled displayed values.

If you are adding other fields, I'm not exactly sure how to proceed. I
suppose the first thing I would try would be to create a SQL statement - for
your total field - using the Cap# field, from the report, as a criteria (or
PO#, as needed). Maybe someone else can offer more.

Good luck.

Sharkbyte

Sharkbyte,

As you sugguested, I was trying to add two queries qryPOTotal and
qryCapTotal to the TABLE1 to build the record source for the report using
wizard. However, after selecting the fields that I would like to print on the
report, a message window came out saying that: "You have chosen fields from
record sources which the wizard can't connect. You may have chose fields from
a table and from a query based on that table. If so, try choosing fields from
only the table or only the query." I don't know how I should proceed now..
The TABLE1 is a big table that I got from appending the result of one query
to the result of another query so that I can print out all information on the
report that shows the different Capital information (CapAmoutn, vendor,
capital requistion description, etc.) related to the corresponding project,
under each CaReq#, there might have multiple POs (PO#, POAmt, PODate, etc)
related to that CapReq#; Under each PO#, there might have different Invoice
information. So the TABLE1 created is a table that may have repetitive field
information in different rows. I guess I am not sure how to add references to
the query and create the relationship here between the TABLE1 and the two
queries I mentioned to calculate the sum of POAmt and CapAmt. Would you
please give me more advices? I really appreciate your help!

Ally
:

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte



:

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

:

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte




:

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Ally]

Sharkbytes,

I did the similar things as you described by steps: 1) I create a report
based on TABLE1 using wizard, and only grouped by Project# and Cap#. add Sum
for POAmt, InvAmt fields in the wizard. After the wizard created the report,
I copied the sum conrol of POAmt in the report footer and pasted it as the
sum control of CapAmt in the report footer. When clicking view for the print
view of the report, I see repetitive data printed out for the same Cap
information (Cap#, Vendor, and Project Description) for multiple PO#s and the
totals don't reflect the unique data, instead, it adds up everylines it shows
up.

2) So I go to the Report Record Source Property by clicking three dots.
(Yes, it already have TABLE1 showing). I did two tests here: A) I only select
Project#, Cap#, PO#, Invoice#) and set the Totals function, Save and close
the builder. When I view the report, the screen prompts me for Entering
Parameter Value for all the other fields (CapAmt, Project Description, POAmt,
PODate, InvAmt, InvDate) that I did not select in the Report Record Source
property. B) So I started over again by clicking on three dots again, and
this time electing all of the fields in TABLE1 and set the TOTAL function,
save and close the builder. When I view the report, the screen does not
prompt any thing to ask me entering parameter value. However, I see the
report is still not include only the unique data, and the totals DO NOT
reflect the unique data. I am not sure which steps I did wrong? Do you have
any idea of why I got the totally different outcomes that you did? Thanks a
ton!

Ally

Ally:

First, my test was on a report. Sorry about the slip.

What I did was, using the report wizard, created a report with the first 2
grouping (Project#, Cap#) levels. No need to group the PO#, as it was all
that was left. (This may cause you a small problem, as you are also
including Invoice#, but you should be fine.)

I then added Sum for the remaining field.

After the Wizard created the report, I copied/pasted the Sum control in the
Report Footer section, and changed it from PO# to Cap#. Now I have both my
Sums, but everything shows, on the report, and everything adds up.

So I go to the Report Record Source property, and click on the SQL Builder
(three little dots). This works just like a query, but it should already
have Table1 showing. Select the appropriate fields: Project#, Cap#, PO#,
(Invoice#)...and then set the Totals function. Save, and close the builder.

Now, when I view the report, I see only the unique data, and the totals only
reflect the unique data.

HTH

Sharkbyte

Sharkbyte,

The report is grouped first by Project#, then by CapReq#, and by PO# and
Invoice#.

You mentioned to use Record Source Property. I would like to try it myself
using this way. but I guess I am not sure what exactly you mean. How did you
test it?Did you tested in a form? How did you add Totals to these fields in a
form? Does report work the same way as form also? Please give me more hints.

Thanks,

Ally

Ally:

I was able to create exactly what you wanted. However, your problem may be
coming from your method of grouping.

Does your report only contain Project#, Cap#, and PO# from Table1, or is
there other information you are bringing in?

If it is just these 3 fields, then use your Record Source property, for the
form, and add Totals (the funny looking E), with all 3 set to Group By. When
I tested this, all the sum fields only totaled displayed values.

If you are adding other fields, I'm not exactly sure how to proceed. I
suppose the first thing I would try would be to create a SQL statement - for
your total field - using the Cap# field, from the report, as a criteria (or
PO#, as needed). Maybe someone else can offer more.

Good luck.

Sharkbyte



:

Sharkbyte,

As you sugguested, I was trying to add two queries qryPOTotal and
qryCapTotal to the TABLE1 to build the record source for the report using
wizard. However, after selecting the fields that I would like to print on the
report, a message window came out saying that: "You have chosen fields from
record sources which the wizard can't connect. You may have chose fields from
a table and from a query based on that table. If so, try choosing fields from
only the table or only the query." I don't know how I should proceed now..
The TABLE1 is a big table that I got from appending the result of one query
to the result of another query so that I can print out all information on the
report that shows the different Capital information (CapAmoutn, vendor,
capital requistion description, etc.) related to the corresponding project,
under each CaReq#, there might have multiple POs (PO#, POAmt, PODate, etc)
related to that CapReq#; Under each PO#, there might have different Invoice
information. So the TABLE1 created is a table that may have repetitive field
information in different rows. I guess I am not sure how to add references to
the query and create the relationship here between the TABLE1 and the two
queries I mentioned to calculate the sum of POAmt and CapAmt. Would you
please give me more advices? I really appreciate your help!

Ally
:

When building your Record Source, for the report, add references to the
queries, and create relationships. This should allow you to add the queries
to your Field List, and then add the fields to your report.

Sharkbyte



:

Sharkbyte,

After trying serveral times with your suggestion, I think it works when I
try this in the query:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);

I did the similar query for CapAmt as well. I got the right numbers of total
amount of POAmt and CapAmt when I run the above queries.

The thing is that I need these numbers printed on the report for Project
subtotal on Project footer and grand total on report footer. I put this
expression: "=(the above query)" in the control source of the text control on
the project footer and report footer. However, no number except "#Name?" is
printed out on the place that I was expecting to see the sum. Would anyone
please tell me why it fails to print the right sum number on the report
although the the report get data from the TABLE and although it works in the
query?

Thanks a lot!

Ally

:

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte




:

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!