Transform Vertical Lines to horizontal please help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

catherine2255 - 28 Mar 2006 11:44 GMT

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

Reply to this Message

ashg657 - 28 Mar 2006 13:52 GMT

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.

Signature

Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
admin@ukliquid.co.uk

> Hi,
> I am having problems with my sql for my table, I have vertical figures
[quoted text clipped - 20 lines]
>
> Catherine

Reply to this Message

John Spencer - 28 Mar 2006 14:12 GMT

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

> Hi,
> I am having problems with my sql for my table, I have vertical figures
[quoted text clipped - 20 lines]
>
> Catherine

Reply to this Message

Duane Hookom - 28 Mar 2006 15:40 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

> Hi,
> I am having problems with my sql for my table, I have vertical figures
[quoted text clipped - 20 lines]
>
> Catherine

Reply to this Message

catherine2255 - 28 Mar 2006 16:01 GMT

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!

Reply to this Message