Cross Tab Query and Date Part

[T Kelley]

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[Dennis]

Format([date_field],"m-yyyy")

 

[T Kelley]

Now how can the results display in appropriate date order. Right now October
shows first then Nov etc, I know it is sorting the text field, is there
something different i can do?

Format([date_field],"m-yyyy")

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[Dennis]

Add 2 more columns to your query, sort on them but don't show them.
Year(date_field)
Month(date_field)

Now how can the results display in appropriate date order. Right now October
shows first then Nov etc, I know it is sorting the text field, is there
something different i can do?

Format([date_field],"m-yyyy")

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[T Kelley]

Is there a particular function to use?


I guessed DatePart, but that didnt work here is the whole SQL view

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
ORDER BY DatePart("yyyy",[SC_DT]), DatePart("m",[SC_DT])
PIVOT Format([SC_DT],"mm/yy");

Add 2 more columns to your query, sort on them but don't show them.
Year(date_field)
Month(date_field)

Now how can the results display in appropriate date order. Right now October
shows first then Nov etc, I know it is sorting the text field, is there
something different i can do?

Format([date_field],"m-yyyy")

:

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[Dennis]

Try creating a select query of the data you require sorted in the order you
require and then build your crosstab on that.

Is there a particular function to use?


I guessed DatePart, but that didnt work here is the whole SQL view

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
ORDER BY DatePart("yyyy",[SC_DT]), DatePart("m",[SC_DT])
PIVOT Format([SC_DT],"mm/yy");

Add 2 more columns to your query, sort on them but don't show them.
Year(date_field)
Month(date_field)

Now how can the results display in appropriate date order. Right now October
shows first then Nov etc, I know it is sorting the text field, is there
something different i can do?

:

Format([date_field],"m-yyyy")

:

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[Dennis]

Forget my last post, it did not work. But after the query has run you can
select each column and move it to the correct place. Close the query and it
will prompt you to save the layout. When you run it again the next time, the
layout is remembered and sorted in the correct order.

Is there a particular function to use?


I guessed DatePart, but that didnt work here is the whole SQL view

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
ORDER BY DatePart("yyyy",[SC_DT]), DatePart("m",[SC_DT])
PIVOT Format([SC_DT],"mm/yy");

Add 2 more columns to your query, sort on them but don't show them.
Year(date_field)
Month(date_field)

Now how can the results display in appropriate date order. Right now October
shows first then Nov etc, I know it is sorting the text field, is there
something different i can do?

:

Format([date_field],"m-yyyy")

:

I know that i can show the Year of a date field by using
DatePart("yyyy",[date_field]) also month by using DatePart("mm,[date_field]).
But how can i show both Month and Year. My table of data spans multiple years.

Example 5/1/2008 should display as 5-2008 or something like that.

 

[Gary Walter]

Is there a particular function to use?


I guessed DatePart, but that didnt work here is the whole SQL view

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
ORDER BY DatePart("yyyy",[SC_DT]), DatePart("m",[SC_DT])
PIVOT Format([SC_DT],"mm/yy");

pmfbi

Hi T,

Probably simplest solution to get "text sort":

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
PIVOT Format([SC_DT],"yyyy/mm");

or DateSerial all to "1st of month" to get "date sort":

TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between #1/1/2007# And
#4/30/2008#))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
PIVOT DateSerial(Year([SC_DT]), Month([SC_DT]), 1);

good luck,

gary