order records alphabetically (ascending) 2

[davyre]

Hi,
I have a code to write into a table called TmpConcatenateParts, the data are from two different listbox value.
So sometimes listbox 1 has value and listbox 2 does not, sometimes listbox 1 does not have value where listbox 2 has, or both listboxes have values.
The values will be inserted into TmpConcatenateParts triggered by a button.
The problem I have is that in that table, the records should be in order based on Vendor name (alphabetically).
How can I make a query to sort/order my table?
I tried to manually clicked the table and click "Sort A to Z", but it does not work (it did work as only appearance, but the actual table did not sorted that way).
Any help? Thanks!

 

[PHV]

There is NO sort order for a table.
If you want sorted records then use a query with the appropriate ORDER BY clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Listbox1
|Part | Qty | Vendor |
|001  | 5   | A      |
|002  | 2   | B      |
|003  | 8   | C      |

Listbox2
|Part | Qty | Vendor |
|001  | 3   | A      |
|101  | 5   | A      |
|301  | 3   | A      |

TmpConcatenateParts (What is should be)                 TmpConcatenateParts (What now it is)
|Part | Qty | Vendor |                                  |Part | Qty | Vendor | 
|001  | [highlight #8AE234]8[/highlight]   | A      |                                  |001  | [highlight #8AE234]8[/highlight]   | A      | (This is correct as it combines qty of same part)
|101  | 5   | A      |                                  |002  | 2   | B      |
|301  | 3   | A      |                                  |003  | 8   | C      |
|002  | 2   | B      |                                  |101  | 5   | A      |
|003  | 8   | C      |                                  |301  | 3   | A      |

Any further info, just ask me. Thanks!

 

[PHV]

So, what is the problem ?

 

[davyre]

Hi, PHV
so the problem is how to use Order By to rearrange my table to be ordered based on vendor?

 

[PHV]

SELECT * FROM TmpConcatenateParts ORDER BY Vendor, Part

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

gosh I solved my problem. Thanks, but using SELECT * FROM etc etc, that wont actually rearrange the table, correct?
It is just selecting all the records ordered by vendor, then the next step might be using a recordset to paste all the ordered records.
Just curious, is there any way to actually rearrange the actual table? I searched on net and some say using CreateQueryDef. Is it possible? I tried but it does not work for me
thanks!

 

[MajP]

Just curious, is there any way to actually rearrange the actual table?

Technically you can put them in an order, but without a sort by clause you cannot ensure you can pull them out in any orders. Worrying about this is a waste of time and serves no purpose. Rarely do you interface directly to the table. It is almost always done through a query. If you want to ensure that the order they go in is the order they come out, then use a datetime stamp or some incremental autonumber. Then sort on this field. You should conceptually think that a table is a bag of marbles. When you access the items it is on you to pull the ones out that you need and put them in order.