Percentage Queries

[AccessWillKill]

Hello,

I've been tasked in the last few months by my bosses to make a database for a client who has requested our database to be different from anything we usually create and also to take out percentages from the underlying data.

The client company also wants the source data (presumably to correlate with the reports created from the queries). While getting a percentage itself is not very difficult, my mind has completely froze on how to get this information out of it using reports and queries.

A major example of this is they would like the percentage of written responses our company takes on their behalf, between a set date. Oh they only want logs where the log has been confirmed and cleared. I.E. the case has been closed.

While i can input the criteria, you also need to count all other logs created between the time period and all the logs in the time period that are classed as 'written' logs. This gives you the numbers to do the percentage


so it would be:

count of closed 'written' logs(between the date period) / count of total closed logs (again between the date period) * 100 = the percentage i need


i've spent months in meeting after meeting constantly having to change things for this client, so if it is glaringly obvious what is needed to get these results from queries feel free to gloat and mock

cheers

 

[Galaxiom]

Derive extra fields in the report's recordsource query to indicate the log entry is confirmed and cleared or whatever other criteria are required.

Then add a control to the header or footer to do the calculation from the counts of the derived fields.

 

[AccessWillKill]

yeah i tried that, however the queried would only pick up on that criteria. i was missing the piece of the puzzle so to speak

i could get that there was so many written closed logs between the time period and use the reports to do the maths. However i couldn't for some reason get it to count the total amount of closed logs as well. Not in the same query anyway because it worked out off the criteria.

i'm not lying there is something i'm missing. I thought about maybe working off a union query maybe that is the answer, but i've never touched them before.

how would i be able to get the total logs that have been created between the same set period?

 

[Galaxiom]

The special criteria are not applied to the whole recordsource but individually to each record. The query criteria should return all records of interest that you want to display in the report, for example, those between two dates.

You can have as many derived fields in the recordsource as you need. Each record is allocated a value for each of these fields to reflect its status as determined by the set of criteria assigned to it.

For example:

=(IIF([somefield]=[Forms]![formname].[somecontrol], True, False)

Add as many conditions as required to the individual derived fields.

The count of the True on a field can be determined in a control in the header or footer with this ControlSource expression which uses the fact that True is stored as -1:

=Abs(Sum([booleanfield]))

It can alternatively be done using a numeric field that stores a value of some field (or a value derived from multiple fields) for True, or Null for False. For example the value of a sale might be stored for True while a Null is for no sale. Or the date of the contact. Anything you like.

This technique allows the results of two different criteria in the one field by storing a number or Null.

A Count() returns the count of successful contacts because the Nulls won't be counted. Sum, Avg, Max or Min can be used to return information about the values that were not Null. Note that the Null values will be completely ignored in the Avg.

So in our sale value example, the count would give the successful contacts while the Avg could give the average value of the successful contacts. Or with the date example the Max of the date gives the date of the last sale.

Note that these expressions in the control source are working with fields in the report's Recordset. It does not work with derived controls on the report.

 

[AccessWillKill]

i'm pretty sure i get what you mean. i shall give it a go and let you know.

cheers