Report/Subreport problem (1 Viewer)

CosmaL · Sep 5, 2018

Hello dear friends,

my problem is the following:

I've a form which contains 4 tabs.

In the 1st tab there's a subform where you can add items form a list*
This tab is called section 1.

All the other tabs contain record fields (sections 2,3,4)

*items list table contains many data, i let the user just to select the items name and then in the report we view all the fields.

When generating the report, i got the section 1 having all the items i select-which is correct.

However -and that's my problem-i get all sections as many times as the items in the sub report. If there's only one, everything is fine. Having 2 or more items giving as results as following:

Section 1 (items list and more information)
Section 2
Section 3
Section 4

Again Section 1
Section 2
Section 3
Section 4

Is there any way to solve it and having all sections 1 time?

Thank you in advance!

isladogs · Sep 5, 2018

It sounds like your report data is based on two tables without a join. This is a do called Cartesian product where you get all possible combinations of data from each table.

If I'm right, you'll need to modify the record source by joining the tables with a suitable field that is in each table.

If I'm wrong, please provide details of your record source(s)

CosmaL · Sep 5, 2018

I'm sorry, i didn't mentioned that!

All sections are linked under the same product ID.

Record sources:

Main product:
Record ID (autonumber, key)
Product ID
Name
etc

SubProduct
Record ID (autonumber, key)
Product Code
Name
etc

Section 1
Record ID
Product ID
Name (Combo from SubProduct Name)

Section 2, 3 and 4
Product ID
Info
Description
Various different fields

isladogs · Sep 5, 2018

Please provide the SQL for the record source in the report itself and the subreport.
Also the master/child fields linking these.

CosmaL · Sep 5, 2018

It looks like a mess!

Sorry for complicating you!

Please let me give one more try and i'll add the code in the end.

The form contains 4 tabs.(main form contains 2 fields, Record ID and SDS_ID)

The 1st 3 tabs contains subforms, which data are from a query

4th tab, is a table.

In the record which i mentioned the problem, i've got 2 rows in the query (which i see them in the form).

All tabs (and table), are linked with main form with SDS_ID.

1st 3 subreports are from queries as the the subforms.
The 4th, is from the table.

All are linked with the same SDS_ID.

Code:
Main report source data: table, (Record ID, SDS_ID)

Subreport 1: source data: query

Code:

SELECT  tblMixtureIngredients.RecordID, tblMixtureIngredients.SDS_ID, tblMixtureIngredients.Ingredient, tblIngredients.[CAS No], tblIngredients.[8h-TWAmg], tblIngredients.[8h-TWAppm], tblIngredients.ShortTermmg, tblIngredients.ShortTermppm, tblIngredients.Comments, tblIngredients.Reference
FROM tblIngredients INNER JOIN tblMixtureIngredients ON tblIngredients.RecordID = tblMixtureIngredients.Ingredient;

(results 2 records listed -ok).

Subreport 2: source data: query

Code:

SELECT tblIngredients.[InhalationAcuteEffectLocal-Workers], tblIngredients.[InhalationAcuteEffectsSystemic-Workers], tblIngredients.[InhalationChronicEffectsLocal-Workers], tblIngredients.[InhalationChronicEffectsSystemic-Workers], tblIngredients.[DermalAcuteEffectLocal-Workers], tblIngredients.[DermalAcuteEffectsSystemic-Workers], tblIngredients.[DermalChronicEffectsLocal-Workers], tblIngredients.[DermalChronicEffectsSystemic-Workers], tblIngredients.[EyeExposure-Workers], tblIngredients.[OralAcuteEffectsSystematic-Consumer], tblIngredients.[OralChronicEffectsLocal-Consumer], tblIngredients.[OralChronicEffectsSystemic-Consumer], tblIngredients.[InhalationAcuteEffectsSystemic-Consumer], tblIngredients.[InhalationChronicEffectsLocal-Consumer], tblIngredients.[InhalationChronicEffectsSystemic-Consumer], tblIngredients.[DermalAcuteEffectsSystemic-Consumer], tblIngredients.[DermalAcuteEffectsLocal-Consumer], tblIngredients.[DermalChronicEffectsLocal-Consumer], tblIngredients.[DermalChronicEffectsSystemic-Consumer], tblIngredients.[EyeExposure-Consumers], tblMixtureIngredients.SDS_ID, tblMixtureIngredients.Ingredient
FROM tblIngredients INNER JOIN tblMixtureIngredients ON tblIngredients.RecordID = tblMixtureIngredients.Ingredient;

(result-2 records listed, OK)

Subreport 3: source data: query

Code:

SELECT tblMixtureIngredients.SDS_ID, tblMixtureIngredients.Ingredient, tblIngredients.FreshWater, tblIngredients.FreshWaterSediments, tblIngredients.MarineWater, tblIngredients.MarineSediments, tblIngredients.FoodChain, tblIngredients.[Soil(Agricultural)], tblIngredients.Air, tblIngredients.MicoorganismsInSewageTreatment
FROM tblIngredients INNER JOIN tblMixtureIngredients ON tblIngredients.RecordID = tblMixtureIngredients.Ingredient;

(results: 2 records listed, OK)

Subreport 4: source data: table linked with SDS_ID)

I've tried with 1 record and i got the report once-as it must be.
With 2 and more ingredients i get this report duplicate or triple.

It regenerates the report for each ingredient...

I must have all the sections once and not the same report again and again, as the query total ingredients.

Please accept my apologies for the inconvenience!

isladogs · Sep 5, 2018

No time to look now. Hopefully someone else can step in

Could you edit the post placing the code in code tags - the # button above the window when you write a post

CosmaL · Sep 5, 2018

It's the same problem like the following: https://access-programmers.co.uk/forums/showthread.php?t=301160

I've created a query for each table and changed the report's/subreport's source data but without success.

I've also tried with the code described in the attached file in the post but also without success.

I've attached (compacted, original file size ~4MB) the involved data.

It's all greek but never mind

main report is "rptSection08"

arnelgp · Sep 6, 2018

this is my analysis:

your main report is based on qry_8 (tblSection08).
table tblSection08 has the following fields and values:

Code:

fieldname	value
=================================
SDS_ID		5
Record_ID	8

SDS_ID		8
Record_ID	9

you are having duplicate report because you are
linking your subReport to SDS_ID, which can appear
multiple times.

you must find a way to restructure the
other table to include the Record_ID of tblSection08
to the other tables that are being used in the report.

to show you glimpse:

current Report structure:
-------------------------
all subReport pointing to SDS_ID.

main report opens, SDS_ID=5.
all subform points to SDS_ID=5.

next record on main report, still
SDS_ID=5.
again all subs link to this SDS_ID,
and therefore creating duplicate
report.

Proposed:
---------
all subReports points to Record_ID of mainReport.
your main form opens (Record_ID=8).
all subreport point to Record_ID=8 of tblSection08.

next record on mainReport, Record_ID=9,
all subform is now pointing to this record.

all subReport print only once (distinct).

CosmaL · Sep 6, 2018

Thank you arnelgp!

I've tried the following (i can't find a way to reconstruct the table...)

I've created a query (qryTest) where data are linked.

Problem so far is that i've got dublicated each record (2 products, 2 times each one). Trying that, i'm using the query (and tblSection08.Record_ID instead of SDS_ID) for all subreports, except tblSection08_2 which is only a record and SDS_ID is working.

Attached the file.

Any ideas?

arnelgp · Sep 6, 2018

just delete 1 record from tblSection08

CosmaL · Sep 6, 2018

arnelgp

It worked!

Thank you very much!

arnelgp · Sep 6, 2018

goidluck friend...

Report/Subreport problem (1 Viewer)

CosmaL

Registered User.

isladogs

MVP / VIP

CosmaL

Registered User.

isladogs

MVP / VIP

CosmaL

Registered User.

isladogs

MVP / VIP

CosmaL

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

CosmaL

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

CosmaL

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread