Solved Advice on how to proceed with report dependent on data from 5 queries and with 3-field filter (1 Viewer)

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
I have been going round in circles on the best way to approach a report which is dependent on data from 13 tables and with a three-field filter.

The report is a student's exam feedback, with all information summarised on a single page. The best I can achieve is 5 queries to extract the data I need (one of which comprises two sub-queries of which one is a crosstab query). There is one field common to all 5 queries and this is TestID. The report also includes a number of calculated fields.

I have included a sketch of the report showing the desired layout and I have colour-coded it according to the queries which provide the data. The three fields upon which the report is filtered are circled in red.

I’m working on the assumption I will need a form as a front end, which is where the three filter fields will reside, but the report is so complex that as for the rest I don’t know where to begin. Queries, VBA and recordsets, text boxes with expressions, combinations of some or all?

Any advice on the approach and steps I should take to achieve what the users have requested will be greatly appreciated.

Nick.
 

Attachments

  • rptStudentFeedback.jpg
    rptStudentFeedback.jpg
    131.8 KB · Views: 44

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
14,860
Hi Nick. If you can create a separate report for each query, then maybe you can try using them as subreports.
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
If you can create a separate report for each query, then maybe you can try using them as subreports.
Yes, I might be able to do that. Good idea.
It will mean increasing the number of objects in the db, which I was trying to avoid (is that crazy?) but if I can link them all together on the same TestID that could do it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
14,860
Yes, I might be able to do that. Good idea.
It will mean increasing the number of objects in the db, which I was trying to avoid (is that crazy?) but if I can link them all together on the same TestID that could do it.
Hi. With a complex set up like yours, it may be inevitable to create additional objects to simplify things. Why were you worried about creating more objects? As long as you don't hit the limits, you should be fine.
 

plog

Banishment Pending
Local time
Today, 02:36
Joined
May 11, 2011
Messages
10,384
If that data is related (and since its going to be on one report it must be) then I don't understand why you must have 5 queries. The yellow, pink and purple data should definitely all come from 1 query. You just use report grouping to hit the format you want.

After that the green and blue might be able to go into that 1 query mentioned above. But worst case they each become their own subreports. However it still must be related to the first query in some manner if you expect to be able to filter it on 3 fields (obviously those 3 fields are related among all your data sources). So you set the parent/child relationships between the main and sub reports using those 3 fields.

Again, I'd like to see SQL and a Relationship tool screenshot for more specific advice. Or even a sample of the database with all pertinent objects.
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
With a complex set up like yours, it may be inevitable to create additional objects to simplify things. Why were you worried about creating more objects?

LOL. Alas it's just my crazy compulsive mania for neatness and tidiness. All those objects looks cluttered!
There is no good reason DBguy, no good reason at all. It was just a flippant comment and I must get help for my OCD :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
14,860
LOL. Alas it's just my crazy compulsive mania for neatness and tidiness. All those objects looks cluttered!
There is no good reason DBguy, no good reason at all. It was just a flippant comment and I must get help for my OCD :)
Oh, I see. Some people use Navigation Groups to somewhat organize the objects. I personally don't use it, because I get more confused when seeing duplicate objects, but have you considered trying it just to see if you can get some sort of organization with your database objects? Just a thought...
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
I'd like to see SQL and a Relationship tool screenshot for more specific advice. Or even a sample of the database with all pertinent objects.

Hi plog

Thank you for your reply and perhaps you are right about fewer queries being possible but I haven't been able to. That's not to say it can't be done, but more that I can't. Please bear in mind that I am a novice and still working from manuals.

In answer to your request I haven't been able to get the db small enough, even zipped, to put here so I have made a zipped copy of just the tables populated with dummy data and the queries for this report available on Microsoft OneDrive which you should be able to get by following this link. I haven't started creating the form or report yet.


I have also attached here a screen shot of all the relationships. The Tests table is poorly named and causes confusion. It is a junction table between ExamPapers and Students and is nothing to do with physical test papers but is actually instances of students taking exam papers.

And yes, I know there are circular relationships but I could see no way around this. I don't believe they will be liable to cause problems. Well, at least they haven't so far!

Nick
 

Attachments

  • wipDB.jpg
    wipDB.jpg
    189.2 KB · Views: 36

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
Oh, I see. Some people use Navigation Groups to somewhat organize the objects.

Ah yes, I remember reading about this option. I share your view though about duplicate object views so it's not for me either. The plain fact is I just have to overcome my foolishness and have as many objects as it takes to get the job done.
 

zeroaccess

Active member
Local time
Today, 02:36
Joined
Jan 30, 2020
Messages
672
I started grouping my queries with similar names or numbers when they are used together either as subqueries or for the same form.
 

plog

Banishment Pending
Local time
Today, 02:36
Joined
May 11, 2011
Messages
10,384
Once I saw the tables this looked familiar:


I stand by my statements in that thread and believe that this is a symptom of your structural issues.
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
I started grouping my queries with similar names or numbers when they are used together either as subqueries or for the same form.
Yes, I did the same. Makes it much easier to keep track of everything.
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
Hi Nick. If you can create a separate report for each query, then maybe you can try using them as subreports.
Just wanted to say thank you DBguy. Your suggestion works beautifully. I have been able to format my report exactly as desired by the users.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
14,860
Just wanted to say thank you DBguy. Your suggestion works beautifully. I have been able to format my report exactly as desired by the users.
Hi Nick. Congratulations! Glad to hear you got it sorted out. @plog, @zeroaccess, and I were all happy to assist. Good luck with your project.
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
Yes, you are right to point out others contributions too. I wasn't overlooking them, simply acknowledging that the solution you proposed was the answer. I have received plenty of support to the threads I have posted on this site and I am very grateful to everyone who has shared their valuable knowledge and experience. I can say quite unequivocally that I wouldn't have got this far without it. :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
14,860
Yes, you are right to point out others contributions too. I wasn't overlooking them, simply acknowledging that the solution you proposed was the answer. I have received plenty of support to the threads I have posted on this site and I am very grateful to everyone who has shared their valuable knowledge and experience. I can say quite unequivocally that I wouldn't have got this far without it. :)
Hi. No offense intended. That was just my way of saying thanks to them too for helping me assist you in this thread. More often than not, I also learn from the contributions of others. Sometimes, they see something I miss. Cheers!
 

SurreyNick

Member
Local time
Today, 08:36
Joined
Feb 12, 2020
Messages
127
No offence taken DBguy. I am nothing other than grateful to everyone who takes the time to contribute to my threads sharing their knowledge and experience freely. I can't promise to incorporate every bit of advice proffered but I'm still grateful for it.
 

Users who are viewing this thread

Top Bottom