I have a query that I want to sort based on a table. (SS BELOW) I want the OS in qry2FINAL to be sorted based on Trn-OSstops table. So the OS column of the query should be sorted as which is the order shown in the table. I tried linking the 2 tables in my query but I had no clue what to do from there. Any help?
MV6
HP108
PT337
HP113 (sometimes there is an extra field, but can I make it show up last?)
http://i10.tinypic.com/68hkwhe.gif
Edit: I looked at my SS and it might be confusing. Ignore all of the records in the Trn-OSstops table except for the highlighted one.
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
This is something I occasionally see in Access and has been bugging me for quite a while.
As an example, when I have a table (all text fields except for the ID field which is an Autonumber with a unique index - ie just what Access creates when you import data) and I try to make a new table from a query by indexing the Autonumber field in descending order (ie to reverse the order of the table), it doesn't work properly.
So if I have:
SELECT [mytable].* INTO [mytable sorted] FROM [mytable] ORDER BY [mytable].[ID] DESC;
When I preview the data (ie run the select query to have a look at it), it looks fine.
When I change the query to a 'Make Table' and I then I check the table it makes, the order changes part-way down the list, so looking at the ID field it runs from number 2669 down to 2087 correctly, then it goes from 1960 to 1956, then 1803 to 1799, then 1751 to 1747, etc etc etc. After a while it seems to correct itself again, and orders normally down to #1
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 :
Is it possible to sort a natural table drop down by date from within a query? What im doing:
1. making a query that has certain results displayed 2. within the query you can select one of the field boxes and it has a list of all the items in that table. 3. is it possible to sort this natural table listing from within the query. I have attached a screenshot. Same thing happens with the client ID listing from within the query. Want to know if it is possible or not to sort those. I cant see how.
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??
I have a query that lists items in locations in our warehouse. Each location is broken down by Aisle-Bay-Level-Position, for example 50-101-01B; or 51-106-02 with all the even Bays on one side and Odd bays on the other side of the aisle. I have the query set up so it separates odd and even so I can go all the way down one side of the aisle then I have to walk back to the beginning of the aisle to check the other side.
What I am trying to do is change the sort based on another field that I have called SORT with a value of AZ or ZA depending on if I need that section to be sorted ASC or DESC. So for example all even bays in aisle 51 need to be sorted DESC while all ODD bays would be ASC. The way our warehouse is laid out it is not always the odd or even side that needs to be sorted DESC, that's why I made the SORT field. The formula I used for the SORT field is as follows:
SORT: IIf(([Aisle]='50' And [OE]='ODD') Or ([Aisle]='51' And [OE]='ODD') Or ([Aisle]='53' And [OE]='ODD') Or ([Aisle]='52' And [OE]='EVEN'),'AZ','ZA')
Is there a way to make it sort the BAY in ASC or DESC based on the value in SORT?
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.
My table has 3 fields, Employee Number (text), Job Description (text) and Current (yes/no). The table keeps track of the Job Descriptions that an Employee has had along with his current Job Description (actually Title is more appropriate a word but client requested Description) So the table has records such as:
0001 - Floor Sweeping - no 0001 - Ceiling Cleaning - yes 0001 - Dumpster Turning - no
The yes signifies that that is the current Job Description for employee #0001 and the others with no are previous Job Descriptions that the employee #0001 has held.
Now - I created a form to add these records to the table and on that form I want a list box to show the records in the table for this employee but I want to sort it such that the current Job Description is listed first and the rest show up in alphabetical order following. I have the query to list all of the Description, but how can I get the current one to always appear on the top of the list?
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
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.
In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.
I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.
The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.
I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.
Been working on this for a while now and can't get it to work how I'd like after trying a few things.
I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.
I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:
Quote:
SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration, DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration, DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,
[Code] ....
Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.
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?
DELETE Master.* FROM Master WHERE (((Master.glm_series)=506) AND ((Master.glm_account) In (SELECT glm_account from glj )) AND ((Master.glm_prft_ctr) In (SELECT glm_prft_ctr from glj)));
The problem is it is trying to delete all the records with the account numbers = and ALL the records with profit center equal. I am getting 2564 records instead on 147 records.
The two tables are related by glm_account AND glm_prft_ctr - BOTH must be equal in order to delete
If I do an INNER Join or add the other table to the Query it gives me "Cannot Delete Records from Specified Table"
I have a Table that is used to collect data from client’s phone calls. There is a field for the Client’s name and another for the date the client called (There are a whole lot more fields, but for simplicities sake let’s focus on these two). I have sorted the table on date and indexed the date field. I need to add a “RECORD NO.” field in order to get some queries to run correctly. This field will be autonumber. I would like to have the RECORD NO. field to run in conjunction with the date, that is RECORD. NO. 1 would be the earliest date in the table and the last RECORD NO. would be the most recent date that a client called in. I have verified that the table is sorted by date, but when I add the RECORD NO. field the table reverts to sorting alphabetically by client’s name. The client’s name field is not indexed. I can’t figure out why it is doing this. Any ideas or suggestions as to how to correct this? Thanks.
This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.
I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).
That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.
I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.
Can someone please post the solution asap. Thanks so much for your help.
Hi I have a parent-child base table (accounts) with the following fields: - id - parentid - name every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child) different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....
I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.
Have the data settings for the form all allow=true And tried the VBA allowedits=true on form open but the form is still locked. Any suggestions about why? Is updating two tables from a two-table query not allowed or??? Thanks,
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
When I started building forms to fill my junction tables, the example that I looked at based them all off of queries. That's what I've done for the most part. However, this latest subform that I've made and embedded into a main form is based off of a table. Will that cause problems for me down the road? I'm not so far with it that I can't redo it off of a query, I just don't want to!
