format/group dates in cross tab query

[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

 

[John Viescas]

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)

 

[Pat Wright]

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

 

[John Spencer (MVP)]

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;

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)



.

 

[John Viescas]

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)

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;

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


.