Transform Vertical Lines to horizontal please help

[catherine2255]

Hi,
I am having problems with my sql for my table, I have vertical figures
which I want to put in horizontally in an sql query:

Original data

Name Amount Quantity
Cath 10 20

The problem I have is I need it to look like this:

Name Type Value
Cath Amount 10
Cath Quantity 20

I am trying the transform on SQL but I can only enter it once and then
it errors.
so far I have 2 queries one for amount and another for quantity then I
do a seperate Union query, but I want to keep the database smaller and
would like to know if its possible just to put this in one query.

thanks for your input!

Catherine

 

[Guest]

Crosstab query perhaps? In the database backend click "New" in the Queries
section and select Crosstab Query and follow the simple wizard. It might not
be 100% what you need but it will definitely help

HTH.

 

[John Spencer]

Dear Catherine,
You do realize that a select query is simply a set of instructions to select
and display records from a table. As a set of instructions, it has very
little impact on the size of the database.

Can you explain why you think your solution - using a union query - is
increasing the size of your database significantly?

John

 

[Duane Hookom]

Try:
SELECT [Name], "Amount" as Type, [Amount] as Value
FROM [Original Data]
UNION ALL
SELECT [Name], "Quantity", [Quantity]
FROM [Original Data];

BTW: name isn't a good name for any object in Access since every object has
a name property.

 

[catherine2255]

thanks a lot all for your help it worked great, Its not really the
size, its the number of queries I have setup. I always end up having
about 20 queries to get to each final report, and then when I make
changes it gets really confusing so now I only have 1 query instead of
3 which is brilliant!

Thanks again!