Using the data from different record

  • Thread starter Thread starter Nata
  • Start date Start date
N

Nata

Hi all:)

I have a report of accounts’ balances. Balances are in USD and account
currencies. My reports picks up the currency rate associated with period and
calculates USD equivalent. It Looks like

GL account 1
Period A
Transaction ! Account currency amount ! Rate ! USD Equivalent
Transfer 1,000,000.00 103.00 9,708.74
Period activities total 1,000,000.00 103.00 9,708.74
Period A Ending Balance 1,000,000.00 103.00 9,708.74

Period B
Transaction Account currency amount Rate USD Equivalent
Transfer 55,000,000.00 103.00 533,980.58
Disbursement -100,000.00 103.00 -970.87
Period activities total 54,900,000.00 103.00 533,009.71
Period B Ending Balance 55,900,000.00 103.00 542,718.45

GL account 2
Period A
Transaction Account currency amount Rate USD Equivalent
Transfer 1,000,000.00 1.00 1,000,000.00
Period activities total 1,000,000.00 1.00 1,000,000.00
Period A Ending Balance 1,000,000.00 1.00 1,000,000.00

Period B
Transaction Account currency amount Rate USD Equivalent
Transfer 55,000,000.00 1.00 55,000,000.00
Disbursement -100,000.00 1.00 -100,000.00
Period activities total 54,900,000.00 1.00 54,900,000.00
Period B Ending Balance 55,900,000.00 1.00 55,900,000.00

I do not have any problems with USD, but I need to evaluate other currencies
with next month currency rate. So I need to insert next month currency rate
in the current month row and calculate the difference between these numbers.
I guess I am looking at “Using the value of next record in queryâ€

Could you please give me some direction to go whit that?
 
Perhaps you left off this part, but I'm not seeing how/where your
transaction records include any date/time information.

"How" depends on "what" ... what does your underlying data look like?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top