Solved Report not appearing? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Hi all.
Following on from this post https://www.access-programmers.co.u...ub-report-control-source.322708/#post-1820747 I am now trying to create the report.
The main report has three subreports, left, right and centre, which is below left and right.
I have managed to change the subreport control to load the correct report rptRest_By_Ship.

I have tested that report on it's own and it displays data. I have even added a label just to be able to see something if the data did not render correctly.
Yet now when I run the report for issue 44 26/09/1979, the report does not appear. :( This is the first Gazette issue that the changes should affect.
I have walked through the load of the main report and confirmed it does change the source object.

The essence of the reporting is that I need to identify the data, then concatenate the Rank (once) with all the names for that rank for that ship for that date/issue.
That I have got working via qryRankCrew and qryRankCrewConcat.

Then, my understanding is I need to concat RankNames into one control on the subreport. That works when in report on it's own. The report rptRest_By_Ship was copied from Rest_By_Ship.
The process is a little slow due to all the concatenates, but I could not think of any other way? :(

Would someone be prepared to have a look and advise where I have gone wrong please.?

Form frmReports is the starting point, and the Date entry combo needs to be 26/09/1979 issue 44 at least. At present I only have about 3 entries for the report, as I was using the old layout for the last 43 issues, but that should be enough for testing?
The main report is Main_By_Date, which uses Deck_By_Ship, Engineer_By_Ship and now the expected rptRest_By_Ship in place of Rest_By_Ship.
Once I have that working I can duplicate the process for Main_By_Ship.

I think that is enough to go on to see my problem, but if anything else is needed of course please ask, rather than having to go looking (Unless you prefer to?). Although it has been quite a while since I first created this (it was my very first DB back in 2011) it might be quicker, than you trying to work out my thought processes at that time. :)

Many thanks for any assistance.
 

Attachments

  • Bibbys (3).zip
    2.4 MB · Views: 175

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Addendum:
I have now even just copied the Main_By_Date report and amended the source for the subreport control manually, and still does not work.?
 

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
Please have a look at this updated file, it runs but still needs some work. The report is very slow because of all the concatenations and the subreport in question is not grouped so it shows duplicates. The main cause of the issue was the use of lookup fields in the Links table, specifically for the Ship field which was used in the linking of the report\subreports. For the main report and the top two subreports the Ship was coming from the Ship table (text, actual name) but for the concatenation is was coming from the Links table and it was a number (ID from Ship) obfuscated by the lookup.

To speed up the report you could alter the concatenation code (nowadays I use theDbGuy's version http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv) to use a saved query and base the recordset on that instead of a SQL string; that way you could add the selected date on the form as a parameter to limit the records that need to be concatenated. Or you could try to convert the concatenation queries into make table or append queries and base the subreport on those temp tables.

Cheers,
 

Attachments

  • Bibbys_Vlad.zip
    2.2 MB · Views: 193

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Thank you very much Vlad, (y)
Especially for explaining what the problem was. (y)

Yes, I have table lookups. It was my very first DB and seemed a good idea at the time. :(
Whilst I am aware now of some issues, this was not one of them. :( I was actually aware that the Ship was the ShipID, despite showing the Ship name as I used that in the Concatenate function call. I might just bite the bullet and remove them now. :)

This is pretty much a tidy up for the DB. Nothing is ever going to change when I get it all correct and updated. It is historic only.

I spent over 4 hours on this yesterday evening and this morning, without knowing what I had done wrong.
I will inspect your code and implement the changes accordingly.

The data to look for is only for 10 Gazettes and the initial qryRankCrew only pulls that data. I am going to try and keep it simple so I can understan it, in case I ever need to come back to tweak it. :)

I cannot thank you enough. :)
I will start on it again tomorrow.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Ok, I added a Group By, but it still shows three entries for the three crew members that should be concatenated. However that is not my main problem.
However as it is now, it is unworkable. We are talking over 5 minutes just to render the report each time. I have been checking each report and amending the data where incorrect. At that rate, I'll likely finish at Xmas next year. :) even with just 10 Gazettes left to check.

Last night I was thinking to create a table once or twice with the concatenated data, but even that is taking ages, so much so I have to kill it in Task manager. Just creating the query to get the data to append to a table is taking way too long.

Going to have to rethink on how to do this. :(
 

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
Hi, here is an updated file that is much faster, I removed some unnecessary concatenation in the first step, should be workable now.

Cheers,
 

Attachments

  • Bibbys_Vlad2.zip
    2.1 MB · Views: 192

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Thank you Vlad.
I have also been working all day on it.:)
Getting the rank and all the crew for the rank is faster now, as I used SimpleCSV as you recommended.
However as I tried to cut down on the number of records pulled at the start of the process, I had to use TempVars, so then the function would not work. So I had to modify it to use the DBguy's Generic recordset code as well, which now works fine.:)

It is the final step of putting all the records for one ship into it's own record that seemed to cause it to fall over, or grind to a halt. :( I've killed Access so many times today. :)

I'll try yours tomorrow.
Many thanks for the effort, not expected but very much appreciated. (y)
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Damn!, that is quick (y)
I had a quick look and see you combined some steps, which I did not think of doing :(

Thank you so much. I was resigned to having one record per rank and perhaps create a table at the end to work off instead. No need now, with that excellent job you have done. (y)(y)
 

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
(y)
You're very welcome, glad I could help!
Cheers,
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Sorry Vlad, I have discovered a problem. Your version repeats the rank for every crew member, when I only need it once at the start. That is why I had that extra step now I realise. :(
I copied in your qryRankCrew, qryRankCrewConcat and the qryRankCrewShipIssue queries.

Then I tried to amend them to get the desired end result after discovering the problem.
I do have the data as I want it, but repeated for every crewmember for that issue and ship. I just need the one record per ship/date.
How can I achieve this please.? I had to add Rank to your qryRankCrewShipIssue and that of course expands the data. :(

So almost there. The Berkshire for issue 44 has the extra data I have now added.
Previously there was only one of each rank, so this issue was not visible. I am sorry for that. :(

I've attached my DB with you queries, albeit modified. :(

If you could offer a solution by reply, I would be more than happy with that. No need to write extra code, unless you prefer to do so.

Thank you.
 

Attachments

  • Bibbys (4).zip
    2.4 MB · Views: 202

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
Sorry but I don't see it, what query repeats the rank?
This is what I have for the qryRankCrewConcat in the second version I sent you:
Screenshot 2022-04-11 101919.png


EDIT: Give me some time I look at your latest post again so I think I get it now.
Cheers,
 
Last edited:

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
Hi,
Please try this version.
Cheers,
 

Attachments

  • BibbysVlad3.zip
    2.1 MB · Views: 222

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
OK, thank you for that.
I have also been trying and think I can do it with
Code:
SELECT qryRankCrewShipIssue.Issue, qryRankCrewShipIssue.Ship, qryRankCrewShipIssue.On_Date
FROM qryRankCrewShipIssue
GROUP BY qryRankCrewShipIssue.Issue, qryRankCrewShipIssue.Ship, qryRankCrewShipIssue.On_Date;
and base the report on that query.
Might not be the most elegant solution, but I'll take anything at the moment. :)

At present I am having a problem downloading files from anywhere, so need to discover what is going on there, then I can download your latest version.
Thanks again.
 

bastanu

AWF VIP
Local time
Today, 16:22
Joined
Apr 13, 2010
Messages
1,401
Yes, I have done something similar, created a new query named qryRankCrewConcatFinal:
Code:
SELECT QryRankCrew.Ship, QryRankCrew.Issue, QryRankCrew.On_Date, SimpleCSV("SELECT ConcatRanksNames FROM QryRankCrewConcat WHERE [Ship]='" & [Ship] & "' AND [Issue]=" & [Issue]) AS FinalConcat
FROM QryRankCrew
GROUP BY QryRankCrew.Ship, QryRankCrew.Issue, QryRankCrew.On_Date, SimpleCSV("SELECT ConcatRanksNames FROM QryRankCrewConcat WHERE [Ship]='" & [Ship] & "' AND [Issue]=" & [Issue]);
and used as the recordsource for the rptRest_By_Ship subreport with the text box bound to [FinalConcat].

Cheers,
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
14,048
Hmm, lost my network download location, that was my download error problem. :)

Done it again Vlad, thank you. I am pleased to see I was on the right track as well.
Yes, I see you how you did it. (y)
 

Users who are viewing this thread

Top Bottom