Reports with subreports and partial info selection (1 Viewer)

Susy

New member
Local time
Tomorrow, 00:28
Joined
Dec 19, 2019
Messages
25
Hi,
I have two queries that I would like to show in one single report. I did so by using a subreport. I get the info I want to in the report, but I would like to change the presentation.

InkedReports_LI.jpg


I would like to select some info of the subreport and show it under the line plan (empty cells highlighted in yellow) if it matches the criteria in the main report (surrounded in red). Eg. Bananas Green January in the main should show the first line in the subreport. Does anyone have some ideas on how I could achieve this?
Thanks,
Susy
 

vba_php

Forum Troll
Local time
Today, 18:28
Joined
Oct 6, 2019
Messages
2,884
Susy,

I'm not a report expert but since no one has said anything....I honestly don't think you can do this. The info you provided is actually kinda vague. Can you provide anything else than that? You're obviously going to need code to do this, if it's even possible. You can do this sort of stuff in a form, and I'm guessing in a report as well. There are articles on this forum that show various code lines that deal with control and subcontrol syntax, in terms of pointing to them from various other places in an object like a report. That is what you want. Here is a thread that covers some of it: https://www.access-programmers.co.uk/forums/threads/form-control-referencing-in-vba.147019/. I know there are others here. and here are two pages on my website that might help you get started too:

=> Form Control Referencing: http://t.adamevanovich.com/formControlReferencing.php

=> Form and Subform Syntax: http://t.adamevanovich.com/FormAndSubFormSyntax.php

I'm fairly certain you can perform those same references in reports too.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:28
Joined
Feb 19, 2002
Messages
42,979
Join the two tables on the related data. If there is nothing that relates the two tables, how would you do this with code when there is no way to tell what goes where?
 

Susy

New member
Local time
Tomorrow, 00:28
Joined
Dec 19, 2019
Messages
25
@vba_php: Thanks for your input. In Excel, I would simply use a VLOOKUP or something similar. My criteria would be Bananas, Yellow, March. Would you recommend working on my queries to simplify the task? Or do you think that I will be successful to do it directly in the report? (I am not a specialist in coding and I am looking for the most simple solution! ;))
FYI for the moment, I have two queries: one where I consolidate my actuals and another one where I consolidate my plan. (The plan was monthly and the actuals were given by separate invoices.)

@Pat Hartman: Thanks Pat. The relationships between the two are the type of fruit (Bananas or Pines), Color (Green or Yellow) and the month.
 

vba_php

Forum Troll
Local time
Today, 18:28
Joined
Oct 6, 2019
Messages
2,884
Would you recommend working on my queries to simplify the task? Or do you think that I will be successful to do it directly in the report? (I am not a specialist in coding and I am looking for the most simple solution! ;))
YES. I was telling someone else yesterday that there has been very few occurances I've run into where a simpler solution did *not* solve the problem they had. But to be sure, I would guess you'd have to upload the actual DB file for people to look at the queries because my guess is that you explaining more about it in the thread here would take quite a while. As far as being successful doing it in the report itself, I'm sure you can, as almost anything is possible most of the time, but if you went about doing things that way, you'd probably have to get a little crazy with your coding! :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:28
Joined
Feb 19, 2002
Messages
42,979
---I would simply use a VLOOKUP or something similar. My criteria would be Bananas, Yellow, March. ---
In a relational database, the function of a VLOOKUP is performed much more efficiently by a join. It can also be performed inefficiently by dLookup(). If you don't know how to make the join, post the two tables with some sample data and we will build it for you.
 

vba_php

Forum Troll
Local time
Today, 18:28
Joined
Oct 6, 2019
Messages
2,884
Susy,

If you're still watching this thread, I'm assuming you looked at the content in those links I put up 2 posts ago. I have taken the content down and locked the subdomain. I just put it in HTML there instead of uploading text files because people here are so afraid of downloading files from threads, that I've noticed. So I hope it was of at least some help to ya. If for some reason you need it again, post here and let me know. I can always upload it in a text file, which is pretty safe.
 

Susy

New member
Local time
Tomorrow, 00:28
Joined
Dec 19, 2019
Messages
25
---I would simply use a VLOOKUP or something similar. My criteria would be Bananas, Yellow, March. ---
In a relational database, the function of a VLOOKUP is performed much more efficiently by a join. It can also be performed inefficiently by dLookup(). If you don't know how to make the join, post the two tables with some sample data and we will build it for you.
Thanks Pat. I have tried to make the join, but as I am using calculated values in my tables, it doesn't work for all relationships. Consequently, I have to review some parts of my database. Thanks for the tip! ;)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:28
Joined
Feb 19, 2002
Messages
42,979
You can join on calculated fields. You just can't do it with the QBE. Switch to SQL view and use the calculation to do the join.

Select ...
From tbl1 Inner Join tbl2 ON tbl1.fld1 > (tbl2.fld3 + tbl2.fld4)

This is called a non-equi join and cannot be represented by the QBE.
 

Users who are viewing this thread

Top Bottom