General :: Updating Sort Order Of Records

Nov 10, 2014

I have a table with entries describing a sequence of Tasks (tblTask)

tskID TaskDescription
10 Clean Room
12 Wash Dishes
etc

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

View Replies


ADVERTISEMENT

General :: Sort Order For Topmost One-to-many Relationship

Dec 6, 2013

I am currently studying Robert Jennings book entitled Using Microsoft Access 2007 Special Edition. In reading about specifying of the sort order and top value limits established by inner joins (pg 455), the author makes this statement.Access displays query result sets in the order of the index on the primary key field of the table that represents the one side of the top most one-to-many relationship of query tables.An example of a query with Orders, Order Details, and Products tables displays rows in productID (the primary key field in the Products table) sequence. He says that it does so because the Products table has a one-to-many relationship with the Order Details table and indirectly with the Orders table. I do not understand why the Products one-to-many relationship with the Order Details table is ranked as the top most relationship in that the Orders table also has a one-to-many relationship (OrderID is the primary key field) with the Order Details table and an indirect relationship with the Products table. The Order Details table has two primary keys (ProductID and OrderID). My attempts to change the sequence in which the table relationships were formed as well as the location of the tables in the database had no effect on the result.

View 2 Replies View Related

Subform Records Change Sort Order

May 8, 2005

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.

View 1 Replies View Related

Queries :: SELECT Records From A Table Based On IN Clause And Sort Them In Order

Jan 4, 2014

WinXPPro Sp3
Access 2007

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

List_Order
3
1
15
5
4
12
2
7

View 3 Replies View Related

General :: Comment History Field To Appear On A Form - Change Sort Order Of Results

Aug 28, 2013

I have after much stress gotten my comments history field to appear on a form.

The following code gives me what I needed

Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))

I have only one simple thing left, that is perplexing to say the least.

Some comment fields will be many short notes, spread over time.

My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.

View 5 Replies View Related

Sort Order

Oct 10, 2005

How do you change a file's sort order from ascii to international?

View 3 Replies View Related

General :: Work Order System - Empty Records Showing In Table

Apr 3, 2014

I have a work order system that people use but it somewhat randomly puts blank records into my table. I've added a lot of validation checks when submitting, closing and resetting the form and limited the way people can exit out of the form to fight this issue but it still happens.

View 3 Replies View Related

Problem With Sort Order

Feb 11, 2007

Hi all, I am new to this site and I am hoping that I might be able to get some help with my database which records address information of people reqesting a brochure or making a hotel booking. The problem that I am having is that the addresses are sorting in post code order, I have selected remove filter/sort and this does not do anything. I would like the records to stay in the order that they are entered at I also use this for a merge with a brochure/deposit letter and it is much simpler if I can merge a block of entries.

Many thanks in advance Pat.

View 6 Replies View Related

Dynamic Sort Order

Apr 18, 2007

Supposed I have the following tables:

TableMonthlyReport consists of the following fields:
*IDStore
*Year
*Month
*IDCatalog
*IDItem
Quantity

TableGoods:
*IDCatalog
*IDItem
ItemName
IDSupplier

The asterisks indicates the index keys. While TableMonthlyReport has 2 links of relationship with the TableGoods on IDCatalog and IDItem.

I have a data entry front end for monthly report. I have no problem to have the record source for this purpose.

I just need that the sort order of the item (in the front end) in a way that they could be managed differently on different period. For example, in March to April 2007 I want the items shown in the following order: A B C D ... While in May to August 2007 I want the items shown as B D A C ....

I tried to create an order table just like this:
TableGoodsOrder:
*IDCatalog
*IDItem
*ValidStart
ValidEnd
OrderItem

It is linked with the TableGoods on IDCatalog and IDItem.

The first order list (March - April 2007) will have the ValidStart of 703 and ValidEnd of 704. While the next order list will have the ValidStart of 705 and 708. The last 2 digits stand for the month, while the first digit is to indicate year.

The SQL query for the record source works but I can not enter any value. So, the idea of having a special table for sort order is not OK.

Can anybody give me a clue for the solution?

View 2 Replies View Related

Sort Order Problem

Dec 6, 2005

I have a table in MS Access with records including dates. The dates are date/time data type and formatted as short dates. They display properly in the table.

However, when I run a query (SQL shown below), it sorts the dates strangely.

Any ideas??

SELECT DISTINCTROW Format$(tblChat.dtDate,'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC
FROM tblChat
GROUP BY Format$(tblChat.dtDate,'Short Date')
ORDER BY Format$(tblChat.dtDate,'Short Date');

Here’s an example of the sort order in the query.

10/31/2005
11/1/2005
11/10/2005
11/11/2005
11/12/2005
11/14/2005
11/15/2005
11/16/2005
11/17/2005
11/18/2005
11/2/2005
11/21/2005
11/22/2005
11/23/2005

View 2 Replies View Related

Sort Order Question

Nov 3, 2006

Is there a way to sort dates using "Decending" but have blank dates stay on top?

I have a listbox based on a qurery that displays all pending and completed jobs. The query sorts (Ascending) the "Job Complete" date. This conveniently puts all of the pending jobs with null values at the top of the list. The bad part is that this puts recently completed jobs at the very bottom of a (thankfully) long list. Is there a way around this?
Thank you!

View 5 Replies View Related

Specific Sort Order

Mar 2, 2005

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.

Colonel
Lt. Colonel
Major
Captain
1st Lt.
2nd Lt.

View 8 Replies View Related

Need Weird Sort Order

Mar 14, 2005

I have a query that fills a report that I have in an Access 2000 database. This report needs to be sorted by the following:

Code:ORDER BY tblPrimaryDept.PDName, tblSubDept.SDName, tblKeyStrategy.KSID, tblKeyInitiative.KIID, tblKeyDeliverable.KDID, tblSubDeliverable.TargetDate, tblQuarters.QID;

It's working fine, but .... (There's always a but)

tblKeyStrategy.KSID can be null, so therefore, it is sorted by null records, then ascending order tblKeyStrategy.KSID. What they client wants is it to be sorted by tblKeyStrategy.KSID first, then the null value ones.


ie:
So as it stands it currently does this:

Finance
Finance
Finance 1
Finance 2

Now they want it to do this:

Finance 1
Finance 2
Finance
Finance

Is there any way this can be done? I've been looking, but haven't seen anything regarding this.

Thanks for any assistance you can give me on this.

View 13 Replies View Related

General :: Updating Records From Unbound Form

Jul 24, 2012

i have developed an access database and working on interface through forms & reports .i needed that a user must have to click save button to update the data and i have found that without an unbound control its hard to achieve functionality .so i want to know step by step how to update records using unbound controls and a save button .

View 6 Replies View Related

Access Changes Sort Order On Paste

Mar 7, 2008

Hi all,

I'm quite new to access, and have a pretty straightforward problem (I hope):

I have a table with 300 records in 10 columns. I want to:
1. filter the data based on column A (gives me 150 visible records)
2. Copy from an excel spreadsheet which exactly matches the access table (single column only, 150 rows)
3. Paste into a new column I created in the access table

Unfortunately, when I hit paste in access, it loses the filter and pastes the data into the complete dataset (i.e. into the wrong records).

I've no idea why access won't keep the filter I set. I also tried numbering the records and sorting them based on this new column. Again, Access lost the sort order before pasting the data.

Help!

I really need to be able to paste my excel data into the table in access!

Can anyone shed some light for me?

Thanks in advance,

Lee

View 4 Replies View Related

Sort (order) Data On Forms

Apr 12, 2005

Hello,

This is related to "Old_value, new_value" thread I posted yestarday (Module&VBA).

I have a problem i missed yestarday. The code it's vorking great, but it allways takes the showed new_value and makes it old_value for the new record, and this is a problem, because the showed record is not the last for that customer.
So, I was thinking that if I make a new Autonumber field in my table, I could sort descending the values and this way the input form will allways show me the last entered data for that person.
As I noticed from a long time ago, if I sort a table that allready has a form, the form won't show the records sorted. Why? It's necessary to make the form again? I tried to order the data on the form from Properties window ->Data->Order by, but the ordering is Ascending and I need it Descending!
Can enybody help me?

Thanx,
Attila

View 1 Replies View Related

Customize The Sort Order Of A Combo Box

Dec 7, 2005

i have a combo box on a payment form which shows existing permit #s. i'd like to make the sort order show the most recently added permit first. i clicked on the build button next to RowSource on the data tab of the properties of the combo box which opens the query my combo box is based on. there are only 3 sort order options...ascending, descending, & not sorted. can this be done?

the permits form has a button to open the payments form (payments can also be opened alone too - ie. if somone applies for a permit today but pays for it tomorrow...). usually this will not be the case but that is why i have to keep it either combo or list.

all help is appreciated.:)

View 3 Replies View Related

Sort Order Of Unbound List

Sep 15, 2006

I need to manage a list of, in this case "medications", with this list the most common "meds" are on the top of the list. there will be at times new "meds" added that should be at the top of the list. I want to create a unbound box that will let me drag the "meds" up or down in the list. The best emample i know of is the way you can set the "Tab" fields on a form to move the tabs stops.

View 4 Replies View Related

Change Sort Order On Form

Nov 29, 2004

How do I change the sort order to display on a form using an option button?

Thanks!

View 1 Replies View Related

Saving Sort Order In A Form

Apr 29, 2008

I am working with Access 2003 and want to save the sort order in a subform. The new sort order would be different from that of the underlying table. The Order By property has been set but isn't working the way I expected. I have tried replacing the underlying table with a query but that hasn't worked out either. I don't want to change the order of the table because other programs that access it would be affected.

I've been reading about an Order By On switch that might do the trick but apparently it's not contained in Access 2003. There must be some other way to skin this cat!

Thanks in advance.

View 4 Replies View Related

Queries :: Specific Sort Order

Sep 13, 2013

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?

View 3 Replies View Related

Access Query Sort Order Every Other One

Sep 29, 2014

I am trying to create a work schedule, some customers are scheduled for morning and others in the afternoon. I am trying to figure out how to sort AMs and PMs so I get a line with every other one.

View 2 Replies View Related

Combo Box - Custom Sort Order

May 22, 2014

I have a combo box with the following entries:

Document
Other
Process
Validate
Verify

I want the list sorted so that "Other" is at the bottom. Everything else is alphabetical ascending. Is this possible?

View 4 Replies View Related

Sort Order With Null Values

Aug 22, 2012

I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.

A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using

IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity])

This code is repeated for each of the six fields with the relevant field name in place of 'property'.

Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub

View 14 Replies View Related

Exporting Data With A Custom Sort Order

Jun 7, 2007

Hi,

I have a Form which contains a datasheet and I have some code which allows users to custom sort this datasheet. Another table based on this datasheet is then exported as a csv file.
However, the data is obviously not exported in the same order as the datasheet and it is quite critical for the application to be a sucess that it is exported in the custom order chosen by the user.

I have done some re-search, searching groups, forums etc and found the solution of having a 'sort column' in the table the datasheet relates to, I can then use this in the query that exports the data. My only problem with this is I had to manually fill in this sort column in the datasheet, once I had decided upon my final sort order. Is there anyway to fill this column automatically based on a row's position in a datasheet?

Many thanks in advance

View 4 Replies View Related

Autonumber Interfering With Sort Order In Report...?

Nov 16, 2005

Need help pls

I'm designing a report that should be sorted by field A, B, C, D, or a combination of BD or CD.

Problem is (it seems) the report ignores the sort order and lists the results in ascending order of the autonumber field - 1,2,3,4,5 etc.

The reports run off queries and the queries work fine - they sort the fields in the proper order.

Is the autonumbering somehow interfering with the sort order in the report?

HELP!!!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved