position, DateTime, value (datetime being a text field by the way, not my fault :-))
I wrote a simple pivot query
TRANSFORM Avg(value) AS AvgOfvalue SELECT Position FROM table WHERE (dateTIME)>format(Date()-2,"DD-MMM-YY") GROUP BY position PIVOT dateTIME;
The idea was that I could report each last 2 days with this query.The pivot query works OK.Creating a report based on this query, the control source of the fields is linked to columns like "12-JUN-15 07:00" which will not be available after some days.Isn't it possible to create a pivot report on a pivot query based on dates?
I have a bar pivot chart made in a form this load fine, I have the below VBA code in place to control the bar color depending on what the bar show.
Set frms = Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0) Dim i As Long With frms For i = 0 To Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1 With .SeriesCollection(i).Name Select Case Forms![OtherCases created by Indv by Month
[code].....
When I then open the Report this is not working at all each bar just get a random color regardless what the bar show.how to convert the above to fit a Pivot chart form a form that is displayed on a report.
I have created a database that tracks production by individuals. I have also created a report which includes a pivot chart to give the user a chart view of production.
In the pivot filter of the chart I have associate names, where the user can click all, or individual associates, to see individual production.
The trouble I am having is when the user goes to print the chart, it will only print the chart with the "All" filter view. Even if the pivot chart is filtered to one associate, it will print like "All" was checked.
Is there a way to allow the report/pivot chart to only print the production for the individual who is checked within the pivot filter?
I am trying to create a report that shows how many sales each sales person has in a 2 year period and sort it from the most # of sales to lowest. I want to show all sales people not just top 5 or 10.
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;
I was wondering is this was possible in access. I've read material on grouping and sorting, but haven't been able to pin down what I need.
I have a combobox field that has 14 entries, beginning with 01 as the first two characters. What I'd like to do is change the order in which everything is displayed on the report.
For instance, show 07 at the top, then 09-13 below that, and so on...
I use the call back function to generate my list of reports in a list box.
I chose to use this method as it seems like an easy way to do what I want, and to a certain extent understand how it works.
What I would really like to be able to do, is have it filter the list of reports.
For example, some reports are for me working behind the scenes, and also so many are subreports that go in to the main report so dont need listing.
I would like to have a report names 'Clients Full Report', 'Clients Medication Report' etc on the list (I dont mind changing report names as I think that will be the key to making it work) So many of my sub reports have client in its name, so if a report could be named '1Clients Main Report' and the code looks for the number 1 and removes the 1 from the list so it displays Clients Main Report on the list.
So its just a way to make the list box only include specific reports based on a number.
I have one table holding lots of different data. That data is all the same, but when it comes to output, it needs to be separated by groupings (which I have defined within the table itself as group 1, group 2, etc)
I was wondering. i would like to do the same thing with forms so that input will be separated easily / visually for the data input. At this point, all I can think of is order ascending /etc, but this doesn't give any visual queues to the user when he has moved to the "next" part.
I don't like the option of just another text box beside it spamming a textbox per record of whats next.
By the way, the form display is continuous, and thats why I want a "grouping" option.
I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.
I have an issue sorting the results on my Access 2010 Report. Here are the details:
- Report Record Source = "Portfolio Ranking" - Portfolio Ranking is the name of a query - The query includes a calculated field called "Impact Score" - FYI: this is a web database (not sure if that limits what I can do)
I want to sort the report on the Impact Score column. However, when I view the query calculation for Impact Score, the "Sort" field says "(not sorted)" and it's greyed out, so I can't select Descending as desired.
I have a report (report1) which is based on a single query (query1).
There is a second query (query2) which returns just a single value (say 100) and I want the result of the query2 to appear at the bottom of the report1. The query2 is not related to the query1 so I cannot join them.
When I insert a text box in the report1 and choose the query2 from the control source Access gives the error, "The Microsoft Access database engine does not recognize '' as a valid field name or expression".
I must use XP/Access 2003 to solve this problem for reasons I won't get into.
I have a report with a Group by on City.
The users need to be able to sort by Coordinator within that OR by Client Name based on a selection they make on the form that opens the report.
-- I have tried to set .OrderBy
-- I have tried to set .RecordSource to different queries with order by hardcoded in them (I only have 2 sort options fortunately)
Is there a way to dynamically change the "Sorting and Grouping" defined in the report? This seems to be the only sorting that the report is responding to.
I have a class roster report in which the Employees' names are concatenated in a text box. When I select to sort the concatenated names alphabetically, it is sorting by the Unique ID, which is a letter and four numbers. The letter used to match the last name, but newer IDs are random. So most names are still in order, but some are not.
The Employee field Row Source is:
SELECT [Last_Name] & ", " & [First_Name] AS Employee, tbl_AM_Operators.User_ID FROM tbl_AM_Operators ORDER BY [Last_Name] & ", " & [First_Name];
What do I need to change to have the Employees' names alphabetized on the report?
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
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?
I have a query which works perfectly fine, it's the report that I'm having issues with displaying correctly.
My report is a daily personnel accountability report that shows where everyone is for the day. Instead of having a cumbersome query like I did before, I have opted to just use the results of the selected drop-down option to move the X to the appropriate box of where so-and-so is for the day.
Using
Code: If [marked_as] = 1 Then Me.Morning.Value = "X" ElseIf [marked_as] = 2 Then Me.Afternoon.Value = "X" ElseIf [marked_as] = 3 Then Me.Evenings.Value = "X" End If
I was unable to get it to work accurately outside of showing the three dummy names under the same column, even though the three dummy names were each placed in one of the three test categories.
I have this set as a private function called when the report loads, which is based on a query that filters down to the exact department or office (depending on the user's selection). Like I said, that part works fine, it's getting the code to accurately display in the correct column.
i want to open a report but only showing the result of one record in a sub form,
i have a field that is on all rows of the subform,[click to run] and what i want the user to be able to do is double click on this field and it will open the report with only the record information for that row displayed.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.
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?
