XTab Colun Heading Date Sorting
I have a cross tab query for which I want to display the dayes in mmm-yy format. I am grappling with the problem that this is essentially a string, so gets sorted alphanumerically.
Having read around related forums I have tried basing the XTab on a select query, which is sorted by the conventialal date, but also has another field that gives a date using Format([DateAdded],"mmm-yy").
Trouble is, Access only lets me bring across 1 column heading, so I must sort on the column I display, and this doesn't solve the problem.
The coulmn headings are not be fixed (i.e. "Jan";"Feb" etc) as the months are financial year months (April - March).
I can get this too work by using yyyy/mm, but my board of directors are not happy with this format.
Any ideas. I would be open to re-querying my Xtab from another query of from a report if that worked.
Cheers
Mike
View Complete Forum Thread with Replies
Related Forum Messages:
Form Laout - Matrix (row Heading, Colum Heading)
Hi all, this is my first post. i have created a simple access database for keeping student and attendance record. student table fileds are: studentId - primary key forname surename dob gender accademic year attendance table fields: studentid - primary key date - primary key attendance (yes/no boolean field) paid (yes/no boolean filed) this database is just ment to keep records of students attending at particualr dates. for example if attendance table cotain records like: http://www.crazyanime.pwp.blueyonder.co.uk/table.JPG for the form layout, what i want to do is http://www.crazyanime.pwp.blueyonder.co.uk/form.JPG i want this to be editable. how would i do this using access form, or do i have to wrtite VBA code PLUS i want the form to automatially have new records when i add for example a student, with ID 10011 OR if i add new records for a different date say 11/11/2006, then i want that to be viewd on the form just like the 21/09/2006 and the 04/11/2006. please help :) been looking for a solution for long time. thanks
View Replies !
Using A Date Field Column Heading In Crosstab Qry
Problem #1 - getting a crosstab to show more than 12 months of data across. Is this a limitation? Problem #2 - is related in that i prefer not to use in the Qry2 field name of: Expr1: Format([CalEndDate],"mmm") The above seems to limit me to just 12 month names like Jan, Feb, Mar etc etc I was prefer to see: 1/31/2007, 2/28/2007, 3/31/2007. So I want to move away from mmm. If i leave CalEndDate "as is" in Qry2, i get an error message: :This expression is typed wrong or too complex to be evaluated." I even tried in Qry1 to make 3 extra field where i strip out 2007 and strip out the month number and then combine the 2 stripped out numbers to look like 2007-1, 2007-2, etc but i got no where on that either. I was thinking that maybe text would work better but no go. Here is what I have. Two Qry's. Qry2 is a crosstab using Qry1 data. Qry1 has a Date field in it. I want this Date field called CalEndDate to become the column heading in my Qry2 crosstab. In Qry1, I have several fields but notably: Field: CalEndDate Criteria: Between [Start Date] And [End Date] Then in Qry2, the crosstab, I have: Field: Expr1: Format([CalEndDate],"mmm") Total: Group by Crosstab: Column Heading In summary: i am looking to have Qry2 ask me my start and end dates and then then give me 18 months of crosstab data based on either a concatenated field like 2006-11, 2006-12, 2007-1, 2007-2 etc in proper order. or worst case, have the field show the CalEndDate for the column headings in the crosstab. thanks for ideas. Penwood
View Replies !
Sorting By Date
I have the data below in a query in Access, and I want it to sort by the Week Begin date. I have put a sort on for 'Ascending' but it doesn't work. (Due to the 29th being after the 28th - regardless of month) Is there any way I can force the sort in this query ? It must be in the same query though, as I could probably do it by using this query in another one then sorting that. However, I don't want to do that for reasons I won't bore you with. WeekBegin Count 05/09/05 137 12/09/05 90 19/09/05 22 26/09/05 55 29/08/05 122 PS - I should mention that I think it is because the WekBegin date is an expression based on an actual date field. Therefore I believe it's treating it as some sort of text field. Any help would be appreciated. Thanks. J.
View Replies !
Sorting By Date
Hi, I've just started playing with Access 2002 (Office XP) and I@m having a little bit of trouble. I'm using the Query Wizard to summarise my database giving me average values for each month. When the query is first run it displays in the correct month order: April, May, June, July, August, September. But viewing the query again or when trying to graph the data in a Form, the sorting forces alphabetical order: April, August, July, June, May, September. How can I get the query/form to return the results in true month order? Here's the SQL info which the Wizard created: SELECT DISTINCTROW Format$([Raw Data].[TimeStamp],'mmmm yyyy') AS [TimeStamp By Month], Avg([Raw Data].[Indoor Temperature]) AS [Avg Of Indoor Temperature], Min([Raw Data].[Indoor Temperature]) AS [Min Of Indoor Temperature], Max([Raw Data].[Indoor Temperature]) AS [Max Of Indoor Temperature] FROM [Raw Data] GROUP BY Format$([Raw Data].[TimeStamp],'mmmm yyyy'), Year([Raw Data].[TimeStamp])*12+DatePart('m',[Raw Data].[TimeStamp])-1; Thanks in advance for your help. David
View Replies !
Sorting By Date
I have a subform that simulates a listbox and I click on the header label to activate the sort. My problem is sorting by date. Below is the sort function and one of the date labels that I need to sort on. Sort Function:Code: ========================================== Private Function SortOrder(col As String, xorder As String) As Integer Dim strSQL As String Dim sf As Form Set sf = Forms!frmMainEntry!fctlNotifications.Form strSQL = "SELECT DISTINCTROW ProgramID, ProgramDescription, Facility, ResponsibleParty, DueDate, FrequencyOfService, AdvancedNoticeDate " strSQL = strSQL & "FROM qryProgramList " strSQL = strSQL & "ORDER BY " & col & " " & xorder sf.RecordSource = strSQL sf.Form.Requery End Function ========================================== On-Click Date:Code: ========================================== Private Sub lblDueDate_Click() Dim response As Integer If Me.txtSortOrder = "DESC" Then response = SortOrder(CDate(DueDate), "asc") Me.txtSortOrder = "asc" Else response = SortOrder(CDate(DueDate), "DESC") Me.txtSortOrder = "DESC" End If End Sub ========================================== I'm not sure if I should convert the date into something that can be sorted or just leave it as a date. I can't get it to work. Help please. Thanks, PC
View Replies !
Sorting By Date
help w/sorting two columns one with date checked out, other with date due back. some are overdue and there is not a date entered yet. need to sort by the longest overdue first and leave out those that have already been returned
View Replies !
Sorting By Date And Time
Hello, I am having some trouble sorting my date and times. I have them both working great individually with RunCommand Sort Ascending after Update. The problem is, the dates will get jumbled up after the time is sorted. Is there a way to connect the two fields where the dates will always stay in order? I have attached a pic of this DB if it helps any. Thanks so much for any help. Fritz
View Replies !
Sorting A Date Field
i have a date field where about 80% of the data is entered in the format dd/mm/yyyy but the rest may be year only since the exact date is not known. Therefore, it does not have an input mask or a format set. Is it still possible to sort on that field? (As i write this i realise it is probably a dumb question but always worth a shot!)
View Replies !
Sorting By The Most Current Date.
Hey all, I have what is probably a simple question. I want to sort my date field in a query by the most recent date. When I use ascending or descending it looks at the Month/day/year to sort it. I need it to look at year/day/month. Any help would be awesome.
View Replies !
Sorting Query By Date & Employee Name
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column: [Enter Driver Name] Then I tried to enter the following in to the criteria field in the date column: Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY] However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?
View Replies !
Am I Heading For Trouble? I've Never Had So Many One-to-one Before...
I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are: tbl_workorders (main project info) tbl_services (services ordered) tbl_contacts (internal company contacts) tbl_customers (customer information) tbl_project_dates (milestones of project) Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders). Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables. Thanks for your feedback!
View Replies !
Sorting A Table, Apply The Sorting To A Form
Hi all. I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number. I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all. Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on.. I'd apreciate some help with this :) Thanks Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
View Replies !
Force ROW Heading In A Crosstab
Being an amateur at Access, I've only recently realized that you can force column headings for crosstab queries, by typing those headings into the properties of the field selected as the column. But can you do the same for ROWS? My data concerns patients of a specific type and the times of day they show up at our A&E (ER). I've grouped these by hour and now I want to crosstab them by day of week. But if there aren't any between say 3am and 5am, those hours don't show in the query, which is screwing up a pre-formatted report I have. I've found some assistance on other threads..... http://www.access-programmers.co.uk/forums/showthread.php?t=92471 http://www.access-programmers.co.uk/forums/showthread.php?t=83820 .....but to be honest I didn't really follow it. Is there an easy solution? If not, it's not a huge problem and I can "fudge" it once a month, offensive as that must sound to you professional types! Thanks as always. Pat
View Replies !
Query Problem: Text With Heading Row
Excel sheets: Item Item Desc Price Price Out Date 11040 MIRR SHUTTERED 38.5 69.99 P.O. # Status 52334 280 53074 280 53075 280 11041 MIRR SHUTTERED 38.5 69.99 P.O. # Status 52334 280 53074 280 53075 280 And I want to make it like: 11040 MIRR SHUTTERED 38.5 69.99 52334 280 11040 MIRR SHUTTERED 38.5 69.99 53074 280
View Replies !
Not Able To Add More Column Heading In Cross Tab Query. Its Urgent Plz Help...
Hi, Not able to add more column heading in cross tab query. I tried to change the query properties to add more column headings as given below. In the query's Design view, right-click up in the area where your tables are shown and choose "Properties" from the right-click menu. The 3rd line down is for Column names. Enter what you need there. Evn after doing it. i am not able to . Whn I try to save or view the datasheet it says. to create a crosstab query u need to have one or more row headin one column headin and one value. please help. its ver urgent. thanks in adv..
View Replies !
Query Field-heading Format: Tablename.Fieldname?
Im running a query and normally there is only a field-name in heading. I have multiple tables with equal field names. Now I want to get table names in heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames... Im finding a solution. Help me please. Thanks :eek: - Roger -- Have a nice day.
View Replies !
Column Heading "too Complex"
Hi, I'm trying to get a crosstab to output some columns that dont have data. e.g. a feedback survey with no low scores. Everytime I put something in the Column Headings property the query becomes "too complex" and won't complete. I have tried just putting "Question" which is a Column that should always output. Any ideas? Thanks, Darren H
View Replies !
Year To Date Totals, Month To Date, Week To Date
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields. I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
View Replies !
|
TRACKER
