Queries :: Filtering Data - Results Need To Be In Specific Order
May 21, 2013
I'm taking my first database class and I'm working on a hospital project in which I need to determine which pair (one doctor and one nurse) has a decremental performance as weeks go on.
I would like to find what pair had a "Good" result in week 1, "Average" in week 2, and "Bad" in week 3. My problem is that the results need to be in this specific order.
The professor told us to use a formula and I got the solution after building 6 queries that involved union, difference, and cartesian.
I have a query that is returning running conditions for a machine over multiple shifts. the table is set up like this
Code: date shift parameter condition 09/13/13 1 front 500 09/13/13 1 middle 450 09/13/13 1 back 475 09/13/13 2 front 510 09/13/13 2 middle 460 09/13/13 2 back 490
this is just a small example. there are about 24 different parameters reported on in a shift..I want the query to sort by date ascending, shift ascending, and then by parameter in a specific custom that i define. I want it ordered "front, middle, back" for each shift. Again this is just an example, there would be 24 parameters I want in a specific order. Then do I need to define the sort order again for the report based on this query?
Is it possible to once you have run a query be able to somehow double click on the results in order to either go to original record or see more details? Similar to "show details" in excel?
I have made some queries that are working well however they are specific in nature and once found display a list of results. I have left a field that uniquely identify them and are after a quick way to view the details of the results from the query rather than take the ID# and manually search the original table.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I've filtered a listbox query between two dates selected by a user through an unbound text, however the listbox only changes after you click it. I've requeried it after someone updates the 2 unbound date textboxes, yet it still doesn't work.
I've created a database to keep track of all our Microsoft licences for the company. Just to make sure we're fully licenced, to allocate where needed, and to help reallocate when we replace a pc.
I have 2 tables, 1 hardware (listing PC ID,location etc.) and 1 software (listing software type, software licences, and to which PC ID allocated).
What i'm trying to do is filter the pc's missing either an Office licence or a Windows licence or both, so i can base a report on the results.
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
I made a form that lets users search for records in a database and displays them in another form. In that form I've made 3 buttons to let users narrow down the results to show only certain items within the search results.
Now, 2 of the buttons show specific items and the third one toggles the form to display all the records. So lets say I'm filtering records by whether or not they contain the words "car" and "truck." If a record has "car" but not truck and I click the button to sort by "truck" then the search result goes blank, which is ok. But if I click "car" or "show all" to display everything again then the search result stays blank.
This doesn't happen with records that contain both "car" and "truck."
For every button I have something like this: DoCmd.OpenForm "AdminSearchResults", , "SOW", "VendName = '" & VendName & "'", , , "'VendName'"
I know my explanation may suck, but if anyone can help me, that would be great, lol
I am using a form in which i am filtering the results in the list box based on the textbox value. I am dynamically switching 3 row sources for the list box.
My Need is that the results produced in the listbox should get filtered again when typing in the second text box i.e based o the country name.
I work in psychological testing and I have created a database to store some data for our patients' test scores. I have a main form with demographics and then 5 buttons on the main form that open into 5 other forms (one form for each test). I also have created a button on the main form to run a Report; however, I would like to filter the report to only show the current record (e.g., Patient ID #1 only).
(Can this be done?) How should I go about filtering the report to show only the open record? Expression is preferred - I'm not very good with code.
I'm also assuming that since each form is linked by the primary key of Patient ID, I should be able to see all data from each form (main + 5 others) in the report for that specified Patient ID... (?)
I am using the Filtering a listbox method from this post [URL] .....
It works great apart from when i type too many characters and no search results can be found i get a run time error '2105' you cant go to the specific record.
I think it may be because my form has a row source. When the example uses an unbound from?
I am trying to create a query that filters results only outside of 1 standard deviation of the mean. Is there an option for this in the criteria spot? I'm fairly new to Access and I'm not sure if I can reference a particular cell in the criteria.
Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).
But NOT filter the results so that user can still navigate as usual after the search has been carried out.
I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)
I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.
Right now on my column headings I have MONTH.
When I try to reference the form in the criteria, I always get the error
"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"
Does this mean that you can not filter crosstab queries?
Okay, here's my new dilema. I'm in the military and rank is one of the fields in my DB. The only issue I'm having is that I would like to sort by seniority, not alphabetically. For Example, I've listed a few ranks below in the correct order from greatest to least. Is there some way that doesn't involve a page of code to achieve this? Thanks.
and the order of Role (Z,A,C) is fixed it has to be in this order and if Role Z has more than one person, it will return only the name, id and contact number but will not repeat the role.
I have a series of dates over several months, and I want to write a parameter query which will list only those from the month of August. I know how to write a general parameter query, but I can't figure out how to write one that specific.
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building Date Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.
I have report which record source is a Union Query, The query contain Order Details and a single row for "Freight Charges", I just want to set Freight Charges show in Report at last row.
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
