Help with Crosstab Query to transpose data

M

Mark

I have a huge database of stock market data. It is a list of daily prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has not
fixed the problem. The Sum Close query runs without the Close, but it only
sums the Close.

Any help would be appreciated

Thanks Mark
 
S

strive4peace

Hi Mark,

can you post the SQL you are using?

View, SQL from the menu in query design...

here is some info on SQL for crosstabs...

~~~~~~~~~~~ CROSSTAB ~~~~~~~~~~~

TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname3
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3
PIVOT By B.Fieldname1;

you can use equations to pivot (this will be column headings). For
instance, if you want the column headings to be year and month, you can
do this:

PIVOT Format([DateField],'yy-mm');

if you want monthnames in chronological, instead of alphabetical, order,
you can do this:

PIVOT Format([DateField],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")


If you also want a column that totals whatever you have in all the VALUE
columns (the expression after TRANSFORM), repeat your transform
expression after the SELECT keyword and the GROUPBY keywords

for instance:

TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname,
Count(B.Fieldname1) AS FieldAlias2
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3,
Count(B.Fieldname1)
PIVOT By B.Fieldname1;


'~~~~~~~~~~~~
here is an example:

*Sales*
DateSale, date
ItemID, long integer
Qty, integer

*Items*
ItemID, autonumber
Item, text

SQL:

'~~~~~~~~~~~~
TRANSFORM Sum(Qty) AS SumQty
SELECT
Item,
Sum(Qty) AS TotalQty
FROM Sales
INNER JOIN Items
ON Sales.ItemID = Items.ItemID
GROUP BY
Item,
Sum(Qty)
PIVOT By Format(DateSale,"yy-mm_mmm");
'~~~~~~~~~~~~

RESULTS:

'~~~~~~~~~~~~
Item TotalQty 06-01_Jan 06_02_Feb 06-03_Mar
Bells 15 5 6 4
Whistles 9 2 7
'~~~~~~~~~~~~

~~~~~~~~ ALIASES ~~~~~~~~

Using Aliases for tablenames (/querynames) makes the SQL easier to read.

For calculated fields, it is best to assign your own field alias instead
of letting Access use "expr1", "expr2", etc. Calculated fields MUST
have aliases.

an Alias follows the keyword AS

The SQL statement can be selected, copied, then pasted into Word for
formatting and printing (makes great wallpaper for your wall, not your
computer ;) as you are learning) or into Notepad to have as a popup
reference while you are working into design view, etc.


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

Guest

Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.
 
M

Mark

Many thanks Karl, I'll give it a go. I've never tried SQL in Access before,
so here's to my good luck.

Mark

KARL DEWEY said:
Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.

Mark said:
I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only
sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
 
M

Mark

Hi Crystal

Many thanks for your help. I've never tried SQL in Access before, so I'll
try a couple of things than post the SQL.

Mark


strive4peace said:
Hi Mark,

can you post the SQL you are using?

View, SQL from the menu in query design...

here is some info on SQL for crosstabs...

~~~~~~~~~~~ CROSSTAB ~~~~~~~~~~~

TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname3
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3
PIVOT By B.Fieldname1;

you can use equations to pivot (this will be column headings). For
instance, if you want the column headings to be year and month, you can do
this:

PIVOT Format([DateField],'yy-mm');

if you want monthnames in chronological, instead of alphabetical, order,
you can do this:

PIVOT Format([DateField],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")


If you also want a column that totals whatever you have in all the VALUE
columns (the expression after TRANSFORM), repeat your transform expression
after the SELECT keyword and the GROUPBY keywords

for instance:

TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname,
Count(B.Fieldname1) AS FieldAlias2
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3,
Count(B.Fieldname1)
PIVOT By B.Fieldname1;


'~~~~~~~~~~~~
here is an example:

*Sales*
DateSale, date
ItemID, long integer
Qty, integer

*Items*
ItemID, autonumber
Item, text

SQL:

'~~~~~~~~~~~~
TRANSFORM Sum(Qty) AS SumQty
SELECT
Item,
Sum(Qty) AS TotalQty
FROM Sales
INNER JOIN Items
ON Sales.ItemID = Items.ItemID
GROUP BY
Item,
Sum(Qty)
PIVOT By Format(DateSale,"yy-mm_mmm");
'~~~~~~~~~~~~

RESULTS:

'~~~~~~~~~~~~
Item TotalQty 06-01_Jan 06_02_Feb 06-03_Mar
Bells 15 5 6 4
Whistles 9 2 7
'~~~~~~~~~~~~

~~~~~~~~ ALIASES ~~~~~~~~

Using Aliases for tablenames (/querynames) makes the SQL easier to read.

For calculated fields, it is best to assign your own field alias instead
of letting Access use "expr1", "expr2", etc. Calculated fields MUST have
aliases.

an Alias follows the keyword AS

The SQL statement can be selected, copied, then pasted into Word for
formatting and printing (makes great wallpaper for your wall, not your
computer ;) as you are learning) or into Notepad to have as a popup
reference while you are working into design view, etc.


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
 
M

Mark

Many Thanks Karl

That worked well. However, I could not get the Format to work. I tried a
couple of approaches from the help file.
But without formatting it's just fine, anyway.

Mark

KARL DEWEY said:
Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.

Mark said:
I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only
sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
 
S

strive4peace

Hi Mark,

since you have used the reserved word, Date, as a fieldname, you might
try this:

PIVOT Format([Mark-Stock_Prices].[Date],"Short Date")

but that will do a character sort and not a chronoligcal sort... you can
Pivot without the Format function or set up the format code to be in
order of Year/Month/Day ... or specify the order for the column headings
(but then you have to know your data)


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Many Thanks Karl

That worked well. However, I could not get the Format to work. I tried a
couple of approaches from the help file.
But without formatting it's just fine, anyway.

Mark

KARL DEWEY said:
Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.

Mark said:
I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only
sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
Click to expand...
 
M

Mark

Many thanks

Yes, I'll give that a go.

Mark


strive4peace said:
Hi Mark,

since you have used the reserved word, Date, as a fieldname, you might try
this:

PIVOT Format([Mark-Stock_Prices].[Date],"Short Date")

but that will do a character sort and not a chronoligcal sort... you can
Pivot without the Format function or set up the format code to be in order
of Year/Month/Day ... or specify the order for the column headings (but
then you have to know your data)


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Many Thanks Karl

That worked well. However, I could not get the Format to work. I tried a
couple of approaches from the help file.
But without formatting it's just fine, anyway.

Mark

KARL DEWEY said:
Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.

:

I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only
sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome, Mark ;)

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Many thanks

Yes, I'll give that a go.

Mark


strive4peace said:
Hi Mark,

since you have used the reserved word, Date, as a fieldname, you might try
this:

PIVOT Format([Mark-Stock_Prices].[Date],"Short Date")

but that will do a character sort and not a chronoligcal sort... you can
Pivot without the Format function or set up the format code to be in order
of Year/Month/Day ... or specify the order for the column headings (but
then you have to know your data)


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Many Thanks Karl

That worked well. However, I could not get the Format to work. I tried a
couple of approaches from the help file.
But without formatting it's just fine, anyway.

Mark

Try this --
TRANSFORM First([Mark-Stock_Prices].Price) AS FirstOfPrice
SELECT [Mark-Stock_Prices].Code
FROM [Mark-Stock_Prices]
GROUP BY [Mark-Stock_Prices].Code
PIVOT Format([Date],"Short Date");

You will need to set the format for the Price as currency.

:

I have a huge database of stock market data. It is a list of daily
prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a crosstab query that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I gave a crosstab query a go following the suggestions of:

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified
expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has
not
fixed the problem. The Sum Close query runs without the Close, but it
only
sums the Close.

Any help would be appreciated

Thanks Mark
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to transpose date 4
SQL query help 6
Transpose - Help with crosstab query 7
To crosstab or not to crosstab ... That is the question 1
CROSSTAB QUERY- Display Rows even if there is no data 2
Find the first value in a crosstab query 11
Cross Tab Query with two value=-[Result] & " - " & [Error] as the 8
Crosstab Query by Week with Dates as Headers 5

Top