What Kind Of Query Can I use?

  • Thread starter Thread starter tkosel
  • Start date Start date
T

tkosel

I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
 
You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;
 
Mr. Dewey,

I appreciate your taking the time to provide me with a possible solution.
However, I am having a very difficult time understanding it. Can you
possibly provide a few more words to clarify?

KARL DEWEY said:
You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;

--
KARL DEWEY
Build a little - Test a little


tkosel said:
I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
Click to expand...
Click to expand...
 
The transform combines two sets of data --
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
You must then use First on the Value.
If you need to do any math it must be performed in another query ahead of
the crosstab.
--
KARL DEWEY
Build a little - Test a little


tkosel said:
Mr. Dewey,

I appreciate your taking the time to provide me with a possible solution.
However, I am having a very difficult time understanding it. Can you
possibly provide a few more words to clarify?

KARL DEWEY said:
You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;

--
KARL DEWEY
Build a little - Test a little


tkosel said:
I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Mr. Dewey,

I understand. I have successfully built the crosstab query. (In fact two
of them.) The reason I need two crosstab queries is because I have two
fields that contain column headings. In building my crosstab query, if I try
to designate two fields as columns headers, it tells me that I can only use
one. Is there a way to use two fields as column headers?

KARL DEWEY said:
The transform combines two sets of data --
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
You must then use First on the Value.
If you need to do any math it must be performed in another query ahead of
the crosstab.
--
KARL DEWEY
Build a little - Test a little


tkosel said:
Mr. Dewey,

I appreciate your taking the time to provide me with a possible solution.
However, I am having a very difficult time understanding it. Can you
possibly provide a few more words to clarify?

KARL DEWEY said:
You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;

--
KARL DEWEY
Build a little - Test a little


:

I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Only one column heading but you can use data from two fields to put two
different values under one heading like in my post.
--
KARL DEWEY
Build a little - Test a little


tkosel said:
Mr. Dewey,

I understand. I have successfully built the crosstab query. (In fact two
of them.) The reason I need two crosstab queries is because I have two
fields that contain column headings. In building my crosstab query, if I try
to designate two fields as columns headers, it tells me that I can only use
one. Is there a way to use two fields as column headers?

KARL DEWEY said:
The transform combines two sets of data --
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
You must then use First on the Value.
If you need to do any math it must be performed in another query ahead of
the crosstab.
--
KARL DEWEY
Build a little - Test a little


tkosel said:
Mr. Dewey,

I appreciate your taking the time to provide me with a possible solution.
However, I am having a very difficult time understanding it. Can you
possibly provide a few more words to clarify?

:

You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;

--
KARL DEWEY
Build a little - Test a little


:

I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Mr. Dewey and others,

If I can only have one column heading, this does me no good. Perhaps I am
still missing the point. I have included below, the two crosstab queries I
have.

Query1

TRANSFORM First([5-LabelDataPassThroughQuery].LBL_VALUE) AS FirstOfLBL_VALUE
SELECT [5-LabelDataPassThroughQuery].COLLECT_TS AS [Collect Date/Time],
[5-LabelDataPassThroughQuery].PROD_NAME AS Poduct,
[5-LabelDataPassThroughQuery].SETUP_NAME AS Setup,
[5-LabelDataPassThroughQuery].PIECE_NMBR AS [Piece No]
FROM [5-LabelDataPassThroughQuery]
GROUP BY [5-LabelDataPassThroughQuery].COLLECT_TS,
[5-LabelDataPassThroughQuery].PROD_NAME,
[5-LabelDataPassThroughQuery].SETUP_NAME,
[5-LabelDataPassThroughQuery].PIECE_NMBR
ORDER BY [5-LabelDataPassThroughQuery].COLLECT_TS,
[5-LabelDataPassThroughQuery].PIECE_NMBR
PIVOT [5-LabelDataPassThroughQuery].LBL_NAME;

Query2

TRANSFORM First([4-RawDataPassThroughQuery].VAR_VALUE) AS [Value]
SELECT [4-RawDataPassThroughQuery].COLLECT_TS AS [Collect Date/Time],
[4-RawDataPassThroughQuery].PROD_NAME AS Poduct,
[4-RawDataPassThroughQuery].SETUP_NAME AS Setup,
[4-RawDataPassThroughQuery].STN_NAME AS [Station Name],
[4-RawDataPassThroughQuery].PIECE_NMBR AS [Piece No]
FROM [4-RawDataPassThroughQuery]
GROUP BY [4-RawDataPassThroughQuery].COLLECT_TS,
[4-RawDataPassThroughQuery].PROD_NAME,
[4-RawDataPassThroughQuery].SETUP_NAME, [4-RawDataPassThroughQuery].STN_NAME,
[4-RawDataPassThroughQuery].PIECE_NMBR
ORDER BY [4-RawDataPassThroughQuery].COLLECT_TS,
[4-RawDataPassThroughQuery].PIECE_NMBR
PIVOT [4-RawDataPassThroughQuery].CHAR_NAME;



In Query1, I get different numbers of columns based on the "Label Name"
field and each of those columns has the appropriate "Label Value".

In Query2, I get . I get different numbers of columns based on the
"Characteristic Name" field and each of those columns has the appropriate
"VAR_VALUE".

I don't think I can do this in one query, as I am limited to 1 column
heading field and I need two. What I really need to do is get some of the
fields produced in query1 and add them on to some of the fields in query2.
Sorry about the length of this post, but I am stick

===================================================

KARL DEWEY said:
Only one column heading but you can use data from two fields to put two
different values under one heading like in my post.
--
KARL DEWEY
Build a little - Test a little


tkosel said:
Mr. Dewey,

I understand. I have successfully built the crosstab query. (In fact two
of them.) The reason I need two crosstab queries is because I have two
fields that contain column headings. In building my crosstab query, if I try
to designate two fields as columns headers, it tells me that I can only use
one. Is there a way to use two fields as column headers?

KARL DEWEY said:
The transform combines two sets of data --
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
You must then use First on the Value.
If you need to do any math it must be performed in another query ahead of
the crosstab.
--
KARL DEWEY
Build a little - Test a little


:

Mr. Dewey,

I appreciate your taking the time to provide me with a possible solution.
However, I am having a very difficult time understanding it. Can you
possibly provide a few more words to clarify?

:

You probably want something like this --
Organization BMO LOG OM
E BILL JONES - 5
E CHARLIE BROWN - 5
E GEORGE BROWN - 5
E JIM FOX - 4
F JIM FOX - 4 JIM FOX - 5
E JOE SCHMO - 5
this data ---
FullName Jurisdiction Category Name NUM
JOE SCHMO E 5 BMO 1
BILL JONES E 5 BMO 2
CHARLIE BROWN E 5 LOG 3
JIM FOX F 4 LOG 4
JIM FOX E 4 OM 5
JIM FOX F 5 OM 6
GEORGE BROWN E 5 BMO 7
from this crosstab query ---
TRANSFORM First([FullName] & " - " & [Category]) AS Expr1
SELECT [Task Group Summary Query AT 4].Jurisdiction AS Organization
FROM [Task Group Summary Query AT 4]
GROUP BY [Task Group Summary Query AT 4].FullName, [Task Group Summary Query
AT 4].Jurisdiction
PIVOT [Task Group Summary Query AT 4].Name;

--
KARL DEWEY
Build a little - Test a little


:

I have two pass through queries that each provide raw data to a crosstab
query from a SQL Server Database.
Each crosstab query yeilds results as illustrated below.

Crosstab1 Query

ProductName StationName Date/Time Char1Value Char2Value etc.

Test Station1 01/01/2008 1.1 2.2
Test Station1 01/02/2008 1.0 2.8

The crosstab is on the Char#Value, and the number of columns will vary
everytime I run the query. In my crosstab, of course the Char#Value heading
is replaced by the Char#Value data. It works fine, as expected.

Crosstab2 Query

ProductName StationName Date/Time Label1Value Label2value etc.

Test Station1 01/01/2008 Test
Test Station1 01/02/2008 String Jon

This crosstab is on the Label#Value and the number of columns will vary
everytime I run the Query. In my crosstab, of course the Label#Value heading
is replaced by the Labelr#Value data. It works fine too.

What I actually want to see in the final query is:

Product Station Date/Time Char1Value Char2Value Label1Value Label2value
etc.

Test Station1 01/01/2008 1.1 2.2 Test
Test Station1 01/02/2008 1.0 2..8 String Jon

If I could have more than one column heading field in my crosstab query, I
could do it in one crosstab query. Does anyone have any idea how this could
be done? Basically what I am trying to do is combine data from two different
crosstab queries.

Any help or other suggestions would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top