Why does this happen?

[Guest]

I have a contacts query that has eleven columns -- no sort is on every one.
Birthdate is the first; cleaneddate is the last. On occasion, the query
shows as follows:
Today's Date, which is column 5, then the next four columns; this makes
Birthdate now in column 6.
If I sort any of the columns by ascending or descending, the columns end up
back in order. I usually go back to a no sort, and it comes out correct.
Has anyone seen this problem before?

 

[Joseph Meehan]

I have a contacts query that has eleven columns -- no sort is on
every one. Birthdate is the first; cleaneddate is the last. On
occasion, the query shows as follows:
Today's Date, which is column 5, then the next four columns; this
makes Birthdate now in column 6.
If I sort any of the columns by ascending or descending, the columns
end up back in order. I usually go back to a no sort, and it comes
out correct. Has anyone seen this problem before?

I am not sure I understand what you are asking, but maybe this will
answer it.

The data in Access and most databases is stored in a bucket, without any
specific order. In Access queries allow you to select any order you want.
If you don't specify an order you get disorder.

 

[Guest]

I thought that the order columns appear was the same order you have them in
the query. Is this not the case? Again, it dosen't happen every time I run
the query.

 

[John Vinson]

I have a contacts query that has eleven columns -- no sort is on every one.
Birthdate is the first; cleaneddate is the last. On occasion, the query
shows as follows:
Today's Date, which is column 5, then the next four columns; this makes
Birthdate now in column 6.

If the *column* order is getting scrambled, you may have a damaged
link to the backend database (assuming it's a split database) or you
may have a corrupt database. Try relinking if it's linked, and in any
case Compact and Repair.

It may be prudent to open this Query in SQL view, copy the SQL to
Notepad, delete the query, compact the database, and paste the SQL
back into a new Query's SQL window.

John W. Vinson[MVP]

 

[Joseph Meehan]

I thought that the order columns appear was the same order you have
them in the query. Is this not the case? Again, it dosen't happen
every time I run the query.

Let's figure out exactly what is happening.

First let's talk fields not columns. Columns is a spreadsheet
thing.

Are you saying the order of the fields is changing or the data displayed
is changing?

Name address City
Amy 1 main NY
Bob 2 main Sligo
Chuck 3 main Smith

changes to Ex #1

Name address City
Bob 2 main Sligo
Chuck 3 main Smith
Amy 1 main NY

or changes to Ex #2

Name City address
Amy NY 1 main
Bob Sligo 2 main
Chuck Smith 3 main

or what?

In the query you should be able to change the order (as in the second
example 2) by just clicking and dragging the fields.

If it is like example 1 you just need to mark any of the three fields as
sort - ascending. Which ever one you chose will sort the others based on how
it is sorted, the records (rows) will stay together. If you want to sort on
two fields you can do that as well the first sort will be the field to the
left.

 

[James Hahn]

But you have indicated that each column has no sort order on it. At least
that's the way I read your message. If you don't apply a sort order in the
query then you get the order that the system decides, which could be
anything at all, and could change between one query and the next.
--