Creating report with grouping based on unique ID

[nammeci]

Good afternoon, community,
I am trying to help a friend create a table where data will be pulled from an Excel spreadsheet. The Excel spreadsheet looks as follows:
Report Number Date Title Recommendation Number Recommendation Text Funds

Please see the attached picture for reference.

The outcome should be sorted based on the last five characters of the report number (in descending order), and the date (in ascending order). Additionally, the data should be grouped based on the Report Number, Date, and Title. Finally "open Recommendations and Potential Monetary Benefits" as seen in the picture should be repeated on each page. The full file can be found here.



Now, my question is: Is this even doable in IMS Access, and if yes, how?
Thank you in advance.

[June7]

Should be.

Will have to extract parts from the report number with string manipulation functions, such as: Right(ReportNumber, 5).

Also, the date value could be an issue because it is a string and 10 will sort before 2 so that will have to be dealt with as well, like: Format([Date],"mm/yyyy")

Build a query that has those calculated values, like:
SELECT Recs.*, Right([ReportNumber],5) AS RepNum, Format([ReportDate],"mm/yyyy") AS MoYear FROM Recs;

Use that query a RecordSource for report and set Sorting & Grouping design features.

Place labels in PageHeader section for "Open Recommendations" and "Potential Monetary Benefits".

Why (This) included in each column header?

Are you having difficulty getting data into Access?

Can attach file to post for analysis. The link provided only has a PDF document, no data file.

Attached is a mockup to get you started.
Database1.accdb

[Micron]

I would recommend linking the spreadsheet in Access so as to have access to the latest data. Then manually create a table in Access with fields that have the correct data types. Then use either update and append queries to move the data into the Access table or use something that's known as an Upsert query. As long as the sheet data doesn't violate the table field data types (e.g. if it's an integer, the sheet column data will not exceed that boundary) then everything should be copacetic.

[jojowhite]

upload your workbook for all to see and work with.

[nammeci]

Thank you for helping with this.

[June7]

Did you look at the sample I uploaded?

Not seeing any attempt to employ aspects of example I provided.

[madpiet]

<shudder>
Ask your friend for the original file. I'd bet it's a PDF. If that's the case, use Excel. If you can get your friend's permission, post the file somewhere so we can see it. In a nutshell, use PowerQuery to connect to the PDF file(s) and then find the table you want to import. That will put it in Excel/PowerQuery as tabular data. Then you can use PowerQuery to do all the cleanup. Splitting columns, changing text strings to dates and so on. Nothing to it. When all of that is done, you should have a second sheet with the cleaned data.
Go into Access, create a linked table, and then query the data... or do the whole thing in Excel. You may not need Access at all.

[jojowhite]

see the SemiAnnualReport report.

[mike60smart]

See rptRecommendations in the attached