How do I Order a Table?

[mathare]

I know it's a fairly basic thing but I need to know how to order a table.

I have queries which I have ordered using the ORDER BY keyword but I have never attempted this with tables and I'm not really sure where to start.

I'm sure someone is now going to give me a very simple answer. I hope so...

 

[MattThat]

(Rasses eyebrows at totally new thought).... I'm note sure you can.

I would have built a quary containing every field on the table and use that to build my forms as they can be used in the same way.
Help any?

-Matt

 

[beetee]

Tables *do* have a filter property. You can change the table's filter and change it's default sort order and record selection.

However, you need to bring much more context into your question to get a useful answer.

 

[mathare]

Sorry, yeah. Awfully written question. That's Friday afternoons for you.

I am using select queries to pick certain fields from tables. These queries I am using to populate other tables by opening recordsets on the query and table and copying from one to the other. I am not always copying whole records, sometimes just bits of fields.

I then want to go through the new tables and order them. Because of the way the code works it won't always be in order even though the query was ordered. With copying just certain bits of fields I can no longer guarantee the order. So I want to perform something like and ORDER BY on my new tables.

Can it be done?

 

[beetee]

I guess I would wonder why you need to order the tables.

As Matt suggested you create a query based on a table, and order that, and use that... e.g.

Select * from MyTable OrderBy [MyTable]![MyField]

However, if you just want the tables to be ordered in a certain way, so when the user clicks on the table it's ordered, then you can use the filter properties. You can even order a table by right clicking on the header and picking a sort order.

Do either of these suggestions work for you? if not, why not?

HTH

 

[mathare]

I want the table ordered because I am a lazy coder, basically.

I want to ensure the table contains no duplicate entries. Adding Distinct into my original query is no good because when I copy bits out of the fields there is a reasonable chance that the same name may be copied out of several fields. So the idea was to tidy the table up at the end.

I was going to do this the quick way by ordering the table then doing something like

rstMyTable.MoveFirst
MyString1 = rstMyTable.Fields(0)
while rstMyTable.EOF <> True
rstMyTable.MoveNext
MyString2 = rstMyTable.Fields(0)
if MyString1 = MyString2 then
   rstMyTable.Delete
else
   MyString2 = MyString1
end if
wend

So we check a record to see if it matches the next one, if so we delete else we leave it and continue down the table. This requires the records to be ordered. Like I said, it's lazy coding but I have used this on ordered queries so I thought I would ask if there was a quick way to order a table.

This is all in the VB code and is to be run before the user gets anywhere near the table. I want the table the user sees to be free of duplicate records.

I suppose the other way is to use a select distinct query on my new table, copy into yet another table using a recordset to recordset copy and then delete the select distinct query and the table it was using as a source. But that seems like a bit more hassle than I was planning for.

 

[beetee]

I think you may want to consider using intrinsic constraints.

For example, rather than looping through the records to search for duplicates, create an index using the same field(s) and set it's unique property. When you execute your append or update queries, they will indicate that some records could not be updated or appended due to duplicate keys.

If for other reasons you still prefer to use code, you can still use an index. Create an index in the table based on the field. Then, prior to traversing the recordset, set the index property of the recordset to the index, e.g.

rstMyTable.Index = "MyIndex"

then, the move first method will find the first record sorted by MyIndex.

HTH

 

[cranebill]

On the thread heading i would say from a furniture dealer and with a major Cedit Card, I know, wasnt any help but maybe made ya smile

Bill