multiple join report

[Davide]

Hello everyone,
in the attached database of a chemical lab, an analysis is made of some tests. A test is performed on one parameter and produces one outcome. Each test can be performed using one or more methods (maximum 3). Each parameter may have 0 or more (2 or 3 maximum) thresholds, according to some regulations.


I have been able to write a query which extracts all the required data, given the analysis ID (you can try the query with analysis ID = 7). Now I need to create a report, where all fields of anaysis are listed in the heading. Under the heading, I would like to have a table, listing all parameters, the methods adopted to measure that parameter, the outcome, the thresholds and the corresponding regulations. I think it should look like the attached pic.
I do not want that fields are multiplied according to the cardinality of the join. For example, I created a report in which methods were listed more than once, depending on how many regulations are applied, even if such fields are independent. If I use two methods to measure one parameter, and two regulations are applied to that parameter, I get 4 records. Something which I do not want to happen. Can you give me any help on this? thanks

[June7]

Either set textbox HideDuplicates property to Yes or use VBA. Review http://allenbrowne.com/func-concat.html

[Davide]

Thanks a lot June, but the simple "hide duplicates" solution does not work.
I am trying the concatRelated function, but in my case the relationship between test and methods is n to m, so there is a cross-ref table in between (TestMethod)
Therefore I have no foreign key to give to the function ConcatRelated to select the methods. Any workaround for this?

[June7]

Not following. You said relationship is "n to m", did you mean "m to m"?

A query instead of table can be referenced.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Davide]

More precisely n to m, since the two can be different (and they usually are). I tried with the query but no success.
I attach here the DB. Thanks a lot for your support,

[June7]

What does "no success" mean - error message, wrong result, nothing happens?

A code module and procedure cannot have the same name. Change the module name to something like ConcatenateRelatedData.

All modules should have "Option Explicit" in header. Can set db to include this by default. From VBA Editor > Tools > Options > check Require Variable Declaration

Advise not to use popup input prompts in queries. These cannot be validated. Reference a control on form for user input. I don't use dynamic parameterized queries. I prefer to use VBA to build criteria and apply to form or report.

And now I see the db was attached to first post. Although I assume it did not have the ConcatRelated code attempt.

Not a valid expression:

=[Method].[Description]=ConcatRelated([Method]![Description],[certificate])

Try:

=ConcatRelated("[Method]![Description]", "[certificate]")

But you will see the same concatenated data in every record. What criteria do you want to use to filter? Perhaps this textbox should be in group header.

Set textbox CanGrow property to Yes.

[Davide]

But you will see the same concatenated data in every record. What criteria do you want to use to filter? Perhaps this textbox should be in group header.
Set textbox CanGrow property to Yes.

This is the problem I tried to address in my previous post. I do not know how to filter, because the relationship between test and methods is n to m. So there is a cross-reference table in the middle. I have no foreign key to use, as suggested in the article you referenced in your first post.
Also, I am not familiar with VBA unfortunately (and I use an italian version of Access). Is 'can grow' a property you can set from the Property window in Access, or only through VBA? Thanks

[June7]

CanGrow is a property of textbox that can be set in report design - yes, in Property Sheet.

If you want to show methods for each parameter, I don't think your data structure allows for that. The best can do is show the methods used in each analysis or each test. Since your report groups by analysis, consider:

Remove Methods and TestMethods tables from certificate query.

Build a query named AnalysisMethodDesc:
SELECT DISTINCT Method.Description, Test.Analysis
FROM Test INNER JOIN (Method INNER JOIN TestMethod ON Method.ID = TestMethod.Method) ON Test.ID = TestMethod.Test;

Textbox expression:
=ConcatRelated("Description","AnalysisMethodDesc", "Analysis=" & [ID],"",Chr(13) & Chr(10))

Move textbox to Code header. Make textbox wide enough to display longest description. The Chr(13) & Chr(10) will force a line return between each description.

Test table has field for parameter. A parameter can have multiple threshold records associated. How do you know which threshold applies - all of them?

Might find this function slows report performance. Only other option would be subreports.

[Davide]

Thanks June,
it all looks much better. Still, I do not understand why the report asks the analysis ID twice.
Yes, one parameter may have more thresholds according to different regulations, so the outcome of a test may be compliant according to one regulation and not to another. I have to show all thresholds that apply to that parameter, but I still can see duplicates in the report. I attach the new version.

[June7]

I forgot to mention the ID field in certificate query needs to be in SELECT. That means the Show checkbox needs to be checked.

You did not remove Methods and TestMethods tables from certificate query.

[Davide]

Thanks June, I am getting very close. But methods are related to a test (and therefore a parameter), not to the whole analysis, which is made of more tests.
(Looks like we are back to the initial problem, as there is a n to m relationship b/w test and method).
I changed the AnalysisMethodDesc to TestMethodDesc:

SELECT DISTINCT Test.Parameter, Method.DescriptionFROM Test INNER JOIN (Method INNER JOIN TestMethod ON Method.ID = TestMethod.Method) ON Test.ID = TestMethod.Test;

but this is not enough. Suggestions? Thanks

[June7]

Frankly, I am having a hard time understanding that requirement. But I will give it one more try.

Query: TestMethodParam
SELECT TestMethod.Test, TestMethod.Method, Method.Description, Parameter.ID, Parameter.Description
FROM (Parameter INNER JOIN Test ON Parameter.ID = Test.Parameter) INNER JOIN (Method INNER JOIN TestMethod ON Method.ID = TestMethod.Method) ON Test.ID = TestMethod.Test;

Query2: TestParamMethods
SELECT DISTINCT Test.Analysis, TestMethod.Test, Test.Parameter, ConcatRelated("Method.Description","TestMethodPara m","ID=" & [Parameter] & " AND Test=" & [Test],"",Chr(13) & Chr(10)) AS MetDesc
FROM Test INNER JOIN TestMethod ON Test.ID = TestMethod.Test;

Now include that query in certificate joining on Parameter and Test ID fields. Bind textbox to MetDesc. Put textbox in Parameter header.

Or instead of Query2, modify certificate query to include Test and Parameter ID fields. Bind textboxes in Parameter header to Test.ID and Parameter.ID. Name them tbxTest and tbxParam (can set not visible). Now expression in textbox:
=ConcatRelated("Method.Description","TestMethodPar am","ID=" & [tbxParam] & " AND Test=" & [tbxTest],"",Chr(13) & Chr(10))

Advise not to name multiple fields with same name "Description" - better would be MethDesc, ParamDesc. Do something similar with Parameter fields.


If you switch db to Overlapping Windows, can open tables side-by-side to more easily view data and analyze relationships. That helps me.

[Davide]

Thanks June, I adopted the second solution, with text boxes and works fine!
Only, I have not been able to align the thresholds with the outcome. They are in a lower group (the body) which is logically ok but esthetically unpleasent, and makes me waste space on the PDF print. Any suggestion on this?

[June7]

Not sure what you mean by 'align the thresholds with the outcome' - what's wrong with what you have?

Need to set Regulation and Destination textboxes CanGrow to yes.

[ssanfu]

I am not a fan of your naming convention (too many PK fields named "ID"), so here is my suggestion




Good luck with your project...