Using a Lookup or calculated field in an Access report

[jonfurn]

Hi all -

Thanks for reading - any help on the below matter would be really appreciated.


I've got a table where there's approx 200 'Org Strings' - the terminology we use for how our departments are hierarchically arranged (e.g. "South - HR - Admin" or "North - Planning" or "North - Area B - Department C - Office A" etc. etc.)

I've got a report (based on a query) that triggers from a command button on a form. This displays post/job information like Job Title, Grade, Temp/Perm etc. This all works fine.

However, in the report, which has a bespoke 'Org String' header so that all the posts are grouped by Org String, I would like to look up the Org String in that header and perform a calculation based upon a table that holds the 'OldPosts' number (a non-changing number) and the 'CurrentPosts' fields.

In a nutshell:
- The report is run and displays all of the chosen posts in Org String order (header) - THIS WORKS
- "South - HR - Admin" (e.g.) displays in the header - THIS WORKS
- Look up "South - HR - Admin" in tblOrgStrings and calculate 'OldPosts' field minus 'CurrentPosts' field - HELP!!
- Each OrgString header on the report to have this calculated text field alongside the Org String itself - HELP!!

So, if in tblOrgStrings, this particular org string shows 'CurrentPosts' = 35 and 'OldPosts' = 33, I would like the report to display, for instance:

South - HR - Admin (2)

This feels like it should be so simple, but I'm afraid I've been scratching my head for hours and hours trying DLookup, DSum and allsorts. I'm clearly missing something!

Any help from someone cleverer would be greatly appreciated!
Cheers,
Jonathan

 

[MazeWorX]

wouldn't this be easier at the query level?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[jonfurn]

Thanks, MaZeWorX - yes, I suppose that's an option. I've just been trying to learn through doing (I've only been using Access for a couple of months) and thought that adding a Lookup function to the 'repertoire' might be worthwhile for this and future projects.

If anyone can shed a bit of light on my original question anyway, I'd be grateful.

 

[dhookom]

I'm with MazeWorX. Doing any kind of a lookup in the report creates another recordset which consumes resources. I think the better and more efficient solution is to combine all possible data columns in the reports record source.

Duane
Hook'D on Access
MS Access MVP

 

[jonfurn]

Thanks, Duane. I will definitely go that route for this particular project. My curiosity is still getting the better of me how it could be done the lookup way (since I spent such a long time attempting to do it! Although it's clearly not the best way to go, I'm frustrated I wasn't able to lay it to rest! Thanks for the responses, guys.

 

[dhookom]

If you want to use DLookup() then you need to provide significant table and field names as well as the report record source.

Duane
Hook'D on Access
MS Access MVP