If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Column Heading Sort
I am writing a report where the user specifies the beginning date and I go
get the information for the twelve month period that begins with that date. Once I have the data I need to get a sum of hours worked for the month for each employee. The Data should look like this: Name 02/2004 03/2004 04/2004 ... Joe 1 3 Jane 3 2 I can accomplish this with a crosstab query but when I do Access displays the columns in order by an alphanumeric sort. IE: 1/2004 10/2004 11/2004 2/2004 I can't fix this in the queries properties because I have no idea what months I will be dealing with. Can anyone help with this? |
#2
|
|||
|
|||
Use relative column headings rather than actual dates. If the "user
specifies the beginning date " using a control [Forms]![frmDates]![txtBegDate], use a column heading expression like: ColHead: "M" & DateDiff("m",[Forms]![frmDates]![txtBegDate], [YourDateField]) Then set the column headings property to: "M0","M1","M2",.."M11" M0 will be results from the beginning date and M11 will be 11 months later. -- Duane Hookom MS Access MVP -- "MJatAflac" wrote in message ... I am writing a report where the user specifies the beginning date and I go get the information for the twelve month period that begins with that date. Once I have the data I need to get a sum of hours worked for the month for each employee. The Data should look like this: Name 02/2004 03/2004 04/2004 ... Joe 1 3 Jane 3 2 I can accomplish this with a crosstab query but when I do Access displays the columns in order by an alphanumeric sort. IE: 1/2004 10/2004 11/2004 2/2004 I can't fix this in the queries properties because I have no idea what months I will be dealing with. Can anyone help with this? |
#3
|
|||
|
|||
Use can try to use the column headings property of the query to specify the
column names in the order that you need. You may need to update it once in a while, depending on how your date ranges move. Otherwise, you can use some VBA programming, and a temp table to sorta gerrymandering of the data and the report label captions. I won't bore you with the details here, but you can ping me if you want to know more. -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "MJatAflac" wrote in message ... I am writing a report where the user specifies the beginning date and I go get the information for the twelve month period that begins with that date. Once I have the data I need to get a sum of hours worked for the month for each employee. The Data should look like this: Name 02/2004 03/2004 04/2004 ... Joe 1 3 Jane 3 2 I can accomplish this with a crosstab query but when I do Access displays the columns in order by an alphanumeric sort. IE: 1/2004 10/2004 11/2004 2/2004 I can't fix this in the queries properties because I have no idea what months I will be dealing with. Can anyone help with this? |
#4
|
|||
|
|||
I normally use a calculated Field formatting the date to the format
"yyyy/mm" so I end up with: "2004/01", "2004/02", "2004/12", "2005/01", "2005/02" .... and the Cross-tab Query will sort the Columns correctly for you. -- HTH Van T. Dinh MVP (Access) "MJatAflac" wrote in message ... I am writing a report where the user specifies the beginning date and I go get the information for the twelve month period that begins with that date. Once I have the data I need to get a sum of hours worked for the month for each employee. The Data should look like this: Name 02/2004 03/2004 04/2004 ... Joe 1 3 Jane 3 2 I can accomplish this with a crosstab query but when I do Access displays the columns in order by an alphanumeric sort. IE: 1/2004 10/2004 11/2004 2/2004 I can't fix this in the queries properties because I have no idea what months I will be dealing with. Can anyone help with this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab Query column heading order | victor | Running & Setting Up Queries | 4 | May 27th, 2005 02:45 PM |
Fixed column names from a crosstab query. | Dave T | Running & Setting Up Queries | 3 | May 2nd, 2005 03:56 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Changing data under the column heading in a crosstab query | Robin | Running & Setting Up Queries | 6 | November 15th, 2004 04:10 PM |
How do I set up a report using dates as my report header? | Robin | Setting Up & Running Reports | 16 | November 13th, 2004 01:00 PM |