Crosstab Query question

[matrix7410]

Hello,
I have the following table in Access
Store Quarter Entertainment Dept. Computer Dept.
Movies Dept.
Best Buy 2007Q1 $1.3 million
$600,000 $400,000
Best Buy 2007 Q2 $1.6 million
$650,000 $350,000
Circut City 2007Q1 $1.0 million
$500,000 $300,000
Circut City 2007Q2 $1.2 million
$600,000 $400,000

How do I convert this to the following table in Access
Store Department 2007Q1 2007Q2
2007Q3
Best Buy Entertainment Dept. $1.3 mill $1.6 mill
Computer Dept. $....
$......
Movies Dept. $...
$....
Circuit City Entertainment Dept $.... $.....
Computer Dept. $....
$.....
Movies Dept. $...
$....

thanks!

 

[Duane Hookom]

The first step is to create a query that normalizes your table:
SELECT Store, Quarter, [Entert..] As Amt, "Entertainment" as Dept
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Computer..], "Computers"
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Movies..], "Movies"
FROM tblNoName
--- others ---;

Then create a crosstab with Store and Department as the Row Headings,
Quarter as the Column Heading, and Sum(Amt) as the Value.

 

[matrix7410]

The first step is to create a query that normalizes your table:
SELECT Store, Quarter, [Entert..] As Amt, "Entertainment" as Dept
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Computer..], "Computers"
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Movies..], "Movies"
FROM tblNoName
--- others ---;

Then create a crosstab with Store and Department as the Row Headings,
Quarter as the Column Heading, and Sum(Amt) as the Value.

--
Duane Hookom
Microsoft Access MVP

Hello,
I have the following table in Access
Store         Quarter        Entertainment Dept.    Computer Dept.
Movies Dept.
Best Buy   2007Q1        $1.3 million
$600,000            $400,000
Best Buy   2007 Q2       $1.6 million
$650,000           $350,000
Circut City  2007Q1       $1.0 million
$500,000            $300,000
Circut City  2007Q2        $1.2 million
$600,000           $400,000

How do I convert this to the following table in Access
Store           Department                 2007Q1           2007Q2
2007Q3
Best Buy     Entertainment Dept.    $1.3 mill           $1.6 mill
                   Computer Dept.           $....
$......
                   Movies Dept.              $...
$....
Circuit City   Entertainment Dept      $....                 $.....
                   Computer Dept.           $....
$.....
                   Movies Dept.              $...
$....

thanks!- Hide quoted text -

- Show quoted text -

Hi Duane,
What's the first part of your solutions? Is it a visual basics code
that I need to enter. Also, how do you make it so that the name of
the store shows up only once?

 

[Duane Hookom]

As I stated "The first step is to create a query ". This is a union query
that you will need to enter in SQL view.

If you want to hide duplicate store names, do this in the report. Check the
properties of the text box for the answer.
--
Duane Hookom
Microsoft Access MVP

The first step is to create a query that normalizes your table:
SELECT Store, Quarter, [Entert..] As Amt, "Entertainment" as Dept
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Computer..], "Computers"
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Movies..], "Movies"
FROM tblNoName
--- others ---;

Then create a crosstab with Store and Department as the Row Headings,
Quarter as the Column Heading, and Sum(Amt) as the Value.

--
Duane Hookom
Microsoft Access MVP

Hello,
I have the following table in Access
Store Quarter Entertainment Dept. Computer Dept.
Movies Dept.
Best Buy 2007Q1 $1.3 million
$600,000 $400,000
Best Buy 2007 Q2 $1.6 million
$650,000 $350,000
Circut City 2007Q1 $1.0 million
$500,000 $300,000
Circut City 2007Q2 $1.2 million
$600,000 $400,000

How do I convert this to the following table in Access
Store Department 2007Q1 2007Q2
2007Q3
Best Buy Entertainment Dept. $1.3 mill $1.6 mill
Computer Dept. $....
$......
Movies Dept. $...
$....
Circuit City Entertainment Dept $.... $.....
Computer Dept. $....
$.....
Movies Dept. $...
$....

thanks!- Hide quoted text -

- Show quoted text -

Hi Duane,
What's the first part of your solutions? Is it a visual basics code
that I need to enter. Also, how do you make it so that the name of
the store shows up only once?

 

[matrix7410]

As I stated "The first step is to create a query ". This is a union query
that you will need to enter in SQL view.

If you want to hide duplicate store names, do this in the report. Check the
properties of the text box for the answer.
--
Duane Hookom
Microsoft Access MVP

The first step is to create a query that normalizes your table:
SELECT Store, Quarter, [Entert..] As Amt, "Entertainment" as Dept
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Computer..], "Computers"
FROM tblNoName
UNION ALL
SELECT Store, Quarter, [Movies..], "Movies"
FROM tblNoName
--- others ---;
Then create a crosstab with Store and Department as the Row Headings,
Quarter as the Column Heading, and Sum(Amt) as the Value.
--
Duane Hookom
Microsoft Access MVP
:
Hello,
I have the following table in Access
Store         Quarter        Entertainment Dept.    Computer Dept.
Movies Dept.
Best Buy   2007Q1        $1.3 million
$600,000            $400,000
Best Buy   2007 Q2       $1.6 million
$650,000           $350,000
Circut City  2007Q1       $1.0 million
$500,000            $300,000
Circut City  2007Q2        $1.2 million
$600,000           $400,000
How do I convert this to the following table in Access
Store           Department                 2007Q1           2007Q2
2007Q3
Best Buy     Entertainment Dept.    $1.3 mill           $1.6 mill
                   Computer Dept.           $....
$......
                   Movies Dept.              $...
$....
Circuit City   Entertainment Dept      $....                 $.....
                   Computer Dept.           $....
$.....
                   Movies Dept.              $...
$....
thanks!- Hide quoted text -
- Show quoted text -

Hi Duane,
What's the first part of your solutions?  Is it a visual basics code
that I need to enter.  Also, how do you make it so that the name of
the store shows up only once?- Hide quoted text -

- Show quoted text -

thanks! It works great.