Dlookup

[mbath20110]

Have a report that gets its data from a query.

I want the report to contain data from different records from the data source. Let me explain.

The report has fields for current year and prior year and under each of those fields is the corresponding data. (i.e. 2004 will show two corresponding fields of data and right next to it 2003 will show its data).

The data source (query) contains the Year field.

On the report, I need to show both years data on the same report, so here's what I have in mind.

For the current year, simply pull down the 'Year' field and the two corresponding data fields. For the prior year, I'm thinking I should use the DLookup function to lookup to the same query and get the prior year and it's corresponding data.

Would someone mind weighing in and letting me know if they think I'm on the right track?

thanks.

 

[FoFa]

That would work, but it would be really, really, slow. Why not just join your table to pull the prior year data and have it all on one row?

 

[mbath20110]

join the table ...

considered something like that but wasn't sure how to proceed.

you mean like, join the table to itself??

 

[mbath20110]

think I got it ...

what made it tricky (a detail that I may have left out) is that my data source (query) was actually several tables already joined.

But, on your advice, I played around with pulling down more than one instance of each table.

As you correctly noted, that had the bonus effect of putting everything on one record which I was then able to link my report fields to.

Thanks for forcing me to think thru that one ....

 

[FoFa]

Glad you got it to work. Usually joining the tables (in this case called a self join) back to themselves works much better than the slow agregate functions.