In order to do a report, I would like to make it so that each customer is on its own row within this query, with his or her total days added up. I have no idea how to go about it.
I currently have a form where I would like the form to display the oldest account first, so the overall objective is the employee will action the oldest account first and then go onto the next one etc.
I created my first RDB input form and it works fine. In the subform new records are created at the bottom row. The subform has a date field. The main and sub forms are based on tables.
When I open the input form I would like to see the subform records sorted by the date field but I don't know where to look for help on that. Can somebody point me in the right direction?
I am including my form in design layout just in case it will help.
I am using Access 2007.I have a report that has 2 subreports within it.One of the sub reports input is from a table (Work Order Parts) whose records have a field called "DisplayOrder". This is a numeric field that I use to enable the user to specify the exact order that they want the records to be displayed in a form or report...well that's the plan anyway.
I can't seem to get the subreport to sort on the "DisplayOrder" field.
I have tried to set the Property value "Display Order" to "[Workorder Parts].DisplayOrder" but this doesn't seem to make a difference. The "Order By on Load" is set to "Yes".
Here is the "Record Source" value : SELECT DISTINCTROW [Workorder Parts].*, Parts.PartName, [Workorder Parts].Notes FROM Parts INNER JOIN [Workorder Parts] ON Parts.PartID = [Workorder Parts].PartID;
Ive created a form and one of the fields is a field called "Due Date"
I want to create a button that once pressed will sort the records in order of due date with the oldest being record 1.
How I can get a button to manipulate fields.
Ive got a set of buttons at the bottom of my form (Ive placed them in the footer) that were set up using Access automatically. They go to first, last, next and previous records. Ive also set up two extra, one that creates a new record and another that sets the record.
The thing is that the set record button I would actually like it to take the data in a field called "Frequency", add a random number to it and then set the record.
I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.
EmpName GroupNum Jon Group1 Sam Group2 Tom Group1 Bob Group1 Hal Group2 Dan Group3 Cal Group2
With sample table above, can I write a query in Access that lists the data by GroupNum based on my criteria, say Group2 1st, then Group3, then Group1? Basically, how do I make the result show:
Sam Group2 Hal Group2 Cal Group2 Dan Group3 Jon Group1 Tom Group1 Bob Group1
I have a subform embedded in a mainform which contains loan payment details over a number of months. The record starts with the payment period number ie 1,2,3 etc. The problem is that every now and again the subform changes the display order of the records so that I get 4,5,6,1,2,3.
When I look at the underlying table the order is OK as is the non embedded sub form. I cannot see any reason for the change and it only happens randomly (apparently).
The numbers are not autonumbering but manually input. Hope someone can help please.
I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder] So if we look at washing dishes as a task then the steps are as follows:
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink , 1, 12.1 512 Turn on tap, 2, 12.2 205 Put in detergent, 3, 12.3 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5 etc
If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5
Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 3, 12.3 435 Turn off tap, 4, 12.4
I've recently been building a database on Access to replace the rather clunky and slow one we currently use here (built in Excel).Generally, I've been making it up as I go along which has worked quite well for me so far. However, I've encountered pivot tables. Normally, it shouldn't be a problem; the tables themselves are easy enough to understand. However, I've found that the tables aren't nearly as flexible as I would have liked. At least, not in a way directly apparent to myself.
(After trying, and failing, to include links to Imgur with details on my issue, I have included a .zip file with both images I was going to show)Generally, in Excel, the table works everything out for us, then we sort by largest to smallest and work out the totals for the last 7 days and the last 7-14 days (the week before last). After that, the last formula works out a percentage (loss or gain). [excel.png]
In the access table, though, I can't find a way to make this happen. There's no readily apparent button to add a new calculation field. I've tried using the create calculated total button, but that seems intent on making a separate total for each day on the table, which I don't want. [access.png]
Also, I can't figure out how to sort the records by grand total descending. I would have thought right clicking the grand total and clicking sort descending would have done it, but apparently that would be far too easy. I feel that it's prudent to mention that I'm not all that experienced with Access. I completed MOS and ECDL courses when I was in primary school, but it's been a really long time since that and all of the Office applications have changed rather dramatically.
I have a Listbox that I use to find records in the Database. It has two fields, [ShootID] and [ShootDate]. It is curently sorted in the query by [ShootID] but I want the user to be able to change it to sort by [ShootDate] and then back again.
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname] =Sum([Quarter1_A]) - Named "Quarter_Total" =Sum([Quarter1_T]) - Named "Quarter_Target" =Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
The recordsource is a query with over 6,000 records. The form currently lists the records in datasheet format with header and footer for things like buttons and filtering. The client wants to be able to go from page to page of the souce query, showing 100 records on the form at a time.
But at the same time, they should be able to filter or sort the data source in it's entirety. The person who created the form came up with what seems like an awful solution to the problem. It seems to use a random number generator to determine how many records to portray at a time. I see this in the code as well as in operation, because the number of records on page to page varies. It doesn't even start out at 100! Worse yet, using a sort on the page only sorts the records that are visible.
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want... when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
I try to do something but I don't know how. I have 1 table with 3 rows (title, year, genre) and 1 Form. Is it possible to create combobox or something with 3 criterium, when I selected criterium to re-sort columns in Forms (by title, by year, by genre)?
Continuous form: The standard menu / toolbar commands sort ASC and DEC work just fine. However, if I define a button with the VBCode "DoCmd.RunCommand acCmdSortAscending", I get an alarm message pop-up saying "the command or action "SortAscending" isn't available now". I made sure that the standard menu items "Sort asc / dec" are not grayed out(disabled) at the time I click the button in runtime. The mouse cursor points to the desired sort criteria field.
Hello all... I have a table which includes month, day & date. I want to sort this table by day, and then by month & then by year... does anyone know how to do this?