Make Table Query - Sorting Errors

[Guest]

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...

 

[Duane Hookom]

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

 

[Guest]

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]

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
--

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;

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

 

[Guest]

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?

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
--

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;

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
--

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...

 

[Duane Hookom]

When you view the datasheet view of the query, do the records display in the
proper order? If so, then I would consider the "feature" is working
properly.

--
Duane Hookom
MS Access MVP
--

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?

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
--

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;

:

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
--

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...

 

[Phil]

I think it is you that does not understand what he is saying.

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. You make a mistake
when you think Access will actually do what you tell it to do in this case.

Do not bother to sort the table itself, as Access just is not going to
do it reliably. 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.

The drawback is that the sorting will happen at runtime, so whatever you
are trying to do is going to take a little longer.

The advantage is it may actually work correctly.

As far as asking why, don't bother. Bill doesn't explain himself.

Phil

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?



:

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
--

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;

:


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
--


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...

 

[John Spencer]

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.

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?

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
--

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;

:

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
--

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...

 

[Tom Ellison]

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

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?

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
--

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;

:

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
--

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...

 

[Guest]

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...

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

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?

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
--

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;

:

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
--

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...

 

[Tom Ellison]

Dear Fred:

The query to concatenate the CAMPNO values will need to be LOOKING at the
table sorted that way. I suggest it does not require the table to actually
be in that order.

Sorting a table is completely different from forcing it to be stored in that
order. Sorting a table creates an appearance of order, not the order itself
inside the table. You can have multiple different sorts of the same data.

If you post the query you are using to do this, I beleive we can get it to
function without the requirement you seem to feel is necessary.

I suspect you're looking at your database as a spreadsheet. It is not. It
is far more powerful, and complex. But, for you, this is a good thing. I
expect you will not need to continue thinking in quite the same way you have
been doing.

Tom Ellison

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...

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

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?



:

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
--

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;

:

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
--

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...

 

[Duane Hookom]

You have yet to provide any clue how you are concatenating within your
module. You have used the term "module" in several posts but seem to be
keeping your code a secret when we continue to question your code or
expectation/assumption in your code.

--
Duane Hookom
MS Access MVP
--

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...

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

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?



:

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
--

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;

:

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
--

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...

 

[Guest]

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?

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...

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

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?



:

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
--

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;

:

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
--

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...

 

[Tom Ellison]

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

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?

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...

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


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?



:

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
--

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;

:

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
--

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...

 

[Guest]

Not trying to keep the module code a secret, it's just rather long....but if
you insist...here it is...

' ****************************************
' * Dimension and declare variable names *
' ****************************************

' ************************
' * Dimension Recordsets *
' ************************

Dim InputTable As DAO.Recordset
Dim OutputTable As DAO.Recordset
Dim MySet As DAO.Recordset

'
'
' ***********************
' * Dimension Databases *
' ***********************

Dim dbcurrent As DAO.Database
Dim mydb As DAO.Database
Dim myworkspace As DAO.Workspace


' *********************
' * Dimension Strings *
' *********************

Dim LLRecord_ID(2), LLCampNo(2), LLMakeTxt(2), LLModelTxt(2),
LLYearTxt(2), LLMfgCampNo(2) As String
Dim LLCompName(2), LLMfgTxt(2), LLBGMan(2), LLEndMan(2), LLRclTypeCd(2),
LLPotAff(2) As String
Dim LLODate(2), LLInfluencedBy(2) As String
Dim LLMfgName(2), LLRCDate(2), LLDateA(2), LLRPNO(2), LLFMVSS(2),
LLDescDefect(2), LLConsequenceDefect(2) As String
Dim LLCorrectiveAction(2), LLDatabaseDate(2), LLYearMakeModel(2) As String
Dim i, ILOOPS, K As Integer
Dim test As String


Set myworkspace = DBEngine.Workspaces(0)
Set mydb = myworkspace.Databases(0)

DoCmd.SetWarnings False

start:

' *****************************
' * Open table tblRecallQuery *
' *****************************
DoCmd.Echo False, "opening tblRecallQuery..."
Set InputTable = mydb.OpenRecordset("tblRecallQuery")




'
******************************************************************************
' * Initialize the counter of the output file flag. After a record is
written *
' * to the output file, K = 1
*
'
******************************************************************************

K = 0

' *******************************
' * Count the number of records *
' *******************************

ILOOPS = InputTable.RecordCount


' *************************************
' * Go to the first record, set I = 1 *
' *************************************

InputTable.MoveFirst
i = 1 ' I = Counter of input records

' ******************************************************
' * Read Record 1 as separate variables for each field *
' ******************************************************

'
' Subscript of "1" for these variables means the previous record
' Subscript of "2" for these variables means the current record
'
If IsNull(InputTable![RECORD_ID]) Then LLRPNO(2) = " " Else
LLRecord_ID(2) = InputTable![RECORD_ID]
If IsNull(InputTable![CampNo]) Then LLCampNo(2) = " " Else LLCampNo(2) =
InputTable![CampNo]
If IsNull(InputTable![MAKETXT]) Then LLMakeTxt(2) = " " Else
LLMakeTxt(2) = InputTable![MAKETXT]
If IsNull(InputTable![MODELTXT]) Then LLModelTxt(2) = " " Else
LLModelTxt(2) = InputTable![MODELTXT]
If IsNull(InputTable![YEARTXT]) Then LLYearTxt(2) = " " Else
LLYearTxt(2) = InputTable![YEARTXT]
If IsNull(InputTable![MFGCAMPNO]) Then LLMfgCampNo(2) = " " Else
LLMfgCampNo(2) = InputTable![MFGCAMPNO]
If IsNull(InputTable![COMPNAME]) Then LLCompName(2) = " " Else
LLCompName(2) = InputTable![COMPNAME]
If IsNull(InputTable![MFGTXT]) Then LLMfgTxt(2) = " " Else LLMfgTxt(2) =
InputTable![MFGTXT]
If IsNull(InputTable![BGMAN]) Then LLBGMan(2) = " " Else LLBGMan(2) =
InputTable![BGMAN]
If IsNull(InputTable![ENDMAN]) Then LLEndMan(2) = " " Else LLEndMan(2) =
InputTable![ENDMAN]
If IsNull(InputTable![RCLTYPECD]) Then LLRclTypeCd(2) = " " Else
LLRclTypeCd(2) = InputTable![RCLTYPECD]
If IsNull(InputTable![POTAFF]) Then LLPotAff(2) = " " Else LLPotAff(2) =
InputTable![POTAFF]
If IsNull(InputTable![ODATE]) Then LLODate(2) = " " Else LLODate(2) =
InputTable![ODATE]
If IsNull(InputTable![INFLUENCED_BY]) Then LLInfluencedBy(2) = " " Else
LLInfluencedBy(2) = InputTable![INFLUENCED_BY]
If IsNull(InputTable![MFGNAME]) Then LLMfgName(2) = " " Else
LLMfgName(2) = InputTable![MFGNAME]
If IsNull(InputTable![RCDATE]) Then LLRCDate(2) = " " Else LLRCDate(2) =
InputTable![RCDATE]
If IsNull(InputTable![DATEA]) Then LLDateA(2) = " " Else LLDateA(2) =
InputTable![DATEA]
If IsNull(InputTable![RPNO]) Then LLRPNO(2) = " " Else LLRPNO(2) =
InputTable![RPNO]
If IsNull(InputTable![FMVSS]) Then LLFMVSS(2) = " " Else LLFMVSS(2) =
InputTable![FMVSS]
If IsNull(InputTable![DESC_DEFECT]) Then LLDescDefect(2) = " " Else
LLDescDefect(2) = InputTable![DESC_DEFECT]
If IsNull(InputTable![CONEQUENCE_DEFECT]) Then LLConsequenceDefect(2) =
" " Else LLConsequenceDefect(2) = InputTable![CONEQUENCE_DEFECT]
If IsNull(InputTable![CORRRECTIVE_ACTION]) Then LLCorrectiveAction(2) =
" " Else LLCorrectiveAction(2) = InputTable![CORRRECTIVE_ACTION]
If IsNull(InputTable![Database_Date]) Then LLDatabaseDate(2) = " " Else
LLDatabaseDate(2) = InputTable![Database_Date]



' *********************************
' * Concatenate Year, Make, Model *
' *********************************
'
'
LLYearMakeModel(2) = LLYearTxt(2) & " " & LLMakeTxt(2) & " " &
LLModelTxt(2)

GoTo 70: ' Skip this section for now...need to check if this is the
last record of the campaign.
80:


' **********************
' * Open table tblTemp *
' **********************
10:
DoCmd.Echo False, "opening tblTemp..."
Set OutputTable = mydb.OpenRecordset("tblTemp", DB_OPEN_DYNASET) ' Do
we get to this statement?


If K = 1 Then GoTo 100

' ******************************************************
' First let's delete all records in the output table. *
' ******************************************************

OutputTable.MoveFirst
Do Until OutputTable.EOF
OutputTable.Delete
OutputTable.MoveNext
Loop
100:

' *************************************************************
' * Write Record I and LLYearMakeModel to new table "tblTemp" *
' *************************************************************


60:
K = 1 ' Set the flag so we know we've written to the output table.

OutputTable.AddNew

OutputTable![RECORD_ID] = LLRecord_ID(1)
OutputTable![CampNo] = LLCampNo(1)
OutputTable![YEARMAKEMODELTXT] = LLYearMakeModel(1)
OutputTable![MFGCAMPNO] = LLMfgCampNo(1)
OutputTable![COMPNAME] = LLCompName(1)
OutputTable![MFGTXT] = LLMfgTxt(1)
OutputTable![BGMAN] = LLBGMan(1)
OutputTable![ENDMAN] = LLEndMan(1)
OutputTable![RCLTYPECD] = LLRclTypeCd(1)
OutputTable![POTAFF] = LLPotAff(1)
OutputTable![ODATE] = LLODate(1)
OutputTable![INFLUENCED_BY] = LLInfluencedBy(1)
OutputTable![MFGNAME] = LLMfgName(1)
OutputTable![RCDATE] = LLRCDate(1)
OutputTable![DATEA] = LLDateA(1)
OutputTable![RPNO] = LLRPNO(1)
OutputTable![FMVSS] = LLFMVSS(1)
OutputTable![DESC_DEFECT] = LLDescDefect(1)
OutputTable![CONEQUENCE_DEFECT] = LLDescDefect(1)
OutputTable![CORRRECTIVE_ACTION] = LLCorrectiveAction(1)
OutputTable![Database_Date] = LLDatabaseDate(1)

OutputTable.Update

If i = ILOOPS Then GoTo 40

70:

' *******************************************************
' * Check if this was the last record in tblRecallQuery *
' *******************************************************
'debug
' MsgBox (I)

If i = ILOOPS Then GoTo 40 'If this was the last record, write record.


' ***********************************************************
' * If not last record, go to next record of tblRecallQuery *
' ***********************************************************
InputTable.MoveNext

i = i + 1

' **************************
' * Define previous values *
' **************************

LLRecord_ID(1) = LLRecord_ID(2)
LLCampNo(1) = LLCampNo(2)
LLYearMakeModel(1) = LLYearMakeModel(2)
LLMfgCampNo(1) = LLMfgCampNo(2)
LLCompName(1) = LLCompName(2)
LLMfgTxt(1) = LLMfgTxt(2)
LLBGMan(1) = LLBGMan(2)
LLEndMan(1) = LLEndMan(2)
LLRclTypeCd(1) = LLRclTypeCd(2)
LLPotAff(1) = LLPotAff(2)
LLODate(1) = LLODate(2)
LLInfluencedBy(1) = LLInfluencedBy(2)
LLMfgName(1) = LLMfgName(2)
LLRCDate(1) = LLRCDate(2)
LLDateA(1) = LLDateA(2)
LLRPNO(1) = LLRPNO(2)
LLFMVSS(1) = LLFMVSS(2)
LLDescDefect(1) = LLDescDefect(2)
LLDescDefect(1) = LLDescDefect(2)
LLCorrectiveAction(1) = LLCorrectiveAction(2)
LLDatabaseDate(1) = LLDatabaseDate(2)


' ************************************************************
' * Read Current Record as separate variables for each field *
' ************************************************************
'
' Nulls will stop the macro, so we'll check for nulls in each field,
and if found, set variable to blanks.
'
'
If IsNull(InputTable![RECORD_ID]) Then LLRPNO(2) = " " Else
LLRecord_ID(2) = InputTable![RECORD_ID]
If IsNull(InputTable![CampNo]) Then LLCampNo(2) = " " Else LLCampNo(2) =
InputTable![CampNo]
If IsNull(InputTable![MAKETXT]) Then LLMakeTxt(2) = " " Else
LLMakeTxt(2) = InputTable![MAKETXT]
If IsNull(InputTable![MODELTXT]) Then LLModelTxt(2) = " " Else
LLModelTxt(2) = InputTable![MODELTXT]
If IsNull(InputTable![YEARTXT]) Then LLYearTxt(2) = " " Else
LLYearTxt(2) = InputTable![YEARTXT]
If IsNull(InputTable![MFGCAMPNO]) Then LLMfgCampNo(2) = " " Else
LLMfgCampNo(2) = InputTable![MFGCAMPNO]
If IsNull(InputTable![COMPNAME]) Then LLCompName(2) = " " Else
LLCompName(2) = InputTable![COMPNAME]
If IsNull(InputTable![MFGTXT]) Then LLMfgTxt(2) = " " Else LLMfgTxt(2) =
InputTable![MFGTXT]
If IsNull(InputTable![BGMAN]) Then LLBGMan(2) = " " Else LLBGMan(2) =
InputTable![BGMAN]
If IsNull(InputTable![ENDMAN]) Then LLEndMan(2) = " " Else LLEndMan(2) =
InputTable![ENDMAN]
If IsNull(InputTable![RCLTYPECD]) Then LLRclTypeCd(2) = " " Else
LLRclTypeCd(2) = InputTable![RCLTYPECD]
If IsNull(InputTable![POTAFF]) Then LLPotAff(2) = " " Else LLPotAff(2) =
InputTable![POTAFF]
If IsNull(InputTable![ODATE]) Then LLODate(2) = " " Else LLODate(2) =
InputTable![ODATE]
If IsNull(InputTable![INFLUENCED_BY]) Then LLInfluencedBy(2) = " " Else
LLInfluencedBy(2) = InputTable![INFLUENCED_BY]
If IsNull(InputTable![MFGNAME]) Then LLMfgName(2) = " " Else
LLMfgName(2) = InputTable![MFGNAME]
If IsNull(InputTable![RCDATE]) Then LLRCDate(2) = " " Else LLRCDate(2) =
InputTable![RCDATE]
If IsNull(InputTable![DATEA]) Then LLDateA(2) = " " Else LLDateA(2) =
InputTable![DATEA]
If IsNull(InputTable![RPNO]) Then LLRPNO(2) = " " Else LLRPNO(2) =
InputTable![RPNO]
If IsNull(InputTable![FMVSS]) Then LLFMVSS(2) = " " Else LLFMVSS(2) =
InputTable![FMVSS]
If IsNull(InputTable![DESC_DEFECT]) Then LLDescDefect(2) = " " Else
LLDescDefect(2) = InputTable![DESC_DEFECT]
If IsNull(InputTable![CONEQUENCE_DEFECT]) Then LLConsequenceDefect(2) =
" " Else LLConsequenceDefect(2) = InputTable![CONEQUENCE_DEFECT]
If IsNull(InputTable![CORRRECTIVE_ACTION]) Then LLCorrectiveAction(2) =
" " Else LLCorrectiveAction(2) = InputTable![CORRRECTIVE_ACTION]
If IsNull(InputTable![Database_Date]) Then LLDatabaseDate(2) = " " Else
LLDatabaseDate(2) = InputTable![Database_Date]

' *************************************************************
' * Check if this next record is for the same campaign number *
' *************************************************************

If LLCampNo(2) = LLCampNo(1) Then GoTo 50 Else GoTo 30

30:

' ****************************************************
' * This is a new campaign, so reset LLYearMakeModel *
' ****************************************************


LLYearMakeModel(2) = LLYearTxt(2) & " " & LLMakeTxt(2) & " " &
LLModelTxt(2)
20:
GoTo 10 ' Since this is a new campaign, we want to write the previous
record to the output table.


50:

'
*******************************************************************************
' * This is the same campaign, so add this year, make, model to
LLYearMakeModel *
'
*******************************************************************************

' Chr$(10) is ASCII for line feed
' Chr$(13) is ASCII for carriage return

LLYearMakeModel(2) = LLYearMakeModel(1) & Chr$(13) & Chr$(10) &
LLYearTxt(2) & " " & LLMakeTxt(2) & " " & LLModelTxt(2)

'debug
'MsgBox (LLYearMakeModel)

GoTo 70


' ***********
' * THE END *
' ***********

40:
'
'
*************************************************************************************************************
' * This is the last record, and it's a different campaign than the
previous record, so write to output table *
'
*************************************************************************************************************

' **********************
' * Open table tblTemp *
' **********************
' Note: might have to loop around this if there are more than one
records for only one campaign.
'
DoCmd.Echo False, "opening tblTemp..."
Set OutputTable = mydb.OpenRecordset("tblTemp", DB_OPEN_DYNASET)

' ******************************************************
' First let's delete all records in the output table. *
' ******************************************************

If K = 1 Then GoTo 90

OutputTable.MoveFirst
Do Until OutputTable.EOF
OutputTable.Delete
OutputTable.MoveNext
Loop
90:
OutputTable.AddNew

OutputTable![RECORD_ID] = LLRecord_ID(2)
OutputTable![CampNo] = LLCampNo(2)
OutputTable![YEARMAKEMODELTXT] = LLYearMakeModel(2)
OutputTable![MFGCAMPNO] = LLMfgCampNo(2)
OutputTable![COMPNAME] = LLCompName(2)
OutputTable![MFGTXT] = LLMfgTxt(2)
OutputTable![BGMAN] = LLBGMan(2)
OutputTable![ENDMAN] = LLEndMan(2)
OutputTable![RCLTYPECD] = LLRclTypeCd(2)
OutputTable![POTAFF] = LLPotAff(2)
OutputTable![ODATE] = LLODate(2)
OutputTable![INFLUENCED_BY] = LLInfluencedBy(2)
OutputTable![MFGNAME] = LLMfgName(2)
OutputTable![RCDATE] = LLRCDate(2)
OutputTable![DATEA] = LLDateA(2)
OutputTable![RPNO] = LLRPNO(2)
OutputTable![FMVSS] = LLFMVSS(2)
OutputTable![DESC_DEFECT] = LLDescDefect(2)
OutputTable![CONEQUENCE_DEFECT] = LLDescDefect(2)
OutputTable![CORRRECTIVE_ACTION] = LLCorrectiveAction(2)
OutputTable![Database_Date] = LLDatabaseDate(2)

OutputTable.Update

' ***********************
' * Close the databases *
' ***********************


OutputTable.Close
InputTable.Close
Set mydb = Nothing
Set dbcurrent = Nothing

End Function

Dear Fred:

The query to concatenate the CAMPNO values will need to be LOOKING at the
table sorted that way. I suggest it does not require the table to actually
be in that order.

Sorting a table is completely different from forcing it to be stored in that
order. Sorting a table creates an appearance of order, not the order itself
inside the table. You can have multiple different sorts of the same data.

If you post the query you are using to do this, I beleive we can get it to
function without the requirement you seem to feel is necessary.

I suspect you're looking at your database as a spreadsheet. It is not. It
is far more powerful, and complex. But, for you, this is a good thing. I
expect you will not need to continue thinking in quite the same way you have
been doing.

Tom Ellison

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...

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


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?



:

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
--

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;

:

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
--

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...

 

[Guest]

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...

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

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?

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...



:

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


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?



:

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
--

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;

:

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
--

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...

 

[Duane Hookom]

Why not use:
Set InputTable = mydb.OpenRecordset("SELECT * FROM tblRecallQuery ORDER BY
CAMPNO")

 

[Tom Ellison]

Dear Fred:

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

Tom Ellison

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...

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

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?



:

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...



:

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


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?



:

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
--

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;

:

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
--

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...

 

[Guest]

Duane,

"Why not use..." you ask?

Because I didn't know how to!!!

But I tried that line in my module, and it works!!!

THANK YOU ALL FOR YOUR HELP ON THIS ONE!!!

 

[Guest]

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)

Dear Fred:

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

Tom Ellison

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...

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


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?



:

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...



:

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


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?



:

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
--

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;

:

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
--

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...