Percentage calculation

[CS_10]

Hi there.
I'm trying to do something that I think should be simple, but I can't quite get it right.

I have a table in my database, tblPatch. The important fields are PatchNo (text), PP (text), TestedinPP (logical).

I want to produce on a report, the total number of records in tblPatch where PP = [Entered Value].
I also want to show, the total number of records in tblPatch where PP = [Entered Value] and TestedinPP = Yes.

I then want to take the two values above, and work out the percentage. So second value, divided by the first value, multiplied by 100.

Please can anybody help me do this?

Many many thanks in advance,



Craig

[June7]

I recommend input the dynamic [Entered Value] into a textbox on form.

If the report is filtered to only the records that meet the [Entered Value] parameter, then expressions to summarize would simply be:

=Count(*)

=Sum(IIf([TestedinPP]=True, 1, 0))

[CS_10]

Hi there,

Thank you very much for the reply.

Sorry but I don't know what you mean...

I've created a new report.

For the record source I've added the 3 fields from the tblPatch table, and then added [Enter PP] in the criteria of PP.

I've then created a new text box with =Count(*). This bit works correctly, it asks me to enter the PP, and returns the correct value.

I then add a second text box with =Sum(IIf([TestedinPP]=True, 1, 0)).

When I run the report, I am asked to enter the PP, and also asked to enter TestedinPP.

What have I done wrong, or is this not what you mean?

Thanks again

[June7]

Is TestedinPP field included in the report RecordSource?

[CS_10]

Is TestedinPP field included in the report RecordSource?

I found out what my problem was, I had a slight typo in the field name!

However, I'm getting one block per line in the table. I just want a summary. I have created the fields in the detail section of the report. Is this right?

So what I would really like to be able to do is provide a summary of certain bits of information from lots of different tables on to one access report.

Is this even possible?

Once I start adding other tables and fields to the record source, it starts altering the counts of the fields I created above.
What other way should I look at doing this?

Thanks.

[June7]

Those suggested calcs go in report or group header or footer section, not detail.

The counts are wrong probably because you are including several tables from the 'many' side of relationships. This won't work. Need to use subreports or Domain Aggregate functions (DCount, DSum, etc).

[CS_10]

Thank you very much.

Will look into DCounts and DSums.

Much appreciated.

[CS_10]

Hi again.

Been having a really good go at the domain aggregate functions and got some good stuff.
However I have one issue.

On my Form Control Source, I have included all of the fields that I want to work with and applied a criteria filter on field PP [Entered Value] from tblPatch, as described in the original post.

However, this filter doesn't seem to work... I just get everything.

All of the other tables are linked via the PatchNo field, so I would have thought that PP field from the table would come through?

I think what I'm trying to say is, I want the same [Entered Value] to be applied across all of my fields in the report, even in different (but Linked tables).
Can this be done?

[June7]

"applied across all of my fields in the report" doesn't really make sense. A filter criteria restricts records, doesn't matter if multiple tables/queries are included in the query.

Post the SQL statement of the query used as form RecordSource. Or provide db for analysis, follow instructions at bottom of my post. Indicate objects involved in issue.

[CS_10]

"applied across all of my fields in the report" doesn't really make sense. A filter criteria restricts records, doesn't matter if multiple tables/queries are included in the query.

Post the SQL statement of the query used as form RecordSource. Or provide db for analysis, follow instructions at bottom of my post. Indicate objects involved in issue.

Hi there,
Sorry I wasn't that clear.

Of the 3 tables I am using on the report, they are all linked to tblPatch and the PatchNo field.

Here is the SQL Statement of the query used as form RecordSource. If this doesn't help then I will look at providing the DB.

SELECT tblPatch.PatchNo, tblPatch.PP_ID, tblPatch.Passed, tblPatch.Release_Notes, tblPatch.Tested_in_PP, tblPatchApplication.PA_ID, tblPatchApplication.Result, tblPatchApplication.Patch_No, tblRisk.RiskID, tblRisk.PatchNo, tblRisk.RiskLevel, tblRisk.RiskMitigated FROM (tblPatch INNER JOIN tblPatchApplication ON tblPatch.PatchNo = tblPatchApplication.Patch_No) INNER JOIN tblRisk ON tblPatch.PatchNo = tblRisk.PatchNo WHERE (((tblPatch.PP_ID)=[Enter Patch Pack Number]));

Thanks so much for the help so far.

[June7]

Is PP_ID a text or number field?

I can't see anything wrong with the query. Did you mean this is RecordSource for report, not form?

I never use dynamic parameters in query. I use control on form for input of filter criteria and reference the control in WHERE argument of DoCmd.OpenReport.

[CS_10]

PP_ID is a text field linked to a lookup of values.

And sorry, yes this is all on a Report.

Am I trying to do something that you can't do on a report?

[June7]

It can be done for report, I just prefer not to.

I never set lookups in tables. This might be source of the issue. The value of PP_ID might not be what you think it is.

However, this doesn't explain why you get all records.

[CS_10]

No matter what I enter on the pop up for [Enter Patch Pack Number], I get exactly the same results.

I don't think I've done anything wrong on the expressions of the actual fields of the report.
Here is one of them.

This should just return the count of the number of records in tblPatch where the PP_ID matches what is entered:

=DCount("[PatchNo]","[tblPatch]")


This one should just return the number of records in the tblPatch_Application and should also take into account the PP_ID (even though this isn't part of the tblPatch_Application - is that right).

=DCount("[tblPatchApplication]![PA_ID]","[tblPatchApplication]")

[CS_10]

Oh, I did forget to add (don't think it is important though but you never know) - this is a split database, so I have a front end with the reports/queries/forms and a separate back end with my table structure.