Crosstab/Subquery Question???

  • Thread starter Thread starter Deville
  • Start date Start date
D

Deville

Hello All,
I would like to know if it is at all possible 2 use a
subquery as the basis 4 the column heading portion of a
crosstab query. The reason is, the filters that apply r
causing only certain State fields 2 show. I would like to
have the State show w/o having 2 hard code them N2 the
Column Headings field N the Query properties.
Here is a sample of the SQl that Im using.

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM Table1
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT (SELECT State FROM tblBASE GROUP BY State;);
 
Is there a relationship between tblBase and Table1 - on something that gives
you the state? The normal way to force all values is to use an outer join -
something like:

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM tblBASE LEFT JOIN Table1
ON tblBASE.State = Table1.State
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT tblBASE.State;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
The relationship would be set by the State Field.

-----Original Message-----
Is there a relationship between tblBase and Table1 - on something that gives
you the state? The normal way to force all values is to use an outer join -
something like:

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM tblBASE LEFT JOIN Table1
ON tblBASE.State = Table1.State
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT tblBASE.State;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello All,
I would like to know if it is at all possible 2 use a
subquery as the basis 4 the column heading portion of a
crosstab query. The reason is, the filters that apply r
causing only certain State fields 2 show. I would like to
have the State show w/o having 2 hard code them N2 the
Column Headings field N the Query properties.
Here is a sample of the SQl that Im using.

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM Table1
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT (SELECT State FROM tblBASE GROUP BY State;);
Click to expand...


.
Click to expand...
 
OK. Then my suggested syntax should work. You'll get all States from
tblBase and any matching from Table1. If tblBase contains multiple rows per
state, you might want to consider creating a separate table of the states
you want or use a DISTINCT query on tblBase.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Deville said:
The relationship would be set by the State Field.

-----Original Message-----
Is there a relationship between tblBase and Table1 - on something that gives
you the state? The normal way to force all values is to use an outer join -
something like:

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM tblBASE LEFT JOIN Table1
ON tblBASE.State = Table1.State
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT tblBASE.State;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello All,
I would like to know if it is at all possible 2 use a
subquery as the basis 4 the column heading portion of a
crosstab query. The reason is, the filters that apply r
causing only certain State fields 2 show. I would like to
have the State show w/o having 2 hard code them N2 the
Column Headings field N the Query properties.
Here is a sample of the SQl that Im using.

TRANSFORM First(Value) AS [Value]
SELECT Section_Sort, Section, Value AS [Values]
FROM Table1
WHERE (((Section_Sort)=4) AND ((Date)=GetEdate()) AND
((Segment) In ('BLG','COML')))
GROUP BY Section_Sort, Section, Value
PIVOT (SELECT State FROM tblBASE GROUP BY State;);
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top