Create report design to meet requirements

[The Odyssey]

I have to create a report that replicates another created in excel. The problem is I have no idea how to do this in my current situation.

The design is in such a way that I don't think it allows for traditional record generation. The only option I have found is tedious and even that has been met with problems.

All of the data is currently being generated by many text boxes containing either a =DSum or =Val control source. Unfortunately, the data is year based, meaning that all reports must be filtered by a specified year. This means that DSum won't work because it will need to have a Year criteria, but that is not possible for the future.

Is there a way to create a report with the design shown in the above image?

Some extra info:

The table this report uses has the following relevant fields:

Firstname
Surname
Branch
Year_
April_A (Actual)
etc.
April_T (Target)
etc.

 

[plog]

The table this report uses has the following relevant fields

I think it can be done but not with the structure you said you have. You shouldn't have fields for each month, nor should you have a field specifically for Actual or Target values.

Let's say the column headings come from your Branch field and this is sales data. This would be the proper structure:

Branch, text
SalesDate, date
SalesAmount, number
SalesType, text, would tell you if row is for Actual or Target

That's it. With a table of that structure you could build that report.

With that said, I'm a realist. So, my advice is to not do this in Access at all. You said you are trying to recreate an Excel spreadsheet. I say, don't recreate it, repurpose it. Use a past spreadsheet as a template for this report. Extract data from Access, paste it into a tab of your spreadsheet then have the current report reference that data tab. Then when you need to regenerate this report in the future, extract your data from Access, paste it into Excel and the exact report you have now will be updated.