Crosstab query will work one way but not the other

[Conan Kelly]

Hello all,

This is killing me. Our table has weekly data in it. I have 2 different versions of underlying queries that will return only the
data that has a weekly date closest to the last day of the month. Then I'm trying to create a crosstab query on each of these
queries. When I run a crosstab on the hokey one, all works fine. When I run the same crosstab query on the more complicated one, I
get the following error message: http://home.att.net/~ctbarbarin/files/crosstab_error_message.jpg

The first, hokey version of the underlying query is (named "qryAL_"):

SELECT [AL(3)].Date, [AL(3)].Market, [AL(3)].Product, [AL(3)].Count, [AL(3)].Average, [AL(3)].High, [AL(3)].Low
FROM [AL(3)]
WHERE (((Day([date]))>24)) OR ((([AL(3)].Date)=#4/24/1998#)) OR ((([AL(3)].Date)=#9/24/1999# Or ([AL(3)].Date)=#2/23/2001# Or
([AL(3)].Date)=#2/22/2002#)) OR ((([AL(3)].Date)=#11/24/2000#)) OR ((([AL(3)].Date)=#7/17/1998#)) OR ((([AL(3)].Date)=#9/24/2004#))
OR ((([AL(3)].Date)=#6/24/2005#)) OR ((([AL(3)].Date)=#12/9/2005#))
ORDER BY [AL(3)].Date;

The second, more complicated version of the underlying query is (NOTE: the table name was changed from AL(3) to AL_3, but the
crosstab error messages were exactly the same (except table name, of course) when run each way):

SELECT AL_3.Date, AL_3.Market, AL_3.Product, AL_3.Count, AL_3.Average, AL_3.High, AL_3.Low
FROM (SELECT MEDate from (SELECT (SELECT MAX([Date]) FROM [AL_3] as MonthEnd WHERE Month([AL_3].Date) = Month([MonthEnd].Date) and
Year([AL_3].Date) = Year([MonthEnd].Date)) AS MEDate FROM AL_3) as Step1 GROUP BY MEdate order by medate) AS Step2 LEFT JOIN AL_3 ON
Step2.MEDate = AL_3.Date
ORDER BY AL_3.Date, AL_3.Product;

These 2 queries will return almost the exact same results, the second one being more accurate than the first w/o having to manually
enter certain dates in the criteria.

The crosstab query I am trying to run on both of these is (adjusting underlying query or table name as necessary):

TRANSFORM Sum(qryAL_.Average) AS SumOfAverage
SELECT qryAL_.Date
FROM qryAL_
GROUP BY qryAL_.Date
PIVOT qryAL_.Product;

When ever I run this on the 1st underlying query, the results are just what I'm looking for. Every time I run it on the 2nd, more
accurate, more complicated, MORE DESIRABLE underlying query, I get the error message, linked to above, immediately. It doesn't
hesitate as if it were trying to execute any of the underlying subqueries.

Is there any way I can get this to work with the 2nd underlying query?

Thanks for any help anyone can provide,

Conan Kelly

 

[Gary Walter]

SELECT
AL_3.Date,
AL_3.Market,
AL_3.Product,
AL_3.Count,
AL_3.Average,
AL_3.High,
AL_3.Low
FROM
(SELECT
MEDate
from
(SELECT
(SELECT
MAX([Date])
FROM
[AL_3] as MonthEnd
WHERE
Month([AL_3].Date) = Month([MonthEnd].Date)
and
Year([AL_3].Date) = Year([MonthEnd].Date)) AS MEDate
FROM
AL_3) as Step1
GROUP BY
MEdate
order by
medate) AS Step2
LEFT JOIN
AL_3
ON
Step2.MEDate = AL_3.Date
ORDER BY
AL_3.Date,
AL_3.Product;

Observations...
you can take it or leave it...
just trying to help.....

1) you have used several Jet reserved words
for field names which requires them to be
bracketed -- but, any interior bracketing in
a subquery used in a FROM clause will break
the Jet query parser.

(interestingly, "Avg" is reserved in Jet,
but not "Average"...don't know about
non-Jet though; will assume this is all
being done within Jet)

2) crosstabs just hate being fed anything
with complicated subqueries -- sometimes you
just plain have to push the query results into a table,
then use that table in the crosstab.

Several (untested) possible alternatives....
(after changing "Date" to "WkDate"
and "Count" to "WkCount")

to return only the data that has a weekly date
closest to the last day of the month

A)

SELECT
t.WkDate,
t.Market,
t.Product,
t.WkCount,
t.Average,
t.High,
t.Low
FROM
AL_3 As t
WHERE
t.WkDate =
(SELECT
Max(q.WkDate)
FROM
AL_3 As q
WHERE
Month(q.WkDate) = Month(t.WkDate)
AND
Year(q.WkDate) = Year(t.WkDate))

B) divide and conquer (what I would do)

qryMaxMnthDate

SELECT
Month(q.WkDate) As qMnth,
Year(q.WkDate) As qYr,
Max(q.WkDate) As qMaxDate
FROM
AL_3 As q
Group By
Month(q.WkDate),
Year(q.WkDate);

qryForXTab

SELECT
t.WkDate,
t.Market,
t.Product,
t.WkCount,
t.Average,
t.High,
t.Low
FROM
qryMaxMnthDate As M
INNER JOIN
AL_3 As t
ON
M.qMaxDate = t.WkDate;

then your xtab would look like
(maybe your "average" means
something else, but I just don't
understand how the sum of some
averages is meaningful...)

TRANSFORM Sum(X.Average) AS SumOfAverage
SELECT X.WkDate
FROM qryForXTab As X
GROUP BY X.WkDate
PIVOT X.Product;


like I said before...just trying to help...
mean no offense with any comments...

 

[Conan Kelly]

Gary,

Thanks for your feedback.

First, no offense taken. Your reply was polite and very helpful. We are all hear to learn or teach and those who know shouldn't
feel guilty or feel like they are talking down to those of us who want to learn. Almost everyone here is very polite and just here
to learn or teach. Those here spreading the knowledge are not here to brag about how much they know or expect praise from those who
are learning or make fun of those of us trying to learn when we ask very elementary questions that should be common sense. Your
help is very much appreciated and in no way offensive.

Second, my original setup was a cross tab query based on 3 subqueries, somewhat like you mentioned at the bottom of your reply. The
first sub query was to return the date closest to month end: MEDate1: (SELECT MAX([Date]) FROM [AL(3)] as MonthEnd WHERE
Month([AL(3)].Date) = Month([MonthEnd].Date) and Year([AL(3)].Date) = Year([MonthEnd].Date)). But because it was complicated, I
could not group & order it. There was a record for each record in table in the resulting recordset returning the date closest to
the month end for that week. So my second query's only function was to group those dates. You showed me how to combine those first
two into one, less complicated query with your:

SELECT
Month(q.WkDate) As qMnth,
Year(q.WkDate) As qYr,
Max(q.WkDate) As qMaxDate
FROM
AL_3 As q
Group By
Month(q.WkDate),
Year(q.WkDate);

I did not realize that getting the date closest to month end could be done in this much more simple way. And this was actually the
cure of the problem. The cross tab didn't like my complicated 3 sub queries, but it ran just fine with your simplified 2
subqueries, and the subqueries ran a lot faster as well.

Third, just to give you some info to answer your questions: This table is data on comparable market interest rates on different
banking products. That is what the Average, High, and Low fields are. The company we received this data from may have used several
different financial institutions as well as federal/government interest rates on each of these products and just sent us the
Average, High and Low of each of these products. So in this table there are about 10 records for each week (1 record for each
product in that week). So when I was summing my Average in my cross tab, all I was really doing was returning that one value for
that specific date and product. If I returned the minimum or maximum of the Average, the results would have been the same. I just
needed some aggregate function to use because cross tab queries are always grouped.

Once again, thank you for all of your help. It is very much appreciated,

Conan Kelly

SELECT
AL_3.Date,
AL_3.Market,
AL_3.Product,
AL_3.Count,
AL_3.Average,
AL_3.High,
AL_3.Low
FROM
(SELECT
MEDate
from
(SELECT
(SELECT
MAX([Date])
FROM
[AL_3] as MonthEnd
WHERE
Month([AL_3].Date) = Month([MonthEnd].Date)
and
Year([AL_3].Date) = Year([MonthEnd].Date)) AS MEDate
FROM
AL_3) as Step1
GROUP BY
MEdate
order by
medate) AS Step2
LEFT JOIN
AL_3
ON
Step2.MEDate = AL_3.Date
ORDER BY
AL_3.Date,
AL_3.Product;

Observations...
you can take it or leave it...
just trying to help.....

1) you have used several Jet reserved words
for field names which requires them to be
bracketed -- but, any interior bracketing in
a subquery used in a FROM clause will break
the Jet query parser.

(interestingly, "Avg" is reserved in Jet,
but not "Average"...don't know about
non-Jet though; will assume this is all
being done within Jet)

2) crosstabs just hate being fed anything
with complicated subqueries -- sometimes you
just plain have to push the query results into a table,
then use that table in the crosstab.

Several (untested) possible alternatives....
(after changing "Date" to "WkDate"
and "Count" to "WkCount")

to return only the data that has a weekly date
closest to the last day of the month

A)

SELECT
t.WkDate,
t.Market,
t.Product,
t.WkCount,
t.Average,
t.High,
t.Low
FROM
AL_3 As t
WHERE
t.WkDate =
(SELECT
Max(q.WkDate)
FROM
AL_3 As q
WHERE
Month(q.WkDate) = Month(t.WkDate)
AND
Year(q.WkDate) = Year(t.WkDate))

B) divide and conquer (what I would do)

qryMaxMnthDate

SELECT
Month(q.WkDate) As qMnth,
Year(q.WkDate) As qYr,
Max(q.WkDate) As qMaxDate
FROM
AL_3 As q
Group By
Month(q.WkDate),
Year(q.WkDate);

qryForXTab

SELECT
t.WkDate,
t.Market,
t.Product,
t.WkCount,
t.Average,
t.High,
t.Low
FROM
qryMaxMnthDate As M
INNER JOIN
AL_3 As t
ON
M.qMaxDate = t.WkDate;

then your xtab would look like
(maybe your "average" means
something else, but I just don't
understand how the sum of some
averages is meaningful...)

TRANSFORM Sum(X.Average) AS SumOfAverage
SELECT X.WkDate
FROM qryForXTab As X
GROUP BY X.WkDate
PIVOT X.Product;


like I said before...just trying to help...
mean no offense with any comments...