Datasheet like report inside a report (1 Viewer)

[penfold1992]

Hello all again,
So... I think I know the answer but I am not sure how to execute it.

I have two tables
1. Package Information
2. Package Uses

Table 1 contains information about packages, from what the package contains, contacts, priority etc etc.
Table 2 contains information about when the package is referenced,
Package Name (linked with Package Name in table1), Date of use and location.

I want to create a dynamic report about each package so I have a page "header like" section which contains information from table 1. THEN I would like a table under this which contains the columns in table 2 WHERE Package Name from table 2 = Package name from table 1

I dont think I can just add these columns in and turn them to "Tablature" design (for some reason it puts this inside the header section and I cant drag it down to the location I want on the page) so I think I have to use a "Sub Report"

this is where coding is required because not only do i need:
Main report - WHERE Package Name = Package Name selected in a form (I already have this set up and working)
Sub report - WHERE Package Name = Package Name (the way I set up the above one was to open it with a parameter... I am not sure if this can be done to the sub report to correspond with the Package Name listed in the header information with no issues...)

Is a sub report the right way to go?

 

[GinaWhipp]

I'm not sure I understand what you are describing but it sounds like you have a one (main Report) to Many (Subreport), so yes, a subreport is the best way. Now you can *link* them on Package Name as I'm not sure, based on your posting, if the two can be linked any other way.

 

[penfold1992]

I'm not sure I understand what you are describing but it sounds like you have a one (main Report) to Many (Subreport), so yes, a subreport is the best way. Now you can *link* them on Package Name as I'm not sure, based on your posting, if the two can be linked any other way.

Hopefully this picture will explain what I want it to do...
the hard part is getting the "sub report" to only report out the "types" in the "packages"

 

[GinaWhipp]

But if you are linking on Package Name and there are Types associated with that Package Name I still don't see why that would be hard. Are the Types associated to the Package? If not, then how are your Tables set up?

 

[penfold1992]

But if you are linking on Package Name and there are Types associated with that Package Name I still don't see why that would be hard. Are the Types associated to the Package? If not, then how are your Tables set up?

So table1 essentially contains all information that is not within that table, the unique column is the Package Name.

table2 contains multiple lines of the information within that table PLUS it contains two columns that are hidden, Package Name and Type so that I can identify where each record is assigned to which Type and Package.

when I start up the report, I will filter on Type so I will have:
from table 1 a list of all the types where package is equal to the filter.
(same time, multiple packages)from table 2 a list of records where Package equal to the filter again with multiple types within.

so, then for each type within the package, display the list where: each record = type and package



EDIT: as expected, the results in the table contain all information related to the Package, it ignores the type. so it produced 3 reports each containing ALL the type's records rather than splitting them up for each report.
EDIT2: image showing what results are like... Pro photoshop skillz

 

[GinaWhipp]

Looks like you need to link the Report/Subreport by *Package Name and Type*. That should solve your problem.

 

[penfold1992]

Looks like you need to link the Report/Subreport by *Package Name and Type*. That should solve your problem.

argh, frustratingly struggling still

because the type can contain multiple values, it requests that I give the value for type when the report opens (rather than give all values of type in the report...)

I am not sure if there is a way I can scrap the sub report idea and just use some groups to do it instead (but im not sure how that will turn out especially when I want to put text boxes AFTER the table)

I am also struggling with filtering the data within the subreport, I need to filter it to only show values = to that of package but it doesnt appear to want to do that when.. still looking further into this.

I may decide to try to create a mock database to upload so you can play around with a similar idea

 

[GinaWhipp]

How are the two tables linked? Because perhaps the issue is a *Junction* table is needed. This way you could connect the two in a many-to-many relationship which you could then use for your subreport.

 

[penfold1992]

ok... here is what I am trying to achieve...
View attachment Database11.accdb
if you select "BMW" you should see 3 pages of the report.
Page 1, contains a list of owners of M3.
Page 2, contains a list of owners of M5.
Page 3, contains a list of owners of Z3.

If you select "Mercedes" you should see 3 pages of the report.
Page 1, contains a list of owners of B-Class.
Page 2, contains a list of owners of C-Class.
Page 3, contains a list of owners of E-Class.

for an additional step, I only want to display the Owners that appear on the latest date.
For "Ford" - "Fiesta" I should see 1 line in the subreport.
For "BMW" - "Z3" I should see 3 lines in the sub report.

Give it a go and let me know how you get on =)

Thank you!

 

[GinaWhipp]

Okay, so if I link on Manufactor and Model for Ford Feista I see 4 records, how do you figure one? What do you consider the latest date? Whatever date that is you have to some how *tell Access*.