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

[SurreyNick]

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.

 

[theDBguy]

Hi Nick. If you can create a separate report for each query, then maybe you can try using them as subreports.

 

[SurreyNick]

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]

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]

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]

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]

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]

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.

wip200413.zip

Compressed (zipped) Folder

1drv.ms

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

 

[SurreyNick]

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]

I started grouping my queries with similar names or numbers when they are used together either as subqueries or for the same form.

 

[plog]

Once I saw the tables this looked familiar:

Help on how to handle this complex data entry task

I could do with some help regarding the best way to approach the following tasks. It’s to do with entering students’ exam scores for the schools database I’m developing and it’s the thorniest task I’ve confronted so far. It’s not a simple case of just entering a student's total score for each...

www.access-programmers.co.uk

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

 

[SurreyNick]

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]

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]

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]

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]

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]

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.