Query Madness

[Guest]

I have 6 queries that are the same except for a calculation based on
criteria. Here is an example:

SELECT tblClassEntries.[Back#], tblHorse.HorseName,
tblClassEntries.RiderName, tblClassEntries.RiderAge,
tblClassEntries.[Class#], tblShowBill.V, qryEntriesPerClass2.FinalEntries,
tblClassEntries.PlaceJ1, (1-[PlaceJ1]+1) AS Points
FROM tblHorse INNER JOIN ((qryEntriesPerClass2 INNER JOIN tblClassEntries ON
qryEntriesPerClass2.[Class#] = tblClassEntries.[Class#]) INNER JOIN
tblShowBill ON (tblShowBill.[Class#] = tblClassEntries.[Class#]) AND
(qryEntriesPerClass2.[Class#] = tblShowBill.[Class#])) ON tblHorse.[Back#] =
tblClassEntries.[Back#]
WHERE (((tblShowBill.V)="V") AND ((qryEntriesPerClass2.FinalEntries)>=1 And
(qryEntriesPerClass2.FinalEntries)<=1) AND ((tblClassEntries.PlaceJ1)<>0));

I have crosstabbed each one to sum the points.
Example:
TRANSFORM Sum(qryVShowDefaultPoints1.Points) AS SumOfPoints
SELECT qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge,
Sum(qryVShowDefaultPoints1.Points) AS [Total Of Points]
FROM qryVShowDefaultPoints1
WHERE (((qryVShowDefaultPoints1.V)="V"))
GROUP BY qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge
PIVOT qryVShowDefaultPoints1.V;


In the tblShowBill, there are 4 columns, Class#, ClassName, A, and V. A
class is designated as A, V or A and V.

These queries work great at calculating the points. When I try to UNION the
6 cross tabs, I get an error that some of the queries are not returning the
same number of fields that are necessary to successfully execute a UNION.
The problem is coming from classes that don't have the V designation and
therefore no points.

Can anyone help me with this????
Thanks in advance.
DN

 

[Rob Oldfield]

You can specify the columns to return from a crosstab. If want the columns
returned to be A, V or 'A and V' then go to the design of the query, right
click the background somewhere and choose properties. Then enter "A";"V";"A
and V" into Column Headings. (Access would normally look after the speech
marks for you, but it'll get confused by the 'and' in this case.)

The downside of doing that is that if something other than one of those
entries turns up then it won't automatically generate a new column, but if
it's fixed then that's obviously not a problem.

I have 6 queries that are the same except for a calculation based on
criteria. Here is an example:

SELECT tblClassEntries.[Back#], tblHorse.HorseName,
tblClassEntries.RiderName, tblClassEntries.RiderAge,
tblClassEntries.[Class#], tblShowBill.V, qryEntriesPerClass2.FinalEntries,
tblClassEntries.PlaceJ1, (1-[PlaceJ1]+1) AS Points
FROM tblHorse INNER JOIN ((qryEntriesPerClass2 INNER JOIN tblClassEntries ON
qryEntriesPerClass2.[Class#] = tblClassEntries.[Class#]) INNER JOIN
tblShowBill ON (tblShowBill.[Class#] = tblClassEntries.[Class#]) AND
(qryEntriesPerClass2.[Class#] = tblShowBill.[Class#])) ON tblHorse.[Back#] =
tblClassEntries.[Back#]
WHERE (((tblShowBill.V)="V") AND ((qryEntriesPerClass2.FinalEntries)>=1 And
(qryEntriesPerClass2.FinalEntries)<=1) AND

I have crosstabbed each one to sum the points.
Example:
TRANSFORM Sum(qryVShowDefaultPoints1.Points) AS SumOfPoints
SELECT qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge,
Sum(qryVShowDefaultPoints1.Points) AS [Total Of Points]
FROM qryVShowDefaultPoints1
WHERE (((qryVShowDefaultPoints1.V)="V"))
GROUP BY qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge
PIVOT qryVShowDefaultPoints1.V;


In the tblShowBill, there are 4 columns, Class#, ClassName, A, and V. A
class is designated as A, V or A and V.

These queries work great at calculating the points. When I try to UNION the
6 cross tabs, I get an error that some of the queries are not returning the
same number of fields that are necessary to successfully execute a UNION.
The problem is coming from classes that don't have the V designation and
therefore no points.

Can anyone help me with this????
Thanks in advance.
DN

 

[Guest]

Thanks Rob. This worked after I figured out what I needed to put in the
Column Headings. The queries now return an equal number of columns and the
UNION works.
Thanks again for your help.
DN

You can specify the columns to return from a crosstab. If want the columns
returned to be A, V or 'A and V' then go to the design of the query, right
click the background somewhere and choose properties. Then enter "A";"V";"A
and V" into Column Headings. (Access would normally look after the speech
marks for you, but it'll get confused by the 'and' in this case.)

The downside of doing that is that if something other than one of those
entries turns up then it won't automatically generate a new column, but if
it's fixed then that's obviously not a problem.

I have 6 queries that are the same except for a calculation based on
criteria. Here is an example:

SELECT tblClassEntries.[Back#], tblHorse.HorseName,
tblClassEntries.RiderName, tblClassEntries.RiderAge,
tblClassEntries.[Class#], tblShowBill.V, qryEntriesPerClass2.FinalEntries,
tblClassEntries.PlaceJ1, (1-[PlaceJ1]+1) AS Points
FROM tblHorse INNER JOIN ((qryEntriesPerClass2 INNER JOIN tblClassEntries ON
qryEntriesPerClass2.[Class#] = tblClassEntries.[Class#]) INNER JOIN
tblShowBill ON (tblShowBill.[Class#] = tblClassEntries.[Class#]) AND
(qryEntriesPerClass2.[Class#] = tblShowBill.[Class#])) ON tblHorse.[Back#] =
tblClassEntries.[Back#]
WHERE (((tblShowBill.V)="V") AND ((qryEntriesPerClass2.FinalEntries)>=1 And
(qryEntriesPerClass2.FinalEntries)<=1) AND

I have crosstabbed each one to sum the points.
Example:
TRANSFORM Sum(qryVShowDefaultPoints1.Points) AS SumOfPoints
SELECT qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge,
Sum(qryVShowDefaultPoints1.Points) AS [Total Of Points]
FROM qryVShowDefaultPoints1
WHERE (((qryVShowDefaultPoints1.V)="V"))
GROUP BY qryVShowDefaultPoints1.[Back#], qryVShowDefaultPoints1.HorseName,
qryVShowDefaultPoints1.RiderName, qryVShowDefaultPoints1.RiderAge
PIVOT qryVShowDefaultPoints1.V;


In the tblShowBill, there are 4 columns, Class#, ClassName, A, and V. A
class is designated as A, V or A and V.

These queries work great at calculating the points. When I try to UNION the
6 cross tabs, I get an error that some of the queries are not returning the
same number of fields that are necessary to successfully execute a UNION.
The problem is coming from classes that don't have the V designation and
therefore no points.

Can anyone help me with this????
Thanks in advance.
DN