Transpose Query Results

  • Thread starter Thread starter Jeefgeorge
  • Start date Start date
J

Jeefgeorge

I have a query that returns information for one project

Fields: Proj_Title Proj_No CIP_No Let_Date
Values: XYZ Street ST00-0000 99999 8/12/08

I would like to transpose this data in order to export into Excel

i.e.
Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08

This will act as a sort of header for another data for the project which is
imported into the same spreadsheet.
 
Try this ----
SELECT "Proj_Title" AS Title, Proj_Title AS Subject
FROM YourTable
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject
FROM YourTable;
 
Works for the transpose, but now it lists all the projects, previously the
query returned only one project - the project selected on a form

WHERE ((([General Project Information].[Project_No])=[Forms]![Add Line Item
Quantities]![Project_No]));
 
Works for the transpose, but now it lists all the projects, previously the
query returned only one project - the project selected on a form

WHERE ((([General Project Information].[Project_No])=[Forms]![Add Line Item
Quantities]![Project_No]));

KARL DEWEY said:
Try this ----
SELECT "Proj_Title" AS Title, Proj_Title AS Subject
FROM YourTable
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject
FROM YourTable                  
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject
FROM YourTable;
Click to expand...
"Jeefgeorge" wrote:
Click to expand...
Click to expand...

well, then include the WHERE clause at the end of each SELECT
statement...
 
Ok, so I did this in 2 steps....1st query returns the desired project
information, and the second transposes it....now my question is can i change
the order of the result

Currently is returns (alphabetical order)

CIP_No 99999
Let_Date 8/12/08
Proj_No ST00-0000
Proj_Title XYZ Street

I would like it to be in this order (the same order is selects the fields

Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08


Jeefgeorge said:
Works for the transpose, but now it lists all the projects, previously the
query returned only one project - the project selected on a form

WHERE ((([General Project Information].[Project_No])=[Forms]![Add Line Item
Quantities]![Project_No]));


KARL DEWEY said:
Try this ----
SELECT "Proj_Title" AS Title, Proj_Title AS Subject
FROM YourTable
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject
FROM YourTable;
Click to expand...
Click to expand...
 
Add a sort field --
SELECT "Proj_Title" AS Title, Proj_Title AS Subject, 1 AS Sort
FROM YourTable
ORDER BY Sort
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject, 2 AS Sort
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject, 3 AS Sort
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject, 4 AS Sort
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Jeefgeorge said:
Ok, so I did this in 2 steps....1st query returns the desired project
information, and the second transposes it....now my question is can i change
the order of the result

Currently is returns (alphabetical order)

CIP_No 99999
Let_Date 8/12/08
Proj_No ST00-0000
Proj_Title XYZ Street

I would like it to be in this order (the same order is selects the fields

Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08


Jeefgeorge said:
Works for the transpose, but now it lists all the projects, previously the
query returned only one project - the project selected on a form

WHERE ((([General Project Information].[Project_No])=[Forms]![Add Line Item
Quantities]![Project_No]));


KARL DEWEY said:
Try this ----
SELECT "Proj_Title" AS Title, Proj_Title AS Subject
FROM YourTable
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns information for one project

Fields: Proj_Title Proj_No CIP_No Let_Date
Values: XYZ Street ST00-0000 99999 8/12/08

I would like to transpose this data in order to export into Excel

i.e.
Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08

This will act as a sort of header for another data for the project which is
imported into the same spreadsheet.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Karl, worked great

KARL DEWEY said:
Add a sort field --
SELECT "Proj_Title" AS Title, Proj_Title AS Subject, 1 AS Sort
FROM YourTable
ORDER BY Sort
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject, 2 AS Sort
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject, 3 AS Sort
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject, 4 AS Sort
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Jeefgeorge said:
Ok, so I did this in 2 steps....1st query returns the desired project
information, and the second transposes it....now my question is can i change
the order of the result

Currently is returns (alphabetical order)

CIP_No 99999
Let_Date 8/12/08
Proj_No ST00-0000
Proj_Title XYZ Street

I would like it to be in this order (the same order is selects the fields

Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08


Jeefgeorge said:
Works for the transpose, but now it lists all the projects, previously the
query returned only one project - the project selected on a form

WHERE ((([General Project Information].[Project_No])=[Forms]![Add Line Item
Quantities]![Project_No]));


:

Try this ----
SELECT "Proj_Title" AS Title, Proj_Title AS Subject
FROM YourTable
UNION SELECT "Proj_No" AS Title, Proj_No AS Subject
FROM YourTable
UNION SELECT "CIP_No" AS Title, CIP_No AS Subject
FROM YourTable
UNION SELECT "Let_Date" AS Title, Let_Date AS Subject
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns information for one project

Fields: Proj_Title Proj_No CIP_No Let_Date
Values: XYZ Street ST00-0000 99999 8/12/08

I would like to transpose this data in order to export into Excel

i.e.
Proj_Title XYZ Street
Proj_No ST00-0000
CIP_No 99999
Let_Date 8/12/08

This will act as a sort of header for another data for the project which is
imported into the same spreadsheet.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top