junior nerd in trouble said:Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order at
all.
Thanks for your help...
Ken Snell said:Post the SQL statement of the query that isn't sorting as you want it to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
Ken Snell said:Change the query to SQL view. Copy the statement there, and paste it into a
message here.
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order at
all.
Thanks for your help...
Ken Snell said:Post the SQL statement of the query that isn't sorting as you want it to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to sort my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
junior nerd in trouble said:This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1, Count(tblLoans.EmployeeID) AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN tblEmployeeInformation ON
tblSite.CityID = tblEmployeeInformation.Site) ON tblSalesJob.SalesJobId =
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12]) AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City]) Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is Null) AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14]) Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
Ken Snell said:Change the query to SQL view. Copy the statement there, and paste it into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you want it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
Ken Snell said:Post some sample data and some sample results from the query. Let's see what
is going on.
Note also that the ! character should be replaced by . character in the
table.field references. For example, tblLoans!YSP should be tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1, Count(tblLoans.EmployeeID) AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN tblEmployeeInformation ON
tblSite.CityID = tblEmployeeInformation.Site) ON tblSalesJob.SalesJobId =
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12]) AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City]) Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is Null) AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14]) Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
Ken Snell said:Change the query to SQL view. Copy the statement there, and paste it into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you want it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
junior nerd in trouble said:Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April 2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April 2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00 April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April 2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April 2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
Ken Snell said:Post some sample data and some sample results from the query. Let's see
what
is going on.
Note also that the ! character should be replaced by . character in the
table.field references. For example, tblLoans!YSP should be tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1, Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12]) AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City]) Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is
Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14]) Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and paste it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to
sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
Ken Snell said:Your query uses a field named YSP from the Loan Table, but I don't see it in
your sample data?
Other than that, I do not see anything obvious in the posted information.
Take a look at the Properties of the query (open the query in design view,
right-click in the "table" area, and select Properties). Is there anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April 2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April 2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00 April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April 2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April 2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
Ken Snell said:Post some sample data and some sample results from the query. Let's see
what
is going on.
Note also that the ! character should be replaced by . character in the
table.field references. For example, tblLoans!YSP should be tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1, Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12]) AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City]) Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is
Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14]) Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and paste it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to
sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
junior nerd in trouble said:YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
Ken Snell said:Your query uses a field named YSP from the Loan Table, but I don't see it
in
your sample data?
Other than that, I do not see anything obvious in the posted information.
Take a look at the Properties of the query (open the query in design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April 2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00 April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April 2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query. Let's
see
what
is going on.
Note also that the ! character should be replaced by . character in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is
Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is
Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to
sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
Ken Snell said:Where do you change the name "Back" to "YSP"? Did you change the name of the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
Ken Snell said:Your query uses a field named YSP from the Loan Table, but I don't see it
in
your sample data?
Other than that, I do not see anything obvious in the posted information.
Take a look at the Properties of the query (open the query in design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April 2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00 April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April 2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query. Let's
see
what
is going on.
Note also that the ! character should be replaced by . character in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume, Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like [forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months) Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is
Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19]) Is
Null)
AND
((([tblMonths].[Months]) Like [forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1: Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try to
sort
my
query
by that expression (ascending or descending) it displays in no
descernable
order. Any body have any ideas?
junior nerd in trouble said:I found somewhere where it changes the shown label, but doesn't change the
actual name of the field.
Ken Snell said:Where do you change the name "Back" to "YSP"? Did you change the name of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I don't see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query. Let's
see
what
is going on.
Note also that the ! character should be replaced by . character in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is
Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try
to
sort
my
query
by that expression (ascending or descending) it displays in
no
descernable
order. Any body have any ideas?
Ken Snell said:Is that the Caption property of the field (design view of the table)? If
yes, then you should try your query after you change YSP back to Back in the
Avg calculation itself.
Avg(((tblLoans!Back-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:I found somewhere where it changes the shown label, but doesn't change the
actual name of the field.
Ken Snell said:Where do you change the name "Back" to "YSP"? Did you change the name of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I don't see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April 2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00 April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00 April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query. Let's
see
what
is going on.
Note also that the ! character should be replaced by . character in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year) Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR (((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6]) Is
Null)
AND
((([tblLoans].[Year]) Like [forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then try
to
sort
my
query
by that expression (ascending or descending) it displays in
no
descernable
order. Any body have any ideas?
junior nerd in trouble said:If I change YSP to Back... it can't find the field.
Ken Snell said:Is that the Caption property of the field (design view of the table)? If
yes, then you should try your query after you change YSP back to Back in
the
Avg calculation itself.
Avg(((tblLoans!Back-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
I found somewhere where it changes the shown label, but doesn't change
the
actual name of the field.
:
Where do you change the name "Back" to "YSP"? Did you change the name
of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I don't
see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in
design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April
2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00
April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00
April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other
fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query.
Let's
see
what
is going on.
Note also that the ! character should be replaced by . character
in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year)
Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR
(((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6])
Is
Null)
AND
((([tblLoans].[Year]) Like
[forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get
o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as
you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then
try
to
sort
my
query
by that expression (ascending or descending) it displays
in
no
descernable
order. Any body have any ideas?
Ken Snell said:OH - YSP is the real name, and Back is the Caption name, right?
I'm out of ideas at the moment for what might be interfering with the order.
So, if you can, create an example database file with sample data and the
query so that it shows the problem, and zip up and email it to me. (My
email address is obtained by removing this is not real from the reply email
address.) Include instructions on how to run the query, etc. so that I can
see the problem first-hand.
I'll take a look and see what I'm overlooking in my questions.
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:If I change YSP to Back... it can't find the field.
Ken Snell said:Is that the Caption property of the field (design view of the table)? If
yes, then you should try your query after you change YSP back to Back in
the
Avg calculation itself.
Avg(((tblLoans!Back-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
I found somewhere where it changes the shown label, but doesn't change
the
actual name of the field.
:
Where do you change the name "Back" to "YSP"? Did you change the name
of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I don't
see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in
design
view,
right-click in the "table" area, and select Properties). Is there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April
2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00
April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00 April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April 2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0 $198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April 2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00
April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00 April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other
fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query.
Let's
see
what
is going on.
Note also that the ! character should be replaced by . character
in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year)
Like
[forms]![frmAveragePricing]![text19]) AND ((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR
(((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like [forms]![frmAveragePricing]![combo6])
Is
Null)
AND
((([tblLoans].[Year]) Like
[forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there, and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I get
o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting as
you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I then
try
to
sort
my
query
by that expression (ascending or descending) it displays
in
no
descernable
order. Any body have any ideas?
junior nerd in trouble said:I could not figure out what you're email address was... but I got a step
closer to figuring out the problem. I went back to old 'version' of the
database, and found the sort feature to be working. So, I went about
making
it NOT work. It appears that when I classify the field as a 'decimal' and
not 'long integer,' the error in sorting occurs. Does that help you?
Ken Snell said:OH - YSP is the real name, and Back is the Caption name, right?
I'm out of ideas at the moment for what might be interfering with the
order.
So, if you can, create an example database file with sample data and the
query so that it shows the problem, and zip up and email it to me. (My
email address is obtained by removing this is not real from the reply
address.) Include instructions on how to run the query, etc. so that I
can
see the problem first-hand.
I'll take a look and see what I'm overlooking in my questions.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
If I change YSP to Back... it can't find the field.
:
Is that the Caption property of the field (design view of the table)?
If
yes, then you should try your query after you change YSP back to Back
in
the
Avg calculation itself.
Avg(((tblLoans!Back-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I found somewhere where it changes the shown label, but doesn't
change
the
actual name of the field.
:
Where do you change the name "Back" to "YSP"? Did you change the
name
of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I
don't
see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in
design
view,
right-click in the "table" area, and select Properties). Is
there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April
2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00
April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00
April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April
2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0
$198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April
2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00
April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00
April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other
fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query.
Let's
see
what
is going on.
Note also that the ! character should be replaced by .
character
in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths
INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year)
Like
[forms]![frmAveragePricing]![text19]) AND
((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR
(((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like
[forms]![frmAveragePricing]![combo6])
Is
Null)
AND
((([tblLoans].[Year]) Like
[forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there,
and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I
get
o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting
as
you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I
then
try
to
sort
my
query
by that expression (ascending or descending) it
displays
in
no
descernable
order. Any body have any ideas?
Ken Snell said:Decimal as a data type is buggy in ACCESS. Do not use it.
Use Double if you need the ability to store fractional numbers.
--
Ken Snell
<MS ACCESS MVP>
junior nerd in trouble said:I could not figure out what you're email address was... but I got a step
closer to figuring out the problem. I went back to old 'version' of the
database, and found the sort feature to be working. So, I went about
making
it NOT work. It appears that when I classify the field as a 'decimal' and
not 'long integer,' the error in sorting occurs. Does that help you?
Ken Snell said:OH - YSP is the real name, and Back is the Caption name, right?
I'm out of ideas at the moment for what might be interfering with the
order.
So, if you can, create an example database file with sample data and the
query so that it shows the problem, and zip up and email it to me. (My
email address is obtained by removing this is not real from the reply
address.) Include instructions on how to run the query, etc. so that I
can
see the problem first-hand.
I'll take a look and see what I'm overlooking in my questions.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble" <[email protected]>
wrote in message
If I change YSP to Back... it can't find the field.
:
Is that the Caption property of the field (design view of the table)?
If
yes, then you should try your query after you change YSP back to Back
in
the
Avg calculation itself.
Avg(((tblLoans!Back-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I found somewhere where it changes the shown label, but doesn't
change
the
actual name of the field.
:
Where do you change the name "Back" to "YSP"? Did you change the
name
of
the
field in the table itself? Is there another query somewhere?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
YSP is Back. I just labeled it different for the end user.
There is nothing in the 'order by' property.
Any other ideas?
:
Your query uses a field named YSP from the Loan Table, but I
don't
see
it
in
your sample data?
Other than that, I do not see anything obvious in the posted
information.
Take a look at the Properties of the query (open the query in
design
view,
right-click in the "table" area, and select Properties). Is
there
anything
in the Order By property?
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Sample Data from the Loan Table...
LoanID EmployeeID Borrower Name Back Front Other Charges Loan
Amount MonthID Year
21313 Willy Wonka Moody, W. 100.60% 3.00% 0 $154,251.00 April
2005
21314 Me, Myself AndIrene De Lira, T. & L.
102.40% 2.20% 0 $139,891.00 April 2005
21315 Steven Siegal Mahana, Jr, K. 100.80% 3.00% 0 $81,356.00
April
2005
40019 Steven Siegal Schmidt, B. 101.00% 4.00% 0 $255,000.00
April
2005
40020 Willy Wonka Dunn, J. 104.80% 1.00% 0 $126,954.00 April
2005
40021 Me, Myself AndIrene Morton/Goff 100.80% 1.00% 0
$198,985.00
April
2005
40024 Willy Wonka Beck, R. 100.20% 3.00% 0 $116,340.00 April
2005
40026 Me, Myself AndIrene Burpo, K. 99.50% 3.00% 0 $115,525.00
April
2005
40027 Steven Siegal Martin, R. 101.50% 3.00% 0 $127,000.00
April
2005
Results from Query...
Employee Average Pricing Number of Loans Volume
Willy Wonka 4.20% 3 $16,639.00
Steven Siegal 4.43% 3 $21,557.00
Me, Myself AndIrene 2.97% 3 $12,905.00
Just so you know I'm not TOO far off.... Sorting by the other
fields
(number
of loans, volume) works fine. It's only when I sort by Avg...
:
Post some sample data and some sample results from the query.
Let's
see
what
is going on.
Note also that the ! character should be replaced by .
character
in
the
table.field references. For example, tblLoans!YSP should be
tblLoans.YSP
instead.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
This is the whole thing... I abbreviated earlier for
simplicity...
SELECT tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name] AS Employee,
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) AS Expr1,
Count(tblLoans.EmployeeID)
AS
CountOfEmployeeID,
Round(Sum(((tblLoans!YSP-1+tblLoans!Front)/1)*tblLoans![Loan
Amount]+tblLoans![Other Charges]),0) AS Volume,
Avg(tblLoans![Loan
Amount])
AS Expr2
FROM (tblSalesJob INNER JOIN (tblSite INNER JOIN
tblEmployeeInformation
ON
tblSite.CityID = tblEmployeeInformation.Site) ON
tblSalesJob.SalesJobId
=
tblEmployeeInformation.[Sales ID]) INNER JOIN (tblMonths
INNER
JOIN
tblLoans
ON tblMonths.MonthID = tblLoans.MonthID) ON
tblEmployeeInformation.[Employee
ID] = tblLoans.EmployeeID
WHERE (((tblSite.City) Like
[forms]![frmAveragePricing]![combo12])
AND
((tblSalesJob.[Sales Job Description]) Like
[forms]![frmAveragePricing]![combo6]) AND ((tblLoans.Year)
Like
[forms]![frmAveragePricing]![text19]) AND
((tblMonths.Months)
Like
[forms]![frmAveragePricing]![combo14])) OR
(((([tblSite].[City])
Like
[forms]![frmAveragePricing]![combo12]) Is Null) AND
((([tblSalesJob].[Sales
Job Description]) Like
[forms]![frmAveragePricing]![combo6])
Is
Null)
AND
((([tblLoans].[Year]) Like
[forms]![frmAveragePricing]![text19])
Is
Null)
AND
((([tblMonths].[Months]) Like
[forms]![frmAveragePricing]![combo14])
Is
Null))
GROUP BY tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
ORDER BY
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount])) DESC;
thanks again...
:
Change the query to SQL view. Copy the statement there,
and
paste
it
into
a
message here.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
Is this what you are asking for?
Employee: tblEmployeeInformation![First Name] & " " &
tblEmployeeInformation![Last Name]
Expr1:
Avg(((tblLoans!YSP-1+tblLoans!front)/1)+(tblLoans![Other
Charges]/tblLoans![Loan Amount]))
Then, as I said, I sort the Expr 1 by descending, and I
get
o
order
at
all.
Thanks for your help...
:
Post the SQL statement of the query that isn't sorting
as
you
want
it
to.
--
Ken Snell
<MS ACCESS MVP>
"junior nerd in trouble"
<[email protected]>
wrote in message
I have a query that uses the AVG expression. If I
then
try
to
sort
my
query
by that expression (ascending or descending) it
displays
in
no
descernable
order. Any body have any ideas?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.