Sorting table - using sql statements

[CuriousGeorge]

Hey,

I have a table where one of the fields contain a time variable. Since files are added randomly to the database and table, the field containing the time variable is not sorted i.e. in ascending order.

I know it is possible to sort the time variable field in the table using a query, however, since it is only sorting that specific field, is it possible in some way when sorting that field to also move the rest of the table in the same way.

What i mean is; if sorting the time field i.e moving row 5 to 2 i also want the remaining fields data on row 5 to move to 2. Even though the time field is the only one being sorted, the rest of the fields data are moved in sync.

Is it possible to do in some way?

Thank u

 

[hgus393]

Hey,

I have a table where one of the fields contain a time variable. Since files are added randomly to the database and table, the field containing the time variable is not sorted i.e. in ascending order.

I know it is possible to sort the time variable field in the table using a query, however, since it is only sorting that specific field, is it possible in some way when sorting that field to also move the rest of the table in the same way.

What i mean is; if sorting the time field i.e moving row 5 to 2 i also want the remaining fields data on row 5 to move to 2. Even though the time field is the only one being sorted, the rest of the fields data are moved in sync.

Is it possible to do in some way?

How do you read the data? You could sort in that step otherwise do a sort like this:

Set rst = CurrentDb.OpenRecordset ("Select * From tbl1")
rst.Sort = "[Time]"

Cheers

Rob

 

[CuriousGeorge]

Each file which is read into the table is actually already sorted. What happens though is that when more files are added to the table the total time field within the table may not be sorted anymore since each individual file could have been recorded at different times. So the sorting statement has to be inserted after the first file has been added.

Do i need to define a recordset for the code u provided?

Cheers

 

[gemma-the-husky]

Hey,

I have a table where one of the fields contain a time variable. Since files are added randomly to the database and table, the field containing the time variable is not sorted i.e. in ascending order.

I know it is possible to sort the time variable field in the table using a query, however, since it is only sorting that specific field, is it possible in some way when sorting that field to also move the rest of the table in the same way.

What i mean is; if sorting the time field i.e moving row 5 to 2 i also want the remaining fields data on row 5 to move to 2. Even though the time field is the only one being sorted, the rest of the fields data are moved in sync.

Is it possible to do in some way?

Thank u

CuriousGeorge - what a curious question. have you not tried this - sort a table on one field/column. you will see that the rows do move with the column.

 

[spikepl]

It seems you should spend some time playing with SQL and understanding what it's is all about.

Your question is weird. Sortorder in tables is 1. irrelevant and 2. a thing not to be relied upon in any way.

Bob Larson provided an excellent description of this here: http://www.access-programmers.co.uk/forums/showpost.php?p=1049757&postcount=3
Read that post.

 

[vbaInet]

To be honest I think you need to read about Queries:

http://office.microsoft.com/en-us/access-help/CH010064577.aspx

From your other threads I can infer that you're a programmer (in a different language, C or C#) and you're using Access with the same mindset without doing any reading on the actual program. It will be worth your while picking up a good book on Access and using online tutorials to support, functionx.com is a good one. The question being posed here is a very basic one.

Also, try not to think of Access as Excel. They do different things.

 

[CuriousGeorge]

yeah, figured out that its quite easy just to sort directly by right clicking on the table field.

Guess im used to coding everything from scratch and not using built in functions, guess i'll have to take a look at it.

Thanks anyways

Cheers

 

[gemma-the-husky]

george

although an access table looks like a spreadsheet, it's a different beast. an access table is a set of rows of simiilar data,but the order in which it is presented is insignificant to access

that's why each row miust be handled individually - you can't drag values from row to row, as you can in excel, and you can't use the previous/next row for anything - there IS no logical previous or next row.

the benefits of this trade-off far outweigh the drawbacks, though for handling data.

the "set" logic assoociated with databases makes the combining of tables/queries into other tables/queries and filtering these into subsets, a very powerful tool for handling large quantities of data.

it is better to try and thing of the data in terms of the "sets" of data though, and not in terms of each row. So deriving an overal total for the set is immiediate and trivial - but deriving a running total line by line is awkward and cumbersome.