format/group dates in cross tab query

  • Thread starter Thread starter Pat Wright
  • Start date Start date
P

Pat Wright

I am trying to run a crosstab query that groups on month
of the year. The dates are stored in the underlying
table in standard date format. I can format them
as "mmm/yy" but the underlying value is still dd/mm/yy,
so the crosstab gives me a value for each day not monthy.

Any help greatly appreciated,

Pat Wright
 
You must use the Format function to generate the field that you use for the
Column Heading. Post the SQL from your query if that doesn't solve it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Where do I use the format?

TRANSFORM Count(projections.LoanNumber) AS
CountOfLoanNumber
SELECT projections.LoanAgent, projections.appldate, Count
(projections.LoanNumber) AS [Total Of LoanNumber]
FROM projections
GROUP BY projections.LoanAgent, projections.appldate
PIVOT projections.refi;
Thank you again,

Pat Wright
 
Pardon me for jumping in. I think that John Viescas will tell you the following.

TRANSFORM Count(projections.LoanNumber) AS
CountOfLoanNumber
SELECT projections.LoanAgent,
Format(projections.appldate,"mmm/yy"),
Count(projections.LoanNumber) AS [Total Of LoanNumber]
FROM projections
GROUP BY projections.LoanAgent,
Format(projections.appldate,"mmm/yy")
PIVOT projections.refi;

Pat said:
Where do I use the format?

TRANSFORM Count(projections.LoanNumber) AS
CountOfLoanNumber
SELECT projections.LoanAgent, projections.appldate, Count
(projections.LoanNumber) AS [Total Of LoanNumber]
FROM projections
GROUP BY projections.LoanAgent, projections.appldate
PIVOT projections.refi;
Thank you again,

Pat Wright
-----Original Message-----
You must use the Format function to generate the field that you use for the
Column Heading. Post the SQL from your query if that doesn't solve it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
Click to expand...
 
Ayup! Thanks, John.

Just starting to get back in the newsgroups now that the book is done! <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John Spencer (MVP) said:
Pardon me for jumping in. I think that John Viescas will tell you the following.

TRANSFORM Count(projections.LoanNumber) AS
CountOfLoanNumber
SELECT projections.LoanAgent,
Format(projections.appldate,"mmm/yy"),
Count(projections.LoanNumber) AS [Total Of LoanNumber]
FROM projections
GROUP BY projections.LoanAgent,
Format(projections.appldate,"mmm/yy")
PIVOT projections.refi;

Pat said:
Where do I use the format?

TRANSFORM Count(projections.LoanNumber) AS
CountOfLoanNumber
SELECT projections.LoanAgent, projections.appldate, Count
(projections.LoanNumber) AS [Total Of LoanNumber]
FROM projections
GROUP BY projections.LoanAgent, projections.appldate
PIVOT projections.refi;
Thank you again,

Pat Wright
-----Original Message-----
You must use the Format function to generate the field that you use for the
Column Heading. Post the SQL from your query if that doesn't solve it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I am trying to run a crosstab query that groups on month
of the year. The dates are stored in the underlying
table in standard date format. I can format them
as "mmm/yy" but the underlying value is still dd/mm/yy,
so the crosstab gives me a value for each day not monthy.

Any help greatly appreciated,

Pat Wright


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top