Reports Grouping

[Cass]

I have a report that i'm creating that groups by Company. It looks
something like this:

Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 14

MA
58 6
59 7
60 8

Subtotals: 21

The groups are already filtered in a Query. Subtotals adds the total
shipments for each group. What we're trying to do is display the
totals before the query filtering the groups was applied. So I created
a separate query displaying the sums for each group & linked them to
my main query & we have something like this now where
99=SumofHMShipments:

Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99

MA
58 6
59 7
60 8

Subtotals: 99

The reason 99 is displayed twice is because when I add the sums for HM
its also displayed under MA. I have separate sum fields that I need to
be displayed under MA. Can anyone offer me help on how to do this?

I thought maybe if I could figure out how to filter the Grouping by
Company displaying only HM & place the fields for its totals in only
its footer section, then I could do another group & filter by MA &
have separate footer sections for it to put the totals in.

The end result should be:

Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99(field from Query of SumofHMShipments)

MA
58 6
59 7
60 8

Subtotals: 88(Field from Query of SumofMAShipments)


I hope this all makes sense I tried to be as detailed as possible!
Please help!

 

[Duane Hookom]

You stated "So I created a separate query displaying the sums for each group
& linked them to my main query" but didn't provide any SQL views. This would
really help? Did you join your separate query to your main query on group?

 

[Duane Hookom]

Why do you have two SumOf...Shipments queries? I would expect this to be a
single group by/totals query that groups by the field containing the HM or MA
field and resulting in two records like:

HMMA NumOfShipments
===== ===========
HM 99
MA 88
--
Duane Hookom
Microsoft Access MVP

Well I don't really work in SQL & I simplified my data on my question. What I
mean is on my final query, I joined my Shipments & group data table with a
separate sums table. This allowed for all of my information to be displayed
in one query so that I could import it into the Report. So it works kind of
like:

Query with Data:
Group Shipments
0 2
1 3
2 4
3 5
58 6
59 7
60 8

Sums Query:
99(field from Query of SumofHMShipments)
88(Field from Query of SumofMAShipments)

Final Query used for report:
Group Shipments SumsofHMShipments SumsofMAShipments
0 2 99
88
1 3 99
88
2 4 99
88
3 5 99
88
58 6 99 88
59 7 99 88
60 8 99 88

This query resulted in the following being displayed on the report:
Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99

MA
58 6
59 7
60 8

Subtotals: 99

But my issue is not in the Query its in the report. All the data I need is in
the query & working fine. I just need help formatting my report to display it
correctly like this:

Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99(field from Query of SumofHMShipments)

MA
58 6
59 7
60 8

Subtotals: 88(Field from Query of SumofMAShipments)

You stated "So I created a separate query displaying the sums for each group
& linked them to my main query" but didn't provide any SQL views. This would
really help? Did you join your separate query to your main query on group?

I have a report that i'm creating that groups by Company. It looks
something like this:

[quoted text clipped - 70 lines]

I hope this all makes sense I tried to be as detailed as possible!
Please help!

 

[Cass]

Why do you have two SumOf...Shipments queries? I would expect this to be a
single group by/totals query that groups by the field containing the HM or MA
field and resulting in two records like:

HMMA      NumOfShipments
=====   ===========
HM           99
MA           88
--
Duane Hookom
Microsoft Access MVP

Well I don't really work in SQL & I simplified my data on my question. What I
mean is on my final query, I joined my Shipments & group data table with a
separate sums table.  This allowed for all of my information to be displayed
in one query so that I could import it into the Report. So it works kind of
like:

Query with Data:
Group          Shipments
 0                  2
 1                  3
 2                  4
 3                  5
 58                6
 59                7
 60                8

Sums Query:
99(field from Query of SumofHMShipments)
88(Field from Query of SumofMAShipments)

Final Query used for report:
Group          Shipments    SumsofHMShipments      SumsofMAShipments
 0                  2                    99
88
 1                  3                    99
88
 2                  4                    99
88
 3                  5                    99
88
 58                6                    99                                 88
 59                7                    99                                 88
 60                8                    99                                 88

This query resulted in the following being displayed on the report:
Group          Shipments

HM
 0                  2
 1                  3
 2                  4
 3                  5

  Subtotals:    99

MA
 58                6
 59                7
 60                8

  Subtotals:    99

But my issue is not in the Query its in the report. All the data I needis in
the query & working fine. I just need help formatting my report to display it
correctly like this:

Group          Shipments

HM
 0                  2
 1                  3
 2                  4
 3                  5

  Subtotals:    99(field from Query of SumofHMShipments)

MA
 58                6
 59                7
 60                8

  Subtotals:    88(Field from Query of SumofMAShipments)

You stated "So I created a separate query displaying the sums for eachgroup
& linked them to my main query" but didn't provide any SQL views. Thiswould
really help? Did you join your separate query to your main query on group?
I have a report that i'm creating that groups by Company. It looks
something like this:
[quoted text clipped - 70 lines]
I hope this all makes sense I tried to be as detailed as possible!
Please help!- Hide quoted text -

- Show quoted text -

You've lost me... there is only one query that calculates the
sums(Sums Query). I've put that in the "Final Query" & Joined it with
the "Query with Data" to make the fields available for the report.
The only thing that displays in the report when I use one of the Sums
fields is "99" or "88" only once as I need it. But I need it to be
under it's specific group. Is there a reason why we keep going back to
my queries? Do you believe i could've created the data wrong?
Everything displays correctly in the Report. I just need a way to
separate the grouping of the company to use its own totals.

 

[Duane Hookom]

I think you should find the SQL views of your report record sources and paste
them into a reply.

--
Duane Hookom
Microsoft Access MVP

Why do you have two SumOf...Shipments queries? I would expect this to be a
single group by/totals query that groups by the field containing the HM or MA
field and resulting in two records like:

HMMA NumOfShipments
===== ===========
HM 99
MA 88
--
Duane Hookom
Microsoft Access MVP

Well I don't really work in SQL & I simplified my data on my question. What I
mean is on my final query, I joined my Shipments & group data table with a
separate sums table. This allowed for all of my information to be displayed
in one query so that I could import it into the Report. So it works kind of
like:

Query with Data:
Group Shipments
0 2
1 3
2 4
3 5
58 6
59 7
60 8

Sums Query:
99(field from Query of SumofHMShipments)
88(Field from Query of SumofMAShipments)

Final Query used for report:
Group Shipments SumsofHMShipments SumsofMAShipments
0 2 99
88
1 3 99
88
2 4 99
88
3 5 99
88
58 6 99 88
59 7 99 88
60 8 99 88

This query resulted in the following being displayed on the report:
Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99

MA
58 6
59 7
60 8

Subtotals: 99

But my issue is not in the Query its in the report. All the data I need is in
the query & working fine. I just need help formatting my report to display it
correctly like this:

Group Shipments

HM
0 2
1 3
2 4
3 5

Subtotals: 99(field from Query of SumofHMShipments)

MA
58 6
59 7
60 8

Subtotals: 88(Field from Query of SumofMAShipments)

Duane Hookom wrote:
You stated "So I created a separate query displaying the sums for each group
& linked them to my main query" but didn't provide any SQL views. This would
really help? Did you join your separate query to your main query on group?

I have a report that i'm creating that groups by Company. It looks
something like this:
[quoted text clipped - 70 lines]
I hope this all makes sense I tried to be as detailed as possible!
Please help!- Hide quoted text -

- Show quoted text -

You've lost me... there is only one query that calculates the
sums(Sums Query). I've put that in the "Final Query" & Joined it with
the "Query with Data" to make the fields available for the report.
The only thing that displays in the report when I use one of the Sums
fields is "99" or "88" only once as I need it. But I need it to be
under it's specific group. Is there a reason why we keep going back to
my queries? Do you believe i could've created the data wrong?
Everything displays correctly in the Report. I just need a way to
separate the grouping of the company to use its own totals.