How to have total for each category in access report? (1 Viewer)

luzz · Sep 20, 2017

Hello guys, I have a access report whereby it contain KGS,NettWeight for each fabrication, i would like to have a total amount for each category in my access report, how can i do that?

For instance if there is two difference fabrication, KGS,Nettweight, my current total will only appear at the second fabrication which is the bottom part.
Whereas i want it to appear at the top and bottom if the PO contain more than one fabrication

Minty · Sep 20, 2017

You will have to group your report by PO and put the total in the group footer. More information here https://support.office.com/en-us/article/Summing-in-reports-AD4E310D-64E9-4699-8D33-B8AE9639FBF4

arnelgp · Sep 20, 2017

firstly, you need to create a Total Query
that will group the PO.

on another column add an expression that will count
[KGS] field and [Net Weight]:

Count([KGS] & [Net Weight])

another column that will Sum the field
you need to Total:

Sum([FieldName])

on my sample (qryNmberOfDifferentFabInPO) i sum the [Net Weight] field.

design your report to add group on PO.

you need code on the detail format
to show:

1. if there are only two different
fabrication on same PO, put
the total (or subTotal?) next to the last record.

2. if 1 or more than 2, put the
total next to the first and last records.

see Detail_Format of sample report (Table1).

*******************************************************
(Note: the Detail_Format will only fire on Print Preview
and not on any other view of the report.
********************************************************

luzz · Sep 21, 2017

arnelgp said:
firstly, you need to create a Total Query
that will group the PO.

on another column add an expression that will count
[KGS] field and [Net Weight]:

Count([KGS] & [Net Weight])

another column that will Sum the field
you need to Total:

Sum([FieldName])

on my sample (qryNmberOfDifferentFabInPO) i sum the [Net Weight] field.

design your report to add group on PO.

you need code on the detail format
to show:

1. if there are only two different
fabrication on same PO, put
the total (or subTotal?) next to the last record.

2. if 1 or more than 2, put the
total next to the first and last records.

see Detail_Format of sample report (Table1).

*******************************************************
(Note: the Detail_Format will only fire on Print Preview
and not on any other view of the report.
********************************************************

Thankyou. I tried and could not get it. It come out error.

arnelgp · Sep 21, 2017

Can u post a db with similar report, table and query

luzz · Sep 21, 2017

arnelgp said:
Can u post a db with similar report, table and query

I would love to. But i cant seem to be able to attached and upload my file. It shows" security token"

arnelgp · Sep 21, 2017

put it in a ZIP file.

luzz · Sep 21, 2017

arnelgp said:
put it in a ZIP file.

OMG thanks! I have attached the file.

Minty · Sep 21, 2017

Have a look at the query and the report to see where you went wrong.

arnelgp · Sep 21, 2017

see if this is what you need.

luzz · Sep 21, 2017

arnelgp said:
see if this is what you need.

Hmm, I want to have a total at the end of every PO

luzz · Sep 25, 2017

arnelgp said:
see if this is what you need.

I attached a image which is my current report. The number in bold is my total. But thats not what i want. I want it the subtotal to be total up after the last color

isladogs · Sep 25, 2017

If you mean you want ONE overall total, place the total row in the report footer

EDIT - just spotted that Minty said that in post 2

Minty · Sep 25, 2017

I'm pretty certain my zipped file has this - but have rather lost the will to keep helping with the lack of response / darting off in different directions.

luzz · Sep 26, 2017

ridders said:
If you mean you want ONE overall total, place the total row in the report footer

EDIT - just spotted that Minty said that in post 2

what minty said in post 2, i have done it previously and is not what i want, that's why i did not follow. What i want is having total for each fabrication instead of a total for all the different fabrication.

Minty · Sep 26, 2017

Try this then - it has a total per PO.

If you had looked at what I had done it should have been pretty obvious what you needed to change in fairness.

How to have total for each category in access report? (1 Viewer)

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Attachments

AWF VIP

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Attachments

MVP / VIP

AWF VIP

Registered User.

AWF VIP

Attachments

Similar threads

Users who are viewing this thread