Re: Make Table Query - Sorting Errors

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
    ... 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: Make Table Query - Sorting Errors
    ... MS Access MVP ... 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 ... I have a make table query that is supposed to sort in ascending order ...
    (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
    ... sorted, it will sort them as they arrive. ... you query them, why is this of any concern to you? ... correctly on the field CAMPNO. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)