need to loop through to get period from field

[#1]

I have a table that has GL ACCOUNT BALANCES.  It has numbers for each GL account for each year.  The record has closing balance for each period, although PERIOD is not a field on the table.  I really need to have PERIOD as a field to link to the transactions table.

I know there is a way to loop through the table and pull out a portion of the field to use as the period.

Example:

field name:  GL_CLOSING_BALANCE_01

The period is 01

same for GL_CLOSING_BALANCE_02 through 12

I would like to loop through   for i=01-12 and have a closing balance for each period.  I would like for the user to be able to choose a PERIOD and have the report pull the correct closing balance for them.

Any advice?

I am pulling this info from a qvd called GL_ACCOUNT_BALANCES.QVD

I'm not sure if I have given enough information.

[#2]

I thought I tried that before, but will give it another go.

Thanks for your patience!  I'll let you know how it turns out. 

[#3]

I think that did it!  I was still getting synthetic keys but realized I was still missing one of the fields for close balance.  Once I made sure they were exactly the same fields in both sections, the synthetic keys were gone.

I will set one up for each period now, and link it to the detail tables. 

I cannot thank you enough for your help.  I learned a lot on this one.

[#4]

how do I mark this as resolved; correct answer?

[Anonymous]

Glad that worked.  You should be able to choose a post and mark it as the correct answer.