Percentage calculation

[June7]

The domain aggregate will include all records in the calc unless there is filter criteria included in its WHERE CONDITION argument.



Since you reference the table without filter, all records are counted. Consider:

=DCount("[PatchNo]", "[tblPatch]", "PP_ID='" & [Enter Patch Pack Number] & "'")

If you don't care about showing raw details on the report, an alternative could be to do individual aggregate queries for each 'many' table then join those queries to the parent table. Each aggregate query would require same filter criteria. This is where reference to input on a form is nice. One input instead of multiple popups.

Also, subreports is still an option to consider. Again, have the subreport RecordSource reference control on form for filter parameter.

[CS_10]

The domain aggregate will include all records in the calc unless there is filter criteria included in its WHERE CONDITION argument.

Since you reference the table without filter, all records are counted. Consider:

=DCount("[PatchNo]", "[tblPatch]", "PP_ID='" & [Enter Patch Pack Number] & "'")

If you don't care about showing raw details on the report, an alternative could be to do individual aggregate queries for each 'many' table then join those queries to the parent table. Each aggregate query would require same filter criteria. This is where reference to input on a form is nice. One input instead of multiple popups.

Also, subreports is still an option to consider. Again, have the subreport RecordSource reference control on form for filter parameter.

Sorry, you've lost me .
How do you create individual aggregate queries and then join them to the parent table?

As you can tell, I'm pretty new and self taught.

I will happily send you a copy of the DB if you would like to see it...

[June7]

Here is another thread with similar issue https://www.accessforums.net/access/...ent-42292.html. See post 4 in particular.

You may attach db to a post if you want. Follow instructions at bottom of my post.

[CS_10]

Please find attached the db.
There is the front end, and the back end, you will just need to re-link the tables again in the Front end.

The bits I'm struggling with are:

rptPPTestProgress

And this is linked to the tables tblPatch, tblPatch_Application and tblRisk.

Any assistance would be so helpful.

Many thanks in advance,
Craig

DB.zip

[CS_10]

Hi there.

Not sure if you've had chance to look into the database yet, but I have been playing around a bit more, and got a tiny bit further.

I have created a new query with all of the fields from the 3 tables (tblPatch, tblRisk and tblPatchApplication).

[Enter PP] was entered as a filter criteria on the PP_ID field, and I also created is as a parameter.

I have double clicked the relationship lines for tblRisk and tblPatchApplication and chose to include All for tblPatch, and only those from tblRisk (and tblPatchApplication) where the joint fields are the same.

I have changed the DCount parts slightly, and for the information in the patch table, this is now working correctly.

For the number of records in tblPatch, this now works and filters properly based on PP_ID.
My DCount is:
=DCount("[PatchNo]","[tblPatch]","PP_ID='" & [Enter PP] & "'")

When doing a DCount for information on the tblRisk or tblPatchApplication tables, this is where I'm having trouble again.

=DCount("[tblPatchApplication]![PA_ID]","[tblPatchApplication]","PP_ID='" & [Enter PP] & "'") - this doesn't work.

I need some way of specifying in the DCount the join I think...

Does this make sense?

[June7]

I don't see attempts at any of the suggested methods (aggregate queries or domain aggregate function or report with subreports). What objects should I look at?

I notice tblPassword is in frontend - why?

Domain aggregate functions cannot specify a join. They can only reference table or query objects. So would have to build that accomplishes the join and save that query. Then the function can refer to that query to pull data.

[CS_10]

Hi there.

Thanks for looking at the DB.

The domain aggregate functions have been used in rptPPTestProgress.

I have since slightly changed it based on what you have put in your reply.

I have now made a query with the 3 tables (tblPatch, tblPatchApplication and tblRisk). I have added all of the fields from the 3 tables. I've made sure the joins are ok (which I think they are). I have entered the [Enter PP] against PP_ID in tblPatch. Regarding the joints in this query, they are just created automatically aren't they as it knows they are linked in the relationship?

So, this rptPPTestProgress now uses this query as its Record Source.

The information from the tblPatch table seems to work ok now, I have used this as the control source for the number of rows in tblPatch and it filters correctly on [Enter PP].

=DCount("[PatchNo]","[tblPatch]","PP_ID='" & [Enter PP] & "'")

However, I'm really struggling to get information from the other two tables (that I believe are joined in the query).

I've used

=DCount("[tblPatchApplication]![PA_ID]","[tblPatchApplication]","[PP_ID]='" & [Enter PP] & "'")

=DCount("[qryTestingSummary]![PA_ID]","[qryTestingSummary]","[PP_ID]='" & [Enter PP] & "'")

But I can't seem to get the value. Can you see what is wrong in the control sources above?

It might be best if I attach a newer version for you to take a look at to see what I mean? Do you think thats ok?

Regarding the tblPassword, this is unused, I attempted to Password protect areas, but failed. I will remove it from the frontend.
This might be the next area that I am asking for help with....

[June7]

Aren't tblPatchApplication and tblRisk both a 'many' side of relationship with tblPatch? Cannot include both tables in the query and achieve the correct summary data. This is why I suggested an aggregate query each for tblPatchApplication and tblRisk or report/subreports.

[CS_10]

Hi there,
Many thanks for the reply.

Yes they are both a many side of the relationship with tblPatch.

I don't know how to make an aggregate query for each table, and then link to tblPatch.........could you tell me?

[June7]

Basic Access functionality. Use the query designer. Access help has guidelines or search web.

Open a query in design view, pull in table, click Totals on the ribbon, set fields as appropriate in the field grid. Save the query. Build another query that uses saved queries and/or table(s). Again, use query builder.

[CS_10]

Hi there,
Really appreciate your help with this and sorry that I'm not quite as knowlegable as I should be.

I understand about building the separate queries, but the bit I don't get is the linking to tblPatch.

Do I just need to build 3 queries.

1 for tblRisk with every field in the table, totals clicked and 'count' set against Risk_ID. Group By on the rest of the fields.
1 for tblPatchApplication with every field in the table, totals clicked and 'count' set against PA_ID. Group by on the rest of the fields.

Then finally, the query that joins them together... that's what I don't get.

Once that is done, what expression would I need to put on the item on the report.

Would something like =DCount("[Risk_ID]","[tblRisk]","PP_ID='" & [Enter PP] & "'") work?

[June7]

3 queries

Two aggregate queries and they will not include every field - only the fields needed to set the grouping and the field to summarize and field(s) for filter criteria if desired.

One query that joins the aggregate queries to tblPatch. Open query builder, pull in the table and the two queries. Create a link between each query and the table (click on key field in query and drag to key field of table). Pull fields down to the field grid.

If you use the aggregate query approach, DCount should not be needed. Use: =Count("*")

[CS_10]

Thanks, will give it a go on Monday.
I have no access to the database until then.

I just can't get my head around how that will work with the filter I want to apply.

Would I add the filter criteria to the 3rd query for [Enter PP]? and that would work when doing counts on the other tables.

So say PP_ID = 5.

I enter 5 when the criteria is asked for.

If I do =Count(Risk_ID), this will return all risks, where PP_ID (from the linked tblPatch) also = 5.
Is that right?

[June7]

Depends what you want to filter and what level the data is aggregated.

If the grouping criteria is the PP_ID field then those summary records can be filtered for specific PP_ID value(s).

If the raw data must be filtered (such as date range or employeeID) then that filter criteria will have to be in both aggregate queries.

[CS_10]

Hi there,
Thanks for the reply again.

I have had a go to no avail, if you wouldn't mind, could you see what I have done wrong in the new version of the db attached?

I have created 3 queries, these are qryApplicationAggregate, qryRiskAggregate and qryAggregateJoin.

I have made a new report with just 3 fields on. The report is rptSummaryNew.

I have literally just tried to add 3 counts to the report.

1 to count the number of records from tblPatch within the [Enter PP] criteria.
1 to count the number of records from tblPatchAPplication within the [Enter PP] criteria
1 to count the number of records from tblRisk within the [Enter PP] criteria.

Please could you see what I have done wrong?

After getting these 3 counts working, I then want to be able to add further stats, such as:

Number of records in tblPatchApplication where Tested_In_PP = Yes
and
Number of records in tblPatchApplication where Tested_In_PP = No

And still keep the [Enter PP] criteria from the search.

So grateful as I keep saying!