Re: Make Table Query - Sorting Errors

Fred,
Phil, Tom, and John gave you the anser ------- Tables are not sorted.

You sort in a query, form or report. You can open a table and click on the
A-Z or Z-A icon for a sort.

You can do a compound sort in a query. In the design view of the query
place the fields you want to sort by in left to right order. Select the sort
order for each field.

You can also be more selective in sorting by creating a field using parts of
a field data like ---
Expr1: Left([XX],3) & Right([YY],1) & Left(Right([ZZ],5),2)

"Tom Ellison" wrote:

Dear Fred:

I believe Duane just gave you what you need. It is to have your process
perform the necessary sorting.

Tom Ellison


"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86BB9279-9DA2-4342-8AE1-C14C53B9779E@xxxxxxxxxxxxxxxx
Don't really understand your comment.

The make table query creates a table named "tblRecallQuery". Each time
this
query is run, the old table "tblRecallQuery" is deleted first. So there
are
never any records with the table delete.

The module that I wrote, and now posted for you to see, just goes through
that table one row at a time and compares the value of COMPNO to the value
in
the previous row.

You said that having a table appear to be in a certain order is not the
same
as the table being stored in that order.

So is it possible to store the table in a certain order? That is
essential
to the proper operation of the module code.

Or, can I have the module read the table and then rewrite it in a sorted
order?

Thanks for your help...





"Tom Ellison" wrote:

Dear Fred:

Even if they are sorted, a table with deleted entries in it would replace
those first, then add new records at the end. Sorting, whether it occurs
or
not, will not fix this. But, it's unimportant.

Tom Ellison


"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2F3278C3-4C79-4BCD-9CC5-8561BE90D75D@xxxxxxxxxxxxxxxx
If I change the query to a "select query" rather than a "make table
query",
the records seem to display in the correct sorted order.

Is it correct then to say the the "order by" command in the query is
only
applicable to a select query, and if you use it for a "make table"
query,
the
results may or may not be sorted correctly?



"Fred" wrote:

In the table, there may be more than one record with the same value of
"CAMPNO". If so, I concatenate one of the fields of the record so
that
the
resulting table only has one record for each value of "CAMPNO". To do
this,
the module reads the table one row at a time and compares the value of
"CAMPNO" to the previous value of "CAMPNO". Using this technique
requires
that the table first be in "CAMPNO" order.

Is there a command that I can use in the module code to first sort the
table
by "CAMPNO"?

Thanks...



"Tom Ellison" wrote:

Dear Fred:

It does often appear that the rows are placed in a table in an
order,
when
done as you have done this.

It is not going to be reliable. It is not meant to be reliable.

If you ever delete a row from your table, it's going to re-use that
position
in the "internal" ordering of the table. This is a specific
explanation of
what others have stated.

If it is somehow desirable to have the rows in a specific order, you
can do
this. Indeed, this is an excellent way to improve performance in
many
cases.

You cannot use Jet to do this. Well, you can, but you must compact
and
repair every time the table changes to make this work. It's
probably
not
practical.

Instead, use MSDE or Express or SQL Server (different versions of
the
same
thing) and create a clustered index. No need to add new records
already
sorted, it will sort them as they arrive.

This is a trick, and somewhat "violates" the rule that rows in a
table
are
in a "bag." In many instances, it is important.

You do not say why this is important to you. Perhaps you could fill
us
in a
bit more.

Since you can always index the rows into any needed order, or sort
them
when
you query them, why is this of any concern to you? Knowing why may
help us
understand your reasoning.

Tom Ellison


"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:426E9379-05DF-40E8-A0E8-CA1571BB0BFC@xxxxxxxxxxxxxxxx
Duane,
One of us doesn't understand...not sure if its me or you!

Let me explain again...forget about all my talk about a module.

I have a make table query that is supposed to sort in ascending
order
on a
text field named CAMPNO.

When I run the query multiple times with the same selection
criteria,
the
number of records returned is constant, but they are not always
sorted
correctly on the field CAMPNO.

I have the Sort box checked in the design view, and the SQL view
shows the
ORDER BY command is in there.

So why doesn't the sort feature properly work?



"Duane Hookom" wrote:

I was referring to your statement "This causes a major problem,
because I
then run a module on that table that requires that the table be
sorted
properly to begin with". I would not do this. I would run a
module
on a
query that sorts a table into a required sort order.


--
Duane Hookom
MS Access MVP
--

"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C2E49AD3-D7E1-492E-88BD-B031D4FF67F8@xxxxxxxxxxxxxxxx
Duane,

I do have an "ORDER BY" clause in the query...here's the SQL of
the
Query:

SELECT Recalls.CAMPNO, Recalls.RECORD_ID, Recalls.YEARTXT,
Recalls.MFGTXT,
Recalls.MFGNAME, Recalls.MAKETXT, Recalls.MODELTXT,
Recalls.MFGCAMPNO,
Recalls.COMPNAME, Recalls.BGMAN, Recalls.ENDMAN,
Recalls.RCLTYPECD,
Recalls.POTAFF, Recalls.ODATE, Recalls.INFLUENCED_BY,
Recalls.RCDATE,
Recalls.DATEA, Recalls.RPNO, Recalls.FMVSS,
Recalls.DESC_DEFECT,
Recalls.CONEQUENCE_DEFECT, Recalls.CORRRECTIVE_ACTION,
[Database
Version].Database_Date INTO tblRecallQuery
FROM Recalls, [Database Version]
WHERE (((Recalls.MFGTXT) Like "*general motor*" Or
(Recalls.MFGTXT)
Like
"*toyota*" Or (Recalls.MFGTXT) Like "*chrysl*" Or
(Recalls.MFGTXT)
Like
"*ford*" Or (Recalls.MFGTXT) Like "*nissan*" Or
(Recalls.MFGTXT)
Like
"*honda*") AND ((Recalls.RCDATE)>"20040930"))
ORDER BY Recalls.CAMPNO;

"Duane Hookom" wrote:

I would never trust table records to be in any order unless I
specify
an
ORDER BY clause in a query.

--
Duane Hookom
MS Access MVP
--

"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2D2FCBC7-2B44-496D-BDA7-3F079444A602@xxxxxxxxxxxxxxxx
I have a make-table query that is not sorting consistently.
I
am
sorting
on
one of the fields in the query, CAMPNO. I ran it once, and
manually
looked
at the table it created. It was sorted properly on CAMPNO
until I
got
to
row
260. In row 259, CAMPNO = 05V104000, and in row 260,
CAMPNO=05V055000.

I then reran the query without any changes. In the
resulting
table,
in
row
409, CAMPNO-05V554000 and in row 410, CAMPNO=05V270000.

This causes a major problem, because I then run a module on
that
table
that
requires that the table be sorted properly to begin with.

Is this a known problem? I have Access 2000 (9.0.3821
SR-1).
If
so,
is
it
corrected in a patch to this version of Access, or in a
later
version
of
Access?

Can you tell me the command to put in the module to sort
this
table?
I
can
try that and see if the command will consistently sort the
table,
since
the
query is not.

Thanks for any help...










.

Relevant Pages

  • Re: Make Table Query - Sorting Errors
    ... resulting table only has one record for each value of "CAMPNO". ... Is there a command that I can use in the module code to first sort the table ... you query them, why is this of any concern to you? ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... Is it correct then to say the the "order by" command in the query is only ... resulting table only has one record for each value of "CAMPNO". ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ... MS Access MVP ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Creating a unique customer code with a function
    ... There is no facility possible in a query to "loop" and do anything of the ... A query could, however, rank them and assign values based on that. ... guess it could be an alpha sort based on the complete company name. ... Tom Ellison wrote: ...
    (microsoft.public.access.queries)