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.
View RepliesA 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 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;
What am I doing wrong?
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...
In Report Design View, is there any way to copy the group, sort and total specifications that are in one report and paste them into another report?
View 4 Replies View RelatedI 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 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 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??
View 3 Replies View RelatedI 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.
View 6 Replies View RelatedHello all,
I have the following data, that was queried and sorted to columns PROBLEM_ID and then by STATUSDATE (ascending):
STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10________12/04/2005___1234
40________15/05/2005___1234
10________11/11/2004___1235
50________15/12/2004___1235
70________20/01/2005___1235
I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;
However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
Kind Regards,
Jean
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
Hi,
I need help with an access 2000 multiple queries problem.
I have the following:
Table 1 = info below / table 2 = computer types / table 3 = offices 1 through 10
field 1 = name (20 employees)
field 2 = computer (5 different type of computers)
field 3 = office (10 different offices)
field 4 = issued
field 5 = not issued
field 6 = stolen
On a form I have the 2 combo boxes (computer and office) to select and check boxes to select issued or not issued or stolen.
I need a report printed based on the multiple criteria query only showing the results that I ask for.
For e.g. in office 5, computer IBM, issued.
Print Report: lists names of all in office 5, with IBM’s, issued only!
etc.
Please help.
I have a Union Query (that works perfectly fine) with the following code:
Code:
SELECT * FROM sbqryUseBattery
UNION
SELECT * FROM sbqryUseBeltsDeck;
UNION
SELECT * FROM sbqryUseBeltsHydro;
UNION
SELECT * FROM sbqryUseBeltsPTO;
UNION
SELECT * FROM sbqryUseFiltersAir
UNION
SELECT * FROM sbqryUseFiltersFuel
UNION SELECT * FROM sbqryUseFiltersOil;
I am using this information on a Report.
The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report?
This is a query, report and vba question. I'm using Ms Access 2007.
TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate
I created a select query to join the info that I need.
SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))
I have 2 crosstab queries.
CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
This qry pulls from multiple tables to create a job ticket for the technician/installer. I will not list all the tables because I have narrowed down my problem to two specific ones. These two tables:
tblBusinessCustomer
tblBillingInfo
Have a one to many relationship, due to a single customer could have multiple billing notes, this is how the tables are structured:
tblBusinessCustomer:
CustomerID (Primary Key)CustomerNameAccount StatusInstallationDate
tblBillingInfo:
BillingID (Primary Key)CustomerIDBillingNotesDateBillingNotes
I have a one to many relationship using the CustomerID field from tblBusinessCustomer to tblBillingInfo. When I run the report I have a field for billing notes. So what happens is that when I run the report (using the qryJobTicket as a filter) the report will have as many iterations as there are Notes. So for example:
CustomerID: 1
BillingNotesDate: 1/1/12
BillingNotes: (some form of gibberish)
BillingNotesDate: 1/2/12
billingNotes: (different gibberish)
so the report will run twice, one for each "line" of notes. I have tried using a form of the Dmax function as a filter in the qry so that I would only grab the most recent date from the BillingNotesDate field, thus only running a report and grabbing the most recent info.The problem is that I have inherited trying to clean up this database, and I added the BillingNotesDate field, since before they were running a constant "log" in the notes field:
1/1/12 Customer changed Name to ABC company, 1/2/12 Customer was notifited of late payment, 1/3/12....and on and on.SO there are NULL values in the BillingNotesDate field for all of the OLD data. And instead of trying to clean that up right away, I was trying to use the Dmax function to give me the most recent date, but the ones that had no date at all (NULL) it would not pull that record. I am trying to use the DMax function on a Date Field with Null values
ok i have a query with 2 fields,
Endtime: AccountTime
Starttime
both date input from another query, if i run without any criteria it works and get some results:
Endtime Starttime
9/6/2005 6:24:09 AM9/6/2005 6:23:53 AM
9/6/2005 6:24:16 AM9/6/2005 6:21:20 AM
9/6/2005 6:24:34 AM9/6/2005 6:24:22 AM
9/6/2005 6:26:06 AM9/6/2005 6:25:51 AM
9/6/2005 6:26:15 AM9/6/2005 6:25:00 AM
and i have some inputs on a form (type - genreal date) with the values:
startdate:
09/06/2005 00:00:01
enddate:
09/06/2005 23:59:59
and the condition is on starttime field:
Between [Forms]![Main]![startdate] And [Forms]![Main]![enddate]
but i get no results....????
any help?
thanks in advance.
Dal
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
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.
View 8 Replies View RelatedI had made a report based on a query last year as bellow, but I don't remember how did I sort the results by "Totally"?
TRANSFORM Count(ID)
SELECT specialist,Count(ID) AS Totally
FROM projects
GROUP BY specialist
PIVOT main_firm;
The results are sorted by Totally:
Specialist totaly A B
-------------------------
Sara 10 6 4
David 7 2 5
Paul 3 2 1
When I run the Query, the results are not sorted, but in the report I see them sorted.
I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:
Code:
Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1
[Code] .....
I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.
I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by
Subquery 2:
Code:
SELECT
CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total],
CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax],
[Line Total With Tax]-[Line Total] AS [Line Tax],
[Inventory Transactions].*
[Code] .....
I have a report when i send units back to the customer, the report generates an ID in a list box (a query picks the last ID used and adds 1)
after the report prints i use an update query to mark the units as "shipped" with the dates in the main table. i need the update query to use the value from the list box to update the value in the table.
I have a report with a date field and want to sort it by week starting on Wednesday. I currently have the week starting on Sunday.
View 3 Replies View RelatedMy parameters are linked to a form and say:
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null
Ordinarily this works fine in returning either the selected value or all values if left null.
I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].
The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.
If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.
The crosstab data isn't specifically the problem but needing to set the parameter names seems to be
I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels
It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.