Is multiple sub-reports is the best solution?

[ashraf]

Hi,
I've an Access database contains main table which is related with some other tables by "ID".
following is a simple example:

I created a report to display the data as on the provided example and used 2 subreports (subrepoet1 & subreport2) linked by ID.
I tried to create a query for such report but failed because of repetition.
My questions are:
-Is there any other way to create the report as per attached image?


-Is it possible to filter the report by I filed in subreport? (e.g. if the subreport1 value is "BB" the report shall display first & last records only.
Thank you.

[ranman256]

The output would be:
id-101
data
subrpt1
subrpt2

id-102
data
subrpt1
subrpt2

etc...

[ashraf]

Sorry I didn't get you.
I want the output report as per attached image.

[CarlettoFed]

Of course it can be done, but it is necessary to understand the structure of the database to give you the right answer.
So you should attach an example access file.

[ranman256]

i dont think Access reports work that way.

[ArviLaanemets]

You create 2 separate reports which return data like your subreports. Then you create a 3rd (main) report based on your main table. (There a 2 possible ways - a simple report where a row is for ID value, or you group rows by ID, and have main table info in group header - I'm not sure which way will work so you have test it yourself.) And then you insert 2 subreports into main report (in design mode drag-and-drop) - either into report detail or report group header (test yourself which works). And then link subreports with main report.

[ashraf]

The second way was exactly what I did (main report & 2 subreport) linked with the main report by ID and it worked fine.
but when I tried to filter the data on an item in subreport I failed.
and I'm asking if there is alternative method to do in a query to get same report.
Thank you,

You create 2 separate reports which return data like your subreports. Then you create a 3rd (main) report based on your main table. (There a 2 possible ways - a simple report where a row is for ID value, or you group rows by ID, and have main table info in group header - I'm not sure which way will work so you have test it yourself.) And then you insert 2 subreports into main report (in design mode drag-and-drop) - either into report detail or report group header (test yourself which works). And then link subreports with main report.

[ashraf]

Of course it can be done, but it is necessary to understand the structure of the database to give you the right answer.
So you should attach an example access file.

I'll try to make an example in access

[ashraf]

The attached access sample is what I did. sample01.zip
My question is, if it possible to filter the report "MainData" by a filed in "Items" ( the subreport) ? (e.g. if the "Items" value is "BB" the report shall display first & last records only.

[ashraf]

Just reminder
Thank you,

[Gicu]

Can you please show us how exactly you would like that filter to work?

Cheers,

[ashraf]

Thanks for responding Gicu.
I want to filter the report "MainData" based on a value in subreport "Items".
for example: if I want to display the records which contains ItemDesc ="BB" on the subreport "Items", the "MainData" report shall display only first & last records.

[Gicu]

First and last in Access only have meaning when referred to in conjunction with a specific sort order. What would that be in your case?

[ashraf]

Sorry Gicu, I didn't get you

[Gicu]

Your data is not clear enough as in your sample you only have two BB's so we wouldn't know which one to eliminate. If the sort order of the man data source changes, for example you sort descending by a "received" date or order date and then ascending by company name, the "first" and "last" will be different than if you would have sorted first by company ascending and then by date.

Cheers,