Prev: Adding control to report
Next: Many to Many Relationship Report
From: MNJoe on 17 May 2010 14:59
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
From: Marshall Barton on 17 May 2010 15:37
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]
 | 
Pages: 1
Prev: Adding control to report
Next: Many to Many Relationship Report