Changing Query Layouts

[Tom]

Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

 

[Michel Walsh]

Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP

 

[Duane Hookom]

At least one other reply in another news group. Please don't post to
multiple news groups.

--
Duane Hookom
MS Access MVP
--

Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP

Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

 

[Tom]

Hi,

I am struggling to get the SQL Code correct here in order
to do this. Sorry, I haven't really used Crosstab queries
before and my SQL knowledge isn't great. Currently, the
query is set up as thus:

Part No Apr Sales May Sales Jun Sales
ZZZ1 10 10 10
ZZZ2 10 10 10

I want to change this to the following:

Month Part No. ZZZ1 Part No ZZZ2
Apr Sales 10 10
May Sales 10 10
Jun Sales 10 10

I know I only need to vary the code you gave me below
accordingley, but I'm not sure how to do it, and
everything I am trying seems to be generating errors!

I appreciate your help on this.

Tom

-----Original Message-----
Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP

Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

.

 

[Tom]

Sorry! I hadn't meant to post to different newsgroups, I
just thought I had put my original post in the wrong one
and didn't know if I could delete it off.

I have tried the Union query you suggested, but with no
success. Is it easier to do it as a Union query rather
than a Crosstab? I have very limited SQL knowledge, and I
think that is what is causing the problem as I can't apply
the code you have given to my query, I just generate
errors.

I appreciate your help on this,
tom

-----Original Message-----
At least one other reply in another news group. Please don't post to
multiple news groups.

--
Duane Hookom
MS Access MVP
--

Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP

Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

.

 

[Michel Walsh]

Hi,


In this case, that is the reverse of what I proposed, but Duane seems to
have already supplied it: it is a UNION or a UNION ALL query that you need
(and you have to type it, since the graphical designer can't help for UNION
query) to normalize the data, and THEN, use a crosstab to denormalize it,
again.



Vanderghast, Access MVP

Hi,

I am struggling to get the SQL Code correct here in order
to do this. Sorry, I haven't really used Crosstab queries
before and my SQL knowledge isn't great. Currently, the
query is set up as thus:

Part No Apr Sales May Sales Jun Sales
ZZZ1 10 10 10
ZZZ2 10 10 10

I want to change this to the following:

Month Part No. ZZZ1 Part No ZZZ2
Apr Sales 10 10
May Sales 10 10
Jun Sales 10 10

I know I only need to vary the code you gave me below
accordingley, but I'm not sure how to do it, and
everything I am trying seems to be generating errors!

I appreciate your help on this.

Tom

-----Original Message-----
Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP

Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

.

 

[Duane Hookom]

Whenever you post something like "with no success", it should be followed
with what you did (in this case your sql) and what the resulting error was.

--
Duane Hookom
MS Access MVP

Sorry! I hadn't meant to post to different newsgroups, I
just thought I had put my original post in the wrong one
and didn't know if I could delete it off.

I have tried the Union query you suggested, but with no
success. Is it easier to do it as a Union query rather
than a Crosstab? I have very limited SQL knowledge, and I
think that is what is causing the problem as I can't apply
the code you have given to my query, I just generate
errors.

I appreciate your help on this,
tom

-----Original Message-----
At least one other reply in another news group. Please don't post to
multiple news groups.

--
Duane Hookom
MS Access MVP
--

Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP



Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top) and make
them rows in a table? For instance, I have Apr, May, June
etc. along the top with a Sales figure below it. How do I
create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom

.

 

[Tom]

Thanks for your patience Duane, I have it working
(eventually)! Thanks for your help though, it was very
much appreciated.

Tom

-----Original Message-----
Whenever you post something like "with no success", it should be followed
with what you did (in this case your sql) and what the resulting error was.

--
Duane Hookom
MS Access MVP

Sorry! I hadn't meant to post to different newsgroups, I
just thought I had put my original post in the wrong one
and didn't know if I could delete it off.

I have tried the Union query you suggested, but with no
success. Is it easier to do it as a Union query rather
than a Crosstab? I have very limited SQL knowledge, and I
think that is what is causing the problem as I can't apply
the code you have given to my query, I just generate
errors.

I appreciate your help on this,
tom

-----Original Message-----
At least one other reply in another news group. Please don't post to
multiple news groups.

--
Duane Hookom
MS Access MVP
--

Hi,


Use a crosstab query.


TRANSFORM SUM(Sales)
SELECT Null]
FROM MyTable
GROUP BY Null
PIVOT [Month]



Hoping it may help,
Vanderghast, Access MVP



Is it possible to change a query's layout? If so, how can
I take a query that has Coloumns (across the top)

and
make

them rows in a table? For instance, I have Apr,

May,
June

etc. along the top with a Sales figure below it.

How
do I

create a table that takes these column headings and
arranges them into one column entitled "Month"? e.g.

Month Sales Figure
Jan 1
Feb 1
Mar 1
Apr 1

Thanks for any help on this!

Tom




.

.