make table problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

SELECT [local mailing table].* INTO temp
FROM [local mailing table]
ORDER BY [local mailing table].[last name], [local mailing table].[first
name], [local mailing table].[middle initial];

what I am trying to do is create a table in last/first/mid order. The data
comes out sorted, but in blocks so I may have a - f then a few w's then back
to a block of b's.

Any Ideas?

Thanks
 
Dear Pookie:

Don't bother sorting the data in the make-table query. Create an
index on the temp table instead, or sort the query or RecordSource you
base on this table.

Better yet, don't use a make-table query at all. Design the table
with a primary key and simply append to it instead. This gives you
complete control over its functional attributes.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
SELECT [local mailing table].* INTO temp
FROM [local mailing table]
ORDER BY [local mailing table].[last name], [local mailing table].[first
name], [local mailing table].[middle initial];

what I am trying to do is create a table in last/first/mid order. The data
comes out sorted, but in blocks so I may have a - f then a few w's then back
to a block of b's.

Any Ideas?

Thanks
Click to expand...

Don't assume that a Table HAS any order. It doesn't. It's an unordered
bucket of data.

Your temp table does not need to exist AT ALL.

You can change your MakeTable query into a simple SELECT query (just
remove the INTO temp clause); this Query can then be used as the
recordsource for a Form, as the recordsource for a Report, it can be
exported to text or to Excel, it can be used as the basis for other
queries - anything you'ld want to do with a Table can be done with the
Query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
thank you for the reply, and good advice

P.B.

John Vinson said:
SELECT [local mailing table].* INTO temp
FROM [local mailing table]
ORDER BY [local mailing table].[last name], [local mailing table].[first
name], [local mailing table].[middle initial];

what I am trying to do is create a table in last/first/mid order. The data
comes out sorted, but in blocks so I may have a - f then a few w's then back
to a block of b's.

Any Ideas?

Thanks
Click to expand...

Don't assume that a Table HAS any order. It doesn't. It's an unordered
bucket of data.

Your temp table does not need to exist AT ALL.

You can change your MakeTable query into a simple SELECT query (just
remove the INTO temp clause); this Query can then be used as the
recordsource for a Form, as the recordsource for a Report, it can be
exported to text or to Excel, it can be used as the basis for other
queries - anything you'ld want to do with a Table can be done with the
Query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
John Vinson said:
Don't assume that a Table HAS any order. It doesn't. It's an unordered
bucket of data.
Click to expand...

I think the accepted term for a table (relation) lacking a key is a
'heap'. Bucket has better imagery, though <g>.

However, a Jet (MS Access) table with a primary key (PK) is considered
to have a logical order (the PK) and a physical order (the clustered
index, which in Jet's case happens to be the same as the PK). I'm
assuming a newly-compacted database, of course, new subsequent rows
are appended in date/time order and only physically re-ordered on disk
when the database is next compacted.

As usual, a demo:

CREATE TABLE MyTable (
MyKeyCol CHAR(3) NOT NULL,
MyDataCol CHAR(10) DEFAULT '{{NK}}' NOT NULL,
CONSTRAINT pk_mytable PRIMARY KEY (MyKeyCol)
)
;
INSERT INTO MyTable (MyKeyCol, MyDataCol)
VALUES ('001','One')
;
INSERT INTO MyTable (MyKeyCol, MyDataCol)
VALUES ('003','Three')
;
INSERT INTO MyTable (MyKeyCol, MyDataCol)
VALUES ('002','Two')
;

Now if I get a dataset without specifying an ORDER BY clause:

SELECT MyKeyCol, MyDataCol
FROM MyTable;

I get

001, One
003, Three
002, Two

being the date/time inserted order. If I do a compact and re-run the
query, I get

001, One
002, Two
003, Three

In summary, without explicitly asking for an order, no order can be
guaranteed, even where an index exists, however the order is
predictable.

Jamie.

--
 
Back
Top