Using 2 field values from the same field to populate calculated control on report

[ML!]

Hi all,

It seems like this should be easy but my sleep deprived brain isn't operating at full capacity.

See the image for a representation of query results isolating 2 records that make up transaction #174.

I want to be able to use the 2 parameters for the same transaction in a calculation and use that expression as the control source for a report text field.

I want to show the percentage that Time parameter B is of Time Parameter A.

Am I able to accomplish this with the expression builder? If not, does anyone have any clever ideas on how to get that value from this data?

TIA
ML!

 

[Trevor G]

Your image suggest that the data is on 2 rows, what exactly are you trying to achieve, a little more information may provide a quicker and clear resolve for you.

 

[ML!]

Hi Trevor,

That's precisely my problem. The 'transaction' table captures values described by parameters for each transaction. In this case, the transid = 174.

In this transaction, there are 2 parameters...A & B. I want to use those 2 values in a calculated control on a report.

A is the length of a shift
B is the amount of idle time on a machine

For this transid, I want to be able to calculate the percentage of work time

1-(B/A)*100 (where transid=147)

So the calculation needs to isolate a transid then use the parameters in that 'collection' in the formula.

Hopefully that makes sense...and hopefully, it's possible to do

ML!

 

[Trevor G]

It does make sence, unfortunately I don't see how Access would do this for you as they are basically 2 individual records, can you not have the data on the same record but in separate fields, then Access would be able to deal with this quickly.

Where would the result go if on 2 different records?

Excel would deal with the calculation fairly easily, yet I feel your example is fairly narrow as there would be a large amount of records requiring the calculation, so placing the fields on the same record would be the best solution.

 

[ML!]

Thanks Trevor, I came to the same conclusion last night. I am guilty again of trying to make Access work like Excel. I have already restructured the table as you suggested. I appreciate your input.

 

[Trevor G]

Pleased you sorted this out.