Dynamic field names per record

[snofrandy]

Hello,



This is by far the most complicated DB i need to build. Basically, my manager wants something that if a certain Account Number is searched for, the lease schedule will pull in along w/ the lease amount similar to this:

Acct# 5/30/2012 6/30/2012 7/30/2012......5/30/2017
123456 $1000 $1000 $1000 $1000

The problem is, all the data I have are the Acct#, Lease Start, Lease Amount. Each account has their own lease start date and lease amount. All leases are 60 months and there are over 500 accounts.

I can create a crosstab and have them linked via Acct#, but what if the lease date change for another account. Won't it break the query?

So is there a way to make the field names for the date dynamic, so that each time an account is searched, it will populate the dates for fields. Is this even possible? So for example if i search for another account, it will look like this:

Acct# 7/6/2011 8/6/2011 8/6/2011......7/6/2016
345678 $1000 $1000 $1000 $1000

I'm still new to access and VBA (two years) and I learn everything on the fly. I'm in finance field but this project was given to me because of my working knowledge with Access.


Thank you!

[pbaldy]

What is your table structure? You don't have fields for dates, do you?