Show Records Below Average

[Guest]

I am fairly new to Access, but am learning quickly. I am trying to figure out a way to generate a custom report and could really use some assistance.

What I have gotten figured out so far

I have a query that generates a list of sellers and the total of their record from one table based on a selected date range. In the date field of the query I put the criteria "Between [StartDate] And [EndDate]", this gets me my list. The last column is a percentage based on two of the other fields

What I want to do

I want to create a report that will give me all the sellers information out of this list whos percentage is below the average. I first need to add up all the records in this list and use generate the percentage. This would be my average percentage. Then for the report I want to take the results of my query, and only select those records for the report whos value in the "percentage" column is below the average that I had to calculate

I know this is probably more complex that someone has a specific answer for, but if someone can show me the path to take to make this work, and remember I am new to this so if you can break down those steps down into failry simple ideas I would really appreciate it.

Thank you for all of your assistance, I have learned alot from reading this group already

R

 

[Marshall Barton]

I am fairly new to Access, but am learning quickly. I am trying to figure out a way to generate a custom report and could really use some assistance.

What I have gotten figured out so far:

I have a query that generates a list of sellers and the total of their record from one table based on a selected date range. In the date field of the query I put the criteria "Between [StartDate] And [EndDate]", this gets me my list. The last column is a percentage based on two of the other fields.

What I want to do:

I want to create a report that will give me all the sellers information out of this list whos percentage is below the average. I first need to add up all the records in this list and use generate the percentage. This would be my average percentage. Then for the report I want to take the results of my query, and only select those records for the report whos value in the "percentage" column is below the average that I had to calculate.


I know this is probably more complex that someone has a specific answer for, but if someone can show me the path to take to make this work, and remember I am new to this so if you can break down those steps down into failry simple ideas I would really appreciate it.

You can use an SQL statement in a field's criteria.

Use something like this as the criteria for the sales amoint
field in your query:

<(SELECT AVG(T.salesamount) FROM table As T
WHERE datefield Between [StartDate] And [EndDate])

If you have trouble getting that straight, post a copy/paste
of your query's SQL view.

 

[Guest]

Here is my SQL statement from my current query. It pulls all the information from "Report2" between two dates. In the column "Percent" I get a value. Then what I need is to get the averge based on just these records, and calculate the percent. Then on the report just show the value that are below this average number from this query

I tried using what you gave me but it just didn't seem to work, I am sure I jsut don't understand where I need to include this. One thing, is it calculating the precent based on the whole of the origional table "Report2", or on just the values generated from this query, I need the average percent from just the amounts in this query basically forcing it to only work with the data in my date range selection

SELECT DISTINCTROW SalesMen.ID, SalesMen.[First Name], SalesMen.[Last Name], Sum(Report2.SalesCount) AS [Sum Of SalesCount], Sum(Report2.Dollars) AS [Sum Of Dollars], Sum(Report2.Income) AS [Sum Of Income], Sum(Report2.[Loss]) AS [Sum Of Loss], IIf([Sum Of Dollars]=0,0,[Sum Of Imcome]/[Sum Of Dollars]) AS [Percent
FROM Screeners INNER JOIN Report2 ON SalesMenID = Report2.I
WHERE (((Report2.Date) Between [Start Date] And [Finish Date])
GROUP BY SalesMen.ID, SalesMen.[First Name], SalesMen.[Last Name]

How do I do this

Thank you for your help so far and it you have any other information I would appreciate it


----- Marshall Barton wrote: ----

RJ Leburg wrote

I am fairly new to Access, but am learning quickly. I am trying to figure out a way to generate a custom report and could really use some assistance.

What I have gotten figured out so far
I have a query that generates a list of sellers and the total of their record from one table based on a selected date range. In the date field of the query I put the criteria "Between [StartDate] And [EndDate]", this gets me my list. The last column is a percentage based on two of the other fields
What I want to do
I want to create a report that will give me all the sellers information out of this list whos percentage is below the average. I first need to add up all the records in this list and use generate the percentage. This would be my average percentage. Then for the report I want to take the results of my query, and only select those records for the report whos value in the "percentage" column is below the average that I had to calculate

I know this is probably more complex that someone has a specific answer for, but if someone can show me the path to take to make this work, and remember I am new to this so if you can break down those steps down into failry simple ideas I would really appreciate it

You can use an SQL statement in a field's criteria

Use something like this as the criteria for the sales amoin
field in your query

<(SELECT AVG(T.salesamount) FROM table As
WHERE datefield Between [StartDate] And [EndDate]

If you have trouble getting that straight, post a copy/past
of your query's SQL view

 

[Marshall Barton]

Here is my SQL statement from my current query. It pulls all the information from "Report2" between two dates. In the column "Percent" I get a value. Then what I need is to get the averge based on just these records, and calculate the percent. Then on the report just show the value that are below this average number from this query.

I tried using what you gave me but it just didn't seem to work, I am sure I jsut don't understand where I need to include this. One thing, is it calculating the precent based on the whole of the origional table "Report2", or on just the values generated from this query, I need the average percent from just the amounts in this query basically forcing it to only work with the data in my date range selection.


SELECT DISTINCTROW SalesMen.ID, SalesMen.[First Name], SalesMen.[Last Name], Sum(Report2.SalesCount) AS [Sum Of SalesCount], Sum(Report2.Dollars) AS [Sum Of Dollars], Sum(Report2.Income) AS [Sum Of Income], Sum(Report2.[Loss]) AS [Sum Of Loss], IIf([Sum Of Dollars]=0,0,[Sum Of Imcome]/[Sum Of Dollars]) AS [Percent]
FROM Screeners INNER JOIN Report2 ON SalesMenID = Report2.ID
WHERE (((Report2.Date) Between [Start Date] And [Finish Date]))
GROUP BY SalesMen.ID, SalesMen.[First Name], SalesMen.[Last Name];

I don't have your setup to test, but if I understand what
you want, this should do it:

. . .
WHERE (Report2.Date Between [Start Date] And [Finish Date])
AND (IIf(Sum(Report2.Dollars) = 0, 0,
Sum(Report2.Income) / Sum(Report2.Dollars)) <
(SELECT Avg(IIf(Sum(T.Dollars) = 0, 0,
Sum(T.Income) / Sum(T.Dollars)
FROM Report2 AS T
WHERE T.Date Between [Start Date] And [Finish Date])
GROUP BY . . .

If you do not want the overall average to include the ones
with 0 dollars, then change the IIf to
. . . Avg(IIf(Sum(T.Dollars) = 0, Null, . . .
--
Marsh
MVP [MS Access]

I am fairly new to Access, but am learning quickly. I am trying to figure out a way to generate a custom report and could really use some assistance.

What I have gotten figured out so far:
I have a query that generates a list of sellers and the total of their record from one table based on a selected date range. In the date field of the query I put the criteria "Between [StartDate] And [EndDate]", this gets me my list. The last column is a percentage based on two of the other fields.
What I want to do:
I want to create a report that will give me all the sellers information out of this list whos percentage is below the average. I first need to add up all the records in this list and use generate the percentage. This would be my average percentage. Then for the report I want to take the results of my query, and only select those records for the report whos value in the "percentage" column is below the average that I had to calculate.
I know this is probably more complex that someone has a specific answer for, but if someone can show me the path to take to make this work, and remember I am new to this so if you can break down those steps down into failry simple ideas I would really appreciate it.

----- Marshall Barton wrote: -----
You can use an SQL statement in a field's criteria.

Use something like this as the criteria for the sales amoint
field in your query:

<(SELECT AVG(T.salesamount) FROM table As T
WHERE datefield Between [StartDate] And [EndDate])

If you have trouble getting that straight, post a copy/paste
of your query's SQL view.