How do I create a report to output to a specific layout for records going back a number of years?

[AccessAllAreas]

Hello to All You Experts out There

By means of assistance I have got from these forums, I have got two major steps closer to finishing my project. Thank you. Here is another issue that I have that must be core functionality for the DB app I am producing. I have tried getting it to work on a report, but was unable. I then turned to a Form, but still no joy. But at least with the form I could produce the pre-specfiifed format required for the form in question (it is based upon a PDF). As I am reporting on entered data, I am as sure as this amateur can be that it is a report that I need. However, I cannot get the report to do what i need it to do. I am not a novice with reports. But this seems to be a level of sophistication that is beyond me right now. Hence, my question to the experts.

I am attaching a picture to show what the format of the report must be, with a little fake data in it. A few things have been removed, but nothing that would cause a solution from being unable to be proposed.

Goal: To be able to report for each "publisher" their monthly "activity", with calculated fields in the bottom two rows. The report would have their personal name at the top, their Date of Birth, their gender and a few other fields relating to personal info. In the grid below, there is a Year field, which would effectively be akin to a financial year (offset from a calendar year). This would autopopulate based upon the calculated field in the query this report would be bound to. Further into the grid, the report should show the activity of the individual per month, going back up to 12 months, a full "fiscal" year. Each of these monthly entries equates to one record in a query. The next record (for the next month, it should show below the previous month). At the bottom of the grid are some totals fields. These should be auto-calculated when the form is run (I am going to try to work that out on my own, but I am definitely not above taking suggestions on the bit too!)

Now, here is the important bit. The grid is duplicated twice on one Letter or A4 page. The personal info mentioned above (Name, Date of Birth, etc) must not be replicated on the same page. If the person has been with the organisation a number of years, it could go onto another page. That page would show the "fiscal" year, starting with the one immediately prior to the one shown on the bottom grid of the First page, continuing in the bottom grid of the second page with previous year to that one, if there was activity from that person with the organisation going back that far. And so forth (although one would normally cut off after going back 48 months).

I have attached two pictures.

Firstly, the form as it should look (with some redactions of personal info). Once again, due to screenshotsize, i only managed to capture the top half of the form as it should look. That means that this picture is just over half of the page, the remaining half is another instance of the grid.

Secondly, a picture of the report output of what I was designing! As you can see, it is horrible. And does not get the data to display correctly, with multiple publisher numbers (different individual's activity) all showing up under the personal name of one Azariah Abraham (not his real name!)

Can anyone help? I am completely stuck!

 

[June7]

I would have to work with your db to figure out how to best design report. If you want to provide, follow instructions at bottom of my post.

 

[arnelgp]

on your report, you Add a Group (group by Name field).
click on the Group band and size it to 0 (Height = 0).
the "Force New Page" property should be set to "Before Section", so that each
"Names" will print on each own page.

 

[AccessAllAreas]

Hi Arnel and June7

Thanks for your input.

I will attempt Arnel's solution First. I will then sanitise the DB and share it, as needed.

 

[AccessAllAreas]

Hi All

Arnel, thank you so much for your suggestion. It worked - mostly! Basically, I had too many headers turned on (report and page, with the page having several fields in it).

As i say, it mostly worked. The thing I cannot work out is how to ensure that there are always twelve rows per grid, one per month of the year, two grids per printed page (although I have some strange things going on with two records being shown per month, but I will have to look at the query.)

Any ideas on how to ensure that two grids print, each with 12 rows per grid (no more, no less), with each row having the name of each month displaying, even if a record has not been recorded for that month?

 

[CJ_London]

without knowing the detail of your tables, the usual way to force 12 lines is to have a separate table listing the months and left join that to the months in your query - then instead of displaying the month in your query, use the month in the new table

 

[arnelgp]

you need to Fill those missing months.
on the attached sample, table1 does not have the complete months in a year.
I added table m (months) and table y (years).
then I created qryMonthYear.

1_missingMonths query shows the query from table1
2_fillMissingMonths is the final query with months missing been filled up.

 

[AccessAllAreas]

Thanks for the suggestions. I will give those a go now!

 

[AccessAllAreas]

Hi Arnel

Many thanks for the sample DB. I think I kind of understand the ideas. I will attempt to implement something similar.

This is a specific query for yourself. I think I made an mistake. i tried to import the tables and queries from your sample into my DB. However, now I am getting an error on being unable to open for exclusive use, even when opening for exclusive use.

Can you point me to where I need to turn of config or remove code so that I do not get this error any more?

 

[arnelgp]

Im not sure what you did. You can delete the tables/queries.

 

[AccessAllAreas]

I simply imported the tables,and queries, including relationships.

I have restored a backup now, so no problems. I just thought you should know what has happened when someone else has tried to import those object from your DB.

Many thanks for the help, though.

I will use that as a template, as I think I have worked out that the info1 field I basically anything else invthe table i already have that I want to assign against a given date entry!

 

[AccessAllAreas]

Hello All

With Arnel's suggestions, I can indeed say I have made some progress on this. So, many thanks.

However, even after taking out almost the identical elements from your DB, Arnel, and adjusting what I thought were the principles i had gleaned, I still cannot implement what I need. I obviously was wrong

Here are three screenshots, with a couple of necessary redactions, that show (a) report as it currently stands (b) the query called Query_Fill_Missing_Months, displaying the output of what I thought I had gleaned from Arnel's suggestion and (c) the design of the aforementioned query.

On the point (c) above, it should be noted that the query is sorted on two additional fields that cannot be seen on the screenshot, in the following order: Year (DESC); and Month / Year (ASC), in the order mentioned.
Also, on the final screenshot, I have added a little colouring just to make it easier to associate which some of the key fields are and where they come from.

To repeat, the aim is to have a single grid per "Year", listing all 12 months of the year, whether there are entries listed for the individual month or not, it should still have an entry in the grid, so that all 12 months names are displayed in the grid, even if there are no entries listed for the month that month for that person. Finally, a second grid should appear directly below the First grid, but without the header, as shown. this grid should also display all 12 months, even if no data is entered for that month.

Looking forward to hearing your ideas.

Many thanks in advance.

 

[arnelgp]

if you can upload a trimmed version of your db, with only 1 publisher.
the query is correct. all you need is to tweak your report to appear as
you want it to. you only need to experiment on each report Sections
property.

 

[AccessAllAreas]

Hi Arnel

Sorry for delay in replying. Other projects diverted me from this. And I did try to adjust the properties of each of the section headers, but to no avail. I must have missed something>

I have trimmed the DB down now. It is attached below.

I have left a few publishers in the DB. The ones to look at with enough data for a decent comparison are:
Christopher Roberts
Abel Abraham

The relevant tables for this issue are:

Tab_Emergency_Contact_List
Tab_Report_Consolidated_Data

Relevant Query:

Query_Missing_Months

The Report that needs adjusting is:

Rep_Publisher_Record_Card........Missing_Month

Just to reiterate, the form should always show two (no more, no less) grids, with all 12 months displayed, whether there is data listed for that month or not, whether in the past or the future.

Many thanks in advance for your assistance in this.

 

[arnelgp]

have a look and see if this is what you want.

 

[AccessAllAreas]

Thank you.

I will have a look very shortly and get back to you.

 

[AccessAllAreas]

Wow! You did a lot of backend work here. Thank you!

I have no idea what functions most of the changes have, but, ultimately, it has got me much closer!

I have adjusted the report to group on ServiceYear (like a financial year), rather than the calendar year field . That now shows all twelve months in one grid in order (starting September, as needed) even if they do not have actual entries in them!

The only thing left do now is to work out how to get more than just the latest year'sngrid (2020) showing on the report!

 

[arnelgp]

The only thing left do now is to work out how to get more than just the latest year'sngrid (2020) showing on the report!

what do you mean?

 

[AccessAllAreas]

Sorry for my lack of clarity.

If a publisher has been with the organisation for a while, they will have built up legacy records, going back, potentially, years.

One each publisher's report, their name should appear once on the top of the page. The grid that we kindly now have should appear twice on that page, with the most recent year, 2020, being the top grid, and the grid, 2019, being below. That is all on one page. Then, the next page would replicate that layout, but with the top grid being the next year back (in this case 2018), and with the bottom grid being the year before that (2017). And so on and so forth. Obviously, as each year elapses, as happens on Tuesday, by sheer coincidence, then that year will show on the First page of the report in the top grid, with the previous year (in this case, on Tuesday, that previous year will become 2020).

I hope that is clearer. Actually, the format of the report that was in the DB that I uploaded demonstrates the multiple grids following on from one another for a given publisher (see Christopher Roberts). It just did not have them two to the page as it did not have all the 12 months of a given year in each grid!

Hope that makes sense.

Many thanks for your continued interest in assisting me with thus. It really is greatly appreciated.

PS I would love to be taught why what you have done makes it all work! It would be good for when I try to make a better version of this DB, or others for that matter!

 

[AccessAllAreas]

Hi Arnel

Just wondered whether you are able to educate me in what you did to implement this solution. it looks very complex and I wish to know how to port those changes into my full DB.

Many thanks again.