A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Suppress printing group footer using Calculated field(s)



 
 
Thread Tools Display Modes
  #1  
Old May 17th, 2010, 07:59 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default Suppress printing group footer using Calculated field(s)

I think I already know the answer but, will ask just to be absolutely sure.

I have a report. 2 tables PART and TRANSACTIONS and need to do some
comparisons to output to a report differences that need to be fixed in the
PART table.

From The TRANS table I have QTY ( quantity of parts purchased), COSTED_QTY
(QTY of parts that have been used on a work order) MATERIAL_$ (total purchase
price of QTY). The calculated fields in the Detail part of the report are
(Field 1) = MATERIAL_$ / QTY (Giving me the cost per unit of the parts),
(Field 2) = QTY - COSTED_QTY (Giving me the QTY on hand in inventory
according to the TRANS table). (Field 3) = (MATERIAL_$ / QTY) * (QTY -
COSTED_QTY) the combination of field 1 and field 2 to give me the calculated
inventory $ value left in inventory according to TRANS table. These are in
the detail of the report. I have changed the Visible value to 'NO' so that
the detail does not print.

Now there maybe more than 1 TRANS record for each part so. I created a group
footer for each part. In that I SUM up each of the 3 fields for a part and
they come out great.

=Sum([QTY]-[COSTED_QTY])

=Sum(([ACT_MATERIAL_COST]/[QTY])*([QTY]-[COSTED_QTY]))

=(Sum(([ACT_MATERIAL_COST]/[QTY])*([QTY]-[COSTED_QTY]))/Sum([QTY]-[COSTED_QTY]))

I have checked several of the output lines and all calculations look good.

Now comes in the PART table with 2 fields QTY_ON_HAND and UNIT_$ into the
group footer.

I want to compare the PART table QTY_ON_HAND to the field
=Sum([QTY]-[COSTED_QTY]) and if they are different print the info and then
check the UNIT_$ against each other and if more than a 10% difference print
the info. otherwise skip this record.

1) is there a way to compare calculated fields, Maybe in an event procedure
using VB. I have a pretty good back ground in VB but not in Access.

Thanks
--
MNJoe
  #2  
Old May 17th, 2010, 08:37 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Suppress printing group footer using Calculated field(s)

MNJoe wrote:

I think I already know the answer but, will ask just to be absolutely sure.

I have a report. 2 tables PART and TRANSACTIONS and need to do some
comparisons to output to a report differences that need to be fixed in the
PART table.

From The TRANS table I have QTY ( quantity of parts purchased), COSTED_QTY
(QTY of parts that have been used on a work order) MATERIAL_$ (total purchase
price of QTY). The calculated fields in the Detail part of the report are
(Field 1) = MATERIAL_$ / QTY (Giving me the cost per unit of the parts),
(Field 2) = QTY - COSTED_QTY (Giving me the QTY on hand in inventory
according to the TRANS table). (Field 3) = (MATERIAL_$ / QTY) * (QTY -
COSTED_QTY) the combination of field 1 and field 2 to give me the calculated
inventory $ value left in inventory according to TRANS table. These are in
the detail of the report. I have changed the Visible value to 'NO' so that
the detail does not print.

Now there maybe more than 1 TRANS record for each part so. I created a group
footer for each part. In that I SUM up each of the 3 fields for a part and
they come out great.

=Sum([QTY]-[COSTED_QTY])

=Sum(([ACT_MATERIAL_COST]/[QTY])*([QTY]-[COSTED_QTY]))

=(Sum(([ACT_MATERIAL_COST]/[QTY])*([QTY]-[COSTED_QTY]))/Sum([QTY]-[COSTED_QTY]))

I have checked several of the output lines and all calculations look good.

Now comes in the PART table with 2 fields QTY_ON_HAND and UNIT_$ into the
group footer.

I want to compare the PART table QTY_ON_HAND to the field
=Sum([QTY]-[COSTED_QTY]) and if they are different print the info and then
check the UNIT_$ against each other and if more than a 10% difference print
the info. otherwise skip this record.

1) is there a way to compare calculated fields, Maybe in an event procedure
using VB. I have a pretty good back ground in VB but not in Access.



Yes, in reports (unlike forms) you can use the values in
calculated controls in the section's Format event procedure
to make controls or the section invisible. And, if
appropriate the control and/or section will then shrink to
reclaim the spece used by the now invisible control/section.

The code would be pretty much as you'd expect using Dlookup
(or whatever) to retrieve the value from the Part table

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.