Bad Query Results

R

Rick Campbell

The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP], Avg(Sales.ListPrice) AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
 
T

Tom Ellison

Dear Rick:

I'm not familiar with Kevin Kline's method but I really enjoyed him in
"Dave". Since it is a function I think I can guess what is happening.

I believe the function is not capable of performing twice in the same
query. However, a simple solution for you might be to make a second
copy of the function with a slightly different name. If there are any
module level variables used with this function you might need to place
the new copy of the funciton into a new module. Give the new copy of
the function a slightly different name, perhaps acbDMedian1. Use this
function for the second reference in your query instead of using the
same one twice.

This is just guess work on my part, but it may do the job for you.

There's a good query based method of medians as well that isn't so
tricky to use, but it would take more effort to learn given your
current situation. But we can discuss that too if you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
R

Rick Campbell

Tom,

Yeah, I liked "Dave" too. Kevin is a very multi-talented person!

The Kline function does work multiple times. I had it working before when I
had the main table hooked up to a lookup table. Alas, the lookup table link
had it's own problems so I had to remove that.

The median function has the same ability as dAvg. So, I believe, if dAvg can
work multiple times, so should the median function.

Then again, where in the sql am I using it multiple times? Do you mean
referring to SoldPrice and Sales? Hmmm? I don't need the median for sales.
Can't remember why that's in there. I think the function wouldn't work if it
weren't. I'll give that a try.

If you've got another, easier, method, bring it on! I'm just trying to
produce some relevant statistics so I can get on with my market trends
reports. Programming ain't my job, nor a vocation. Just something I need to
do.

Thanks for the "instant" response.

Rick

Tom Ellison said:
Dear Rick:

I'm not familiar with Kevin Kline's method but I really enjoyed him in
"Dave". Since it is a function I think I can guess what is happening.

I believe the function is not capable of performing twice in the same
query. However, a simple solution for you might be to make a second
copy of the function with a slightly different name. If there are any
module level variables used with this function you might need to place
the new copy of the funciton into a new module. Give the new copy of
the function a slightly different name, perhaps acbDMedian1. Use this
function for the second reference in your query instead of using the
same one twice.

This is just guess work on my part, but it may do the job for you.

There's a good query based method of medians as well that isn't so
tricky to use, but it would take more effort to learn given your
current situation. But we can discuss that too if you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP], Avg(Sales.ListPrice) AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Rick:

You're right - its only in there one time.

Without studying the function it's hard for me to tell. I also am not
familiar with the methods of writng new domain functions.

Sorry I couldn't help. Maybe I'll be fresher in the morning.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Yeah, I liked "Dave" too. Kevin is a very multi-talented person!

The Kline function does work multiple times. I had it working before when I
had the main table hooked up to a lookup table. Alas, the lookup table link
had it's own problems so I had to remove that.

The median function has the same ability as dAvg. So, I believe, if dAvg can
work multiple times, so should the median function.

Then again, where in the sql am I using it multiple times? Do you mean
referring to SoldPrice and Sales? Hmmm? I don't need the median for sales.
Can't remember why that's in there. I think the function wouldn't work if it
weren't. I'll give that a try.

If you've got another, easier, method, bring it on! I'm just trying to
produce some relevant statistics so I can get on with my market trends
reports. Programming ain't my job, nor a vocation. Just something I need to
do.

Thanks for the "instant" response.

Rick

Tom Ellison said:
Dear Rick:

I'm not familiar with Kevin Kline's method but I really enjoyed him in
"Dave". Since it is a function I think I can guess what is happening.

I believe the function is not capable of performing twice in the same
query. However, a simple solution for you might be to make a second
copy of the function with a slightly different name. If there are any
module level variables used with this function you might need to place
the new copy of the funciton into a new module. Give the new copy of
the function a slightly different name, perhaps acbDMedian1. Use this
function for the second reference in your query instead of using the
same one twice.

This is just guess work on my part, but it may do the job for you.

There's a good query based method of medians as well that isn't so
tricky to use, but it would take more effort to learn given your
current situation. But we can discuss that too if you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP], Avg(Sales.ListPrice) AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Campbell

Hi Tom,

Not only my wife, but my 2 and half year old daughter, are always dragging
me away from the computer. "Come on, Papa!"

If you want a look at the function, here it is in all its glory.

Option Compare Database
Option Explicit

Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function



Tom Ellison said:
Dear Rick:

You're right - its only in there one time.

Without studying the function it's hard for me to tell. I also am not
familiar with the methods of writng new domain functions.

Sorry I couldn't help. Maybe I'll be fresher in the morning.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Yeah, I liked "Dave" too. Kevin is a very multi-talented person!

The Kline function does work multiple times. I had it working before when I
had the main table hooked up to a lookup table. Alas, the lookup table link
had it's own problems so I had to remove that.

The median function has the same ability as dAvg. So, I believe, if dAvg can
work multiple times, so should the median function.

Then again, where in the sql am I using it multiple times? Do you mean
referring to SoldPrice and Sales? Hmmm? I don't need the median for sales.
Can't remember why that's in there. I think the function wouldn't work if it
weren't. I'll give that a try.

If you've got another, easier, method, bring it on! I'm just trying to
produce some relevant statistics so I can get on with my market trends
reports. Programming ain't my job, nor a vocation. Just something I need to
do.

Thanks for the "instant" response.

Rick

Tom Ellison said:
Dear Rick:

I'm not familiar with Kevin Kline's method but I really enjoyed him in
"Dave". Since it is a function I think I can guess what is happening.

I believe the function is not capable of performing twice in the same
query. However, a simple solution for you might be to make a second
copy of the function with a slightly different name. If there are any
module level variables used with this function you might need to place
the new copy of the funciton into a new module. Give the new copy of
the function a slightly different name, perhaps acbDMedian1. Use this
function for the second reference in your query instead of using the
same one twice.

This is just guess work on my part, but it may do the job for you.

There's a good query based method of medians as well that isn't so
tricky to use, but it would take more effort to learn given your
current situation. But we can discuss that too if you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy')
Click to expand...
=
'" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP],
Click to expand...
Avg(Sales.ListPrice)
AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy')
Click to expand...
=
'" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Rick,

PMFBI

Does your table "Sales" have another field "City,"
(I suspect not for you create an alias with that
same name)?

If answer is no, then shouldn't line:
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
Click to expand...

be:

Format$(Sales.SoldDate,'yyyy') & "' And CityUp = '" & Sales.CityUp & "' And

Again apologies for butting in (especially if I am wrong).

Gary Walter

Rick Campbell said:
The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP], Avg(Sales.ListPrice) AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
Click to expand...
 
R

Rick Campbell

Gary,

Hey, don't apologize! You hit it right on the head! I knew it was just
something really stupid on my part. You saved what's left of my hair!

Thank you!!!

Rick

Gary Walter said:
Hi Rick,

PMFBI

Does your table "Sales" have another field "City,"
(I suspect not for you create an alias with that
same name)?

If answer is no, then shouldn't line:
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "'
Click to expand...
And

be:

Format$(Sales.SoldDate,'yyyy') & "' And CityUp = '" & Sales.CityUp & "' And

Again apologies for butting in (especially if I am wrong).

Gary Walter

Rick Campbell said:
The hair is going, in large clumps!!!!!

Using a median module from Kevin Kline's book. The module has the same
syntax as dAvg. It works on the table as a whole and it works on date
subsets. But, it is not working when I add another subset. I'm doing
something wrong and I can't figure it out.

Here's the working sql:

SELECT DISTINCTROW Sales.Style, (Format$(Sales.SoldDate,'yyyy')) AS
SaleDate, Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And Style = '" & Sales.Style &
"'")),'$#,###') AS Median, Format$(Count(*),'#,###') AS Sales,
Format$(Avg(Sales.DaysOnMarket),'#,###') AS DOM,
Format$([Average]/[ListPrice],'##.#%') AS [SP/LP], Avg(Sales.ListPrice) AS
ListPrice
FROM Sales
GROUP BY Sales.Style, Format$(Sales.SoldDate,'yyyy'), Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Year(Sales.SoldDate)-1;

The following sql produces a median of $3,061 for each city:

SELECT DISTINCTROW Sales.Style, Sales.CityUp AS [City],
(Format$(Sales.SoldDate,'yyyy')) AS [Year],
Format$(Avg(Sales.SoldPrice),'$#,###') AS Average,
Format$(CDbl(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'yyyy') = '" &
Format$(Sales.SoldDate,'yyyy') & "' And City = '" & Sales.CityUp & "' And
[Style] = '" & Sales.Style & "'")),'$#,###') AS Median, Count(*) AS Sales,
Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS [SP/LP],
Avg(Sales.ListPrice) AS ListPrice
FROM Sales
GROUP BY Sales.Style, Sales.CityUp, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate)-1
ORDER BY Sales.Style, Sales.CityUp, Year(Sales.SoldDate)-1;

If anyone can tell me what I'm doing wrong, I'll love ya, man!!!!

TIA

Rick
Click to expand...
Click to expand...
 

Ask a Question

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.

Ask a Question

Similar Threads

Count not working 3
#error message in results 8
basMedian obtaining quarterly results 5
Sort by Month in a query 3
Query on Duration 1
Report Subtotals 3
Report Subtotals - Repost - Thanks! 3
Help With Report Subtotals 0

Top