How to reorder columns in a crosstab query

The columns in a crosstab query are generated automatically, and sorted 
alphabetically, even though the field's data type is Date/time.

How do I make the query sort the columns in date order?

Thanks.
0
Utf
6/27/2007 12:26:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
2110 Views

Similar Articles

[PageSpeed] 29

Absolutely correct.  This technique works best for crosstabs that will be 
used for fixed reports.

However, you can, and I have, created the query in VBA.  If you are doing 
this, then you can write a query that will return the "dates" that will be 
selected in your main query.  Then just sort them in the order you want them, 
and add them to the PIVOT clause of the SQL.

If you google on "VBA dynamic crosstab report", you will find some 
references to how to create a report with dynamic column headers.

HTH
Dale

"Martin Folley" wrote:

> Does this not require you to know, in advance, what the column headings will 
> be (what dates will occur?) ... surely this will change whenever the 
> underlying data changes?
> 
> "Dale Fye" wrote:
> 
> > Right click in the grey porition of the query grid and select Properties, 
> > then type the values in the order you want them in the Column Headings area.
> > 
> > It is imperative that you get these values exactly as they appear in the 
> > query because if the column headings are misspelled, the corresponding column 
> > will not show up in the query results.
> > 
> > Once you have done this, you should look at the SQL to see how it actually 
> > looks in the SQL.
> > 
> > 
> > 
> > "Martin Folley" wrote:
> > 
> > > The columns in a crosstab query are generated automatically, and sorted 
> > > alphabetically, even though the field's data type is Date/time.
> > > 
> > > How do I make the query sort the columns in date order?
> > > 
> > > Thanks.
0
Utf
6/27/2007 2:20:04 PM
Reply:

Similar Artilces:

refer to second column of listbox
Hi, I have a multi selected listbox. How can I refer to the second column of the listbox? Me.lstContract.ItemData(varItem).column(1) ??? Dim varItem As Variant For Each varItem In Me.lstContract.ItemsSelected gstrReportFilter = "[Ck_ContractID]='" & Me.lstContract.ItemData(varItem) & "'" ''DoCmd.OpenReport "rptCoFundActivity_k" '', , , gstrReportFilter DoCmd.OpenReport "rptCostShareContribution_k", acViewPreview '', , gstrReportFilter Next varItem SF ...

Query Text in the Mid of a memo
I am trying to query off a memo field [Description] the text that follows "User Name: " until the first space the "User Name: " is not in the same position nor is the text of the user name a fixed size. I am working with an InStr function but I don't know how to stop at the " " after the name. Thanks so much for any help. AngieSD wrote: >I am trying to query off a memo field [Description] the text that follows >"User Name: " until the first space the "User Name: " is not in the same >position nor is the text of the user n...

return a column no
I have a cell containing text. I need a formula that takes the text and finds an exact match in a row and return the column no. Eg Find Text: Name3 Name1 Name2 Name3 Name4 Name5 I want to return the column number which in this case is row 3. I know there is a simple formula but I cant find it Thanks =match(a2,1:1,0) will return the number of the column of the first match (if there is one). Assumes that the Name3 is in A2 and the names are listed in A1:IV1 (row 1) Craig McLaughlin wrote: > > I have a cell containing text. I need a formula that takes the text...

re: best way to move columns between 2 excel docs
Ok, here is my little project: In execel.doc A I have some columns that I want to move to Excel.doc B, and the columns are always positioned the same way in A, they never change their position. How do you transfer them to B, to append a running process of pre-existing prefilled from previous days ? Any code samples ? Macros ? Please help!!! :- ) I am reading a book on application-level programming, VBA for Access, so I understand more and more everyday. How much work do I have here ? Thanks. ...

Can't insert column; keep receiving error message
I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message that said, "...unable to shift nonblank cells off worksheet." I tried everything from unhiding all columns and rows toreformatting all the comments to move and size with their relative cells. I also removed all the comments and I was still unable to insert a column. Does anyone have a solution Thank you >-----Original Message----- >I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message th...

Local OLAP cubes in Excel/MS Query
Hi Can someone please recommend some good book or web page about creating local OLAP cubes using MS Query. I have some data in Access that I want to work with using cubes. Kind regards IgorM http://wang.se/en/CreateOLAPCube.html/ Ed Ferrero Hi Thanks for response. Unfortunately I still don't know if the show-details-after-double-click feature is available in pivottables linked to OLAP cubes. I tried all three connection types. With the two first options (rebuild at once and rebuild when needed) I still cannot make the pivot table to create a sheet on the fly and provide detai...

Column Headers #7
How do I print Column Headers on every page? File | Page Setup | Sheet Put the row you want to Repeat in the spot provided for Rows to Repeat at top. tj "Cscritch" wrote: > How do I print Column Headers on every page? ...

Relating one column to another
Stupid newbie question coming up: Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: M...

Comparing two Columns #3
I have to columns full of data one column is "inventory" and the secon column is "charged items" inventory is what should be on the shel according to the computer, and charged items are the books that ar checked out. So the inventory - charged items would more less give m what "should" be on the shelf according to the computer. So I want column C to list me everything that is in Column A that i not in Column B. Column A is Inventory Column B is charged items (goes up to 6000). After I typed it out it seems very simply I just haven't figured ou how to...

Renaming a column label
How do I rename the column header labels from "A", "B", etc. to something else? Made a valiant effort. Can't figure it out. Mike -- DO NOT reply to the "from" address in this post. Instead, construct a reply address from this template: v6nos at yahoo dot com. Short answer: you don't - that capability (other than using numbers - check the Tools/Options/General R1C1 reference style checkbox) doesn't exist in XL. Longer answer: If you hide the row and column headers (Tools/Options/View) you can format Row 1 for column headers. You can even use t...

How may I add more than 256 columns to an Excel spreadsheet?
I am trying to create a horizontal 12 month calendar in an Excel spreadsheet. I need 370 columns. Is there anyway to accomplish this within Excel? SPO, excel only has 256 columns -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "SPO" <SPO@discussions.microsoft.com> wrote in message news:75457654-C31C-4787-9790-D07E220849BA@microsoft.com... > I am trying to create a horizontal 12 month calendar in an Excel spreads...

View query results in MsgBox
I have developed qryQuickLook that brings up rptQuickLook when cmdQuickLook is clicked, everything works great but I would rather see the results (never more than 10 line items) in a MsgBox than have a seperate report window open up. Is there a way to do this? Thanks Dave On Nov 23, 11:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > Why "MsgBox"? You can display the results in a dialog form with a command > button or two. > > -- > Duane Hookom > Microsoft Access MVP > > > > "maces...@gmail.com" wrote: > > I have deve...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

How do I combine 2 text columns in Microsoft Excel?
I have two columns of descriptive text, the second column is the end of the first column's sentence, however I can not find a way in the Help options to combine the text values to create one complete sentence in one column. Does anyone know of a way to do this? Try: =A5&" "&B5 if you want spaces between the columns or look at the CONCATENATE function example =CONCATENATE(A5,B5) Domaniman wrote: > I have two columns of descriptive text, the second column is the end of the > first column's sentence, however I can not find a way in the Help options to ...

sum values from the same item in a single column?
Hi, I have a query to get a table like this: Item Num Values A 1 10 A 2 22 A 3 78 B 1 32 B 2 40 B 3 87 C 1 34 C 2 76 C 3 98 actually each "Item" has more than a thousand of "Num". how to sum all "Item" (A+B+C) at each "Num"? Like: Num Sum 1 76 2 138 3 263 Thanks! pemt pemt, Have a look at Crosstab queries under Help, that should give you what you...

Extracting Data in Cells in order -- (or) eliminating empty cell space in a column
Hi I have this problem that I bet is easy to solve, but i am lost. I am an expert at the slow way to do things, but maybe there is a better way. The only way I can describe the problem is by means of an example. Lets say I have a column of numbers: >_A_|_B_| etc >> 1_1_|___| 2_3_|___| 3_2_|___| 5_5_|___| 5_3_|___| 6_4_|___| 7_7_|___| 8_3_|___| 9_1_|___| and then i write a little function in the adjoing cell, B1: =if(a1=3,a2,"") From there I fill down column B to B9. OK, pretty simple so far, right? What I am looking for is instances where I find a '3' in co...

unique values of a column
hi how can i get the unique values of a column in an array? thanks in advance I think Advanced Filter will do what you want, there is an excellent tutorial here from Debra Dalgleish http://www.contextures.com/xladvfilter01.html Regards <anonymous@discussions.microsoft.com> wrote in message news:073d01c49649$6e4e49e0$a401280a@phx.gbl... > > hi > > how can i get the unique values of a column in an array? > thanks in advance > > Hi, Additionally check out Chip Pearson's website at: http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique >-----Orig...

"Group"ing adjacent columns independently
I was hoping that someone might know how to do the following - or whether it is possible at all ... I am using Excel's "Group and Outline" capability and I would like to independently group adjacent columns (or rows). For example, I'd like to be able to create a group of columns A and B and a separate group of columns C and D - with each group being independently selectable via the "+" and "-" buttons. As far as I can tell, if you try to group adjacent columns, the two groups get merged into a single group. Also, if I play games by trying to insert an ...

Report based on parameter query
I have a report that is based on an ordinary parameter query (i.e. right now it pops up dialogs asking for three values that it needs for its where clause). I use this query in several places, so I don't want to make the parameters dependent on specific form controls, which is the only information I've been able to find on google on feeding parameters in a report query's Recordsource. What I actually want to happen is to make a button in the main switchboard which, when pressed, will look up those values in a different query I already have and then use those to populate th...

creating a table with multiple columns
for a High School science project my daughter created a survey with 39 possible responses (3 groups of 13 responses in each group) the people taking the survey are supposed to pick the top 4 responses in each group (numbered 1-4, 1 being the highest). Other information is Age, grade and gender of the person taking the survey. What would be the best way to create the table and eventually charts to diplay the results of the survey? I was thinking of a flat table with a number in the cells correspondig to their answers but I have no idea how to create the charts or compare the answers w...

Multiple Unrelated Queries In One Report or Form?
Hello guys, I've been tasked with generating a report for my boss that provides an overview of our core database statistics. This includes the total number of orders placed each month and the total value of orders placed each month. I also need to provide the same information for purchase orders, quotations and discrepancy reports. Each individual query is a piece of cake on its own but how can I display all of this information either in a form or preferably in a nicely formatted report? The queries are all completely unrelated so there's no way I can somehow joi...

Query to Compare Actual and Budget
One table defines budget (each one has a unqiue ID) Another table defines employees (each one has a unique ID) Another table defines tasks (each one has a unique ID) Another table records budgeted time by project, task, employee Another table records actual time by project, task, employee I created one query which will total up the budgeted time by project and employee (so if an employee was assigned multiple tasks on the same project, you'll just get one total for that employee for that project). I created another query which will total up actual time byt project and employee (so if an e...

Column width
Is there a way to adjust the column widths in the Account screens? Envo In microsoft.public.money, Envo wrote: >Is there a way to adjust the column widths in the Account screens? It depends what you mean by account screens. Portfolio yes. Registers, no. "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message news:bbop021kqtuktlmju9bv83crvajkflh9ji@4ax.com... > In microsoft.public.money, Envo wrote: > >>Is there a way to adjust the column widths in the Account screens? > > It depends what you mean by account screens. Portfolio yes. > Re...

Repost-Edit and save query criteria
Sorry to repost this, but I was not sure I would get help with this again and it would get lost in the list. As suggested, I created a table called ExcludeShows1 with the ShowID and Year fields. I have posted the SQL with those changes to the query. When I tested this initially, it appeared to work. Then I tried testing the queries using last year's show data for 2009. No data is being returned for 2009. Not sure what is happening that it is not returning 2009 data. SELECT Shows.ShowID, Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([...

[Wave4] Account Column still not fixed
I Unfortunately there's still the same annoying "bug" as it was in the previous final version. If you manually add another column for Account in order to see where an email originates from it doesn't work for Hotmail/Live accounts. The new column displays the account correctly for all email accounts accept for Hotmail/Live accounts. http://i.imgur.com/56r5X.jpg I hope it gets fixt until the final release. I think you maybe need to set up your hotmail/live accounts as pop3 to do this. Mine all work to add columns. Art [artfudd] Folden -- "Phil"...