Re: Make Table Query - Sorting Errors

Tech-Archive recommends: Fix windows errors by optimizing your registry

Pardon me,

If you are saying that you expect the MakeTable query to put the records in
the table in a specific order, then you are wrong. It may happen that it
does create them in a specific order. However, that does not mean that they
are stored in that order. I would expect that the order by clause in a make
table query would be ignored.

Records in tables do not have an order. They are like marbles in a bag. If
you want to have an order then you need to use a query on the table you have
created that has an order by clause. If you are doing that and the records
in the query of the created table are not in order then there is a problem.


"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: Make Table Query - Sorting Errors
    ... Put bluntly, do not expect Access to create a table in a sorted order, even if the MAKE TABLE query is set up to sort it. ... Instead base everything else, like your module, on a SELECT query that has all of the records you need, and sorts them into the order you require. ... 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. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)