Replies

Cheers Hans, i feel so foolish!

Thanks Hans, it is attached, do please excuse the naming conventions and the modelling, it is inherited. There is a one to one relation with trsmain1 and tranSTD1. When I select show all in trsmain1 in the query qmain it returns a message saying no records found yet there are clearly records in trsmain1 and some in the other table too. I am at a loss.
The password is Transfer

Many thanks, Darren.

Here is a version of the report that uses DCount to provide the summary counts.

Fantastic Hans, Cheers, Darren

You can specify multiple conditions like this:

=DCount(“rk”,”qRisks”, “crn='” & [crn] & “‘ AND rk=’stringexample'”)

It’s important to keep track of the single and double quotes – mismatches lead to syntax errors or to unexpected results.

Thanks Hans, I was getting the doubles and singles wrong!

In the earlier topics we ended up with a main report with two subreports in the detail section, i.e. the subreports are at the same level, immediately below the main report.
Now you’re mentioning a main report, a subreport within the main report and a sub-subreport within the subreport.
That’s what confuses me, and why you will need to provide much more and much clearer information.

Ok Hans, I’ll send a stripped down version once I return home.

Thanks again, Darren.

This is getting rather confusing; the descriptions seem to change each time.
Could you provide a clear, precise and comprehensive description instead of just a vague general one, and/or post a stripped down and zipped copy of the database, so that we get an idea of what you’re actually working with?

So sorry, this is the earlier post for which you gave me a fantastic answer that enabled to build the reports a I needed:

“I have a main table with say main ID, from this I have a many scheme table with schemeID. Scheme has two many to many relations with risks and checks. I have created appropriate junction tables for each. I have combined all these in one query to show all records.”

What I now wish to do is total the number of risks and checks at scheme level and then overall at the main level on the top parent report.

Hope this make sense.

Thank you, Darren.

Don’t use Parent if you’re on the main report. Use

=[SubReportName]![ControlName]

or

=[SubReportName]![SubSubReportName]![ControlName]

etc. You must use the name of the subreport as a control on its parent; this is not necessarily the same as the name of the source object in the database window.

Note: you should refer to a control in the report header or footer of the subreport; it doesn’t make sense to refer to a control in the detail section or in a group header or footer – the results would not be what you want.

Cheers Hans, Darren.

The main report should be bound to the Scheme table.
One of the subreports should be bound to the Checks table, and the other to the Risks table.

The Link Master Fields property should list the linking field(s) in the main report, in this example spk.
The Link Child Fields property should list the corresponding field(s) in the subreport, in this example spfk.

Thank you Hans for your patience.

This seems to be more like Hans, sorry for my lack of understanding. I have not manually linked reports previously, so if report 1 is the main and has spk as the scheme ID and then report 2 is one of the many to many with spfk as the scheme link, what do I actually put in the property link master and child? I have tried the ellipse builder but it doesn’t recognise an unbound report.

Cheers, Darren.

If you group the report by ID, you can place a hidden text box txtRunningCount in the group header or footer with Control Source set to =1 and Running Sum set to Over All.

In the report footer, place a text box with Control Source =[txtRunningCount]

Neat Hans, thanks, Darren.

Guys, I apologise for wasting time here, I hadn’t named my linking field correctly, I am so so sorry.

Thanks, Darren.

If you have specified the Link Child Fields and Link Master Fields properties correctly, both subforms should display the correct records automatically, without needing code.

What is frmJCheckSub?

Thanks Hans, it is form C in my example. The code was added by the wizard which I built the form with? As another point which has just ocurred to me, the second sub (C1) when doen by the wizard is not within the detail part of the second form(. It is in form A’s detail. I am attempting to add from C2 in A’s detail too since I want B to be a recurring form which Access doesn’t seem to allow within form B’s detail. I hope this makes sense.

Thanks

I interpret your question differently to Hans.
I think you need two levels of grouping… not three. Group by Client, then by Account, then put the transaction specific fields in the Details section.
For each of the Groupings create a Header, and put the fields specific to that level in the Header.

Thank you gents, I can see the ambiguity in my post. I did mean as John interpreted.

Thanks to you both and apologies for my delay.

Darren

Eternally obliged Hans

Thanks, D.

That’s fine Hans, will do nicely.

Regards and thanks, Darren.