Record Order in Query vs. VBA

[Guest]

I posted a question last week about a query that was supposed to draw text
data from a date-sorted table and concatenate the text up to a max of 50
characters, then write it to an output field in a different table. Problem
was that although the source table was sorted, the concatenated data was not,
so instead of getting the FIRST few records in my output I was getting them
in more or less random order.

I solved my immediate problem with some easier-than-I-thought-it-would-be
VBA; opening the table and using the good ol' MoveFirst/MoveNext brought up
the records in date order, exactly the way they show in the table.

But I'm still curious about WHY the query approach was grabbing data from
all over the map instead of in the order the records appear in the source
table. Can anybody 'splain me that using short words I might be able to
understand? I've been doing quite a lot of things on the assumption that a
query works its way through the source table in the sequence you see on the
screen when you open the table, and if that ain't necessarily so then I'm
going to have to revise my thinking.

 

[Steve Schapel]

Larry,

The 'splanation comes down to this... The data in a table is not
ordered. A table is just a big bucket. If you want data that is
sorted, you have to use a query with a sort defined. It is not clear
from your post exactly how you were doing this. I think you had a query
anyway... did you have a sort order defined in the query. If you did,
and it didn't produce the results you expected, then this is a different
question. If so, lets' have a look at the SQL view of the query.

 

[[MVP] S.Clark]

Without seeing your SQL string, I can't give a definate anything, but...

Access, being the very user-friendly app that it is, allows you to store an
Order By clause with just about any table, query, form, and/or report.
Therefore, you're assumption that what you see on the screen for one object
is what you will get on another object is not a valid one.

If you run a query and don't specify a sort order, then it typically resorts
to sorting by the PK or other unique index.

 

[Guest]

Sequence of events was:

(1) A make-table query first used a large source table (Table1) and pulled
Part Number, Date, and Comment, sorted by Part Number and Date, into a new
Table2. NOTE: Table2 might have numerous records for each part number.
(2) A second (update) query then tapped into Table2 and attempted to create
a concatenated comments field (max 50 characters) for each part number,
posting that result to the appropriate field in destination Table3 where Part
Number is unique.

Unfortunately, when done this way the concatenation was seemingly random:
for Part Number 121212 you might see record 87, followed by record 3,
followed by record 31, followed by record 19, etc. until the 50-character
limit was reached. (Yes, I know that technically there are no "record
numbers," I just mean the order in which the information was displayed in
Table2.) It was CONSISTENTLY random, in that every time it ran the results
were the same, but they were NOT in the desired oldest-first date order.

Opening Table2 with VBA allowed me to pull the records from Table2 in the
sorted order, so that's the route I ultimately went. But for my continuing
education I'd like to better understand what the query was doing.

The SQL isn't immediately available, as I abandoned the query and deleted
it, but if seeing that will help you or anyone clarify what's going on for
me, I'll try to reconstruct and post it.

 

[Steve Schapel]

Larry,

Well, just to re-iterate, you would need to have a sort order explicitly
defined in the query. The ordering in the initial Make-Table query
would have no bearing. Probably looping through the Table2 recordset in
code was overkill, all you needed was an Order By in the query.

 

[Guest]

Thanks for both who responded. In one of my early efforts to get it to work
("it" being the second, update query) my initial thought was just to add a
"sort ascending by date" to it, but lo and behold, the Query Builder for an
update query doesn't have a place to specify a sort order! I admit I didn't
go the extra step and try to just type it into the SQL. Even without such a
sort clause, though, it seemed to me that a process using the table, which
was built by a query that DID have a sort clause, should have yielded results
in that order.

 

[Steve Schapel]

Larry,

... it seemed to me that a process using the table, which
was built by a query that DID have a sort clause, should have yielded results
in that order.

Nope, doesn't work like that. Think of tossing playing cards into a
bucket, in order, ace of hearts first, all the way down to 2 of spades.
Then tip them out of the bucket, and have a look at the order.