Life Expectancy tbl will display on rpt beginning with client age

[djrickel]

First of all, THANK YOU in advance for your help with this. You guys are awesome as this forum has been a huge help to me.



Originally, we created a large Word document that linked to Excel many, many, MANY times but had nothing but problems with the links causing each object to embed instead of being dynamically linked--forcing tons of constant rework. Often, the Word document and Excel workbooks would corrupt so badly that neither could be re-opened. So I concluded that dynamic links work great in theory but not in real life, especially in large quantities.

Now, I have a database that will print off a series of reports mimicking each page of the Word document, each one dependent on a certain field or two from the Client table.

Here's my dilemma: (see the sample Word document) The big issue is how to recreate the life expectancy table on an Access report that begins with the client's current age to show how long the client could potentially live.

I assume this would be a subreport pulled from a Life Expectancy table? But how to get the subreport to format the same way? The idea is to get this subreport to look like a snapshot of a life expectancy table beginning with the client's current age.

[June7]

Need to know more about data structure. Do you have a table of life expectancy records? Build subreport of that table and apply filter criteria to the Age column: >= [input Age value here]

The filter parameter can be a dynamic popup in query (I never do this) or can be reference to a textbox on a form or even the client's calculated age in the main report RecordSource.

If you don't have this table, there are methods to generate these records for report but involve quite a bit of VBA code.

[djrickel]

Sorry...just attached the database with the Client and Life Expectancy tables.

If you look carefully at the sample Word document, the table appears in two columns of Age and Year. Excel can do this but I can't figure out how to format this in Access.

Thanks again for your help.

[June7]

Two sets of columns for Age and Expectancy is a multi-column report. This is a design setting of report. With report open in Design or Preview, look at the Columns tool on the Page Layout section of ribbon. However, I think must done on main report, not a subreport. Which means cannot have text info next to the columns as shown in the pdf.

Alternatives require VBA code.

[djrickel]

That worked! Thank you!

[djrickel]

Oops. No it didn't. When I link the form and subform based on the client's current age, the subform only returns one line for the current age. Excel solves this with a VLOOKUP. Help!

[June7]

Did you mean report/subreport?

I did say multi-column probably work only on main report.

But why are you linking on age? Age must be filter criteria, not a link.

[djrickel]

The multi-column subreport does work if it is not linked or filtered. As soon as I do a master/child thing or a filter then all I see is the one row that matches the client's current age. From the sample I attached, you will see that it is a table snapshot beginning with the client's current age. How can I replicate the Vlookup done in Excel to create that in Access?

[June7]

Don't understand why filter won't work. What did you use? Age>= [client age]

The only alternatives I can suggest involve a lot of VBA.

[djrickel]

OMG. The littlest things hang you up....

I had only used = and >= !!!! Thank you so much. It works now.