Why Query works from table but not Query

  • Thread starter Thread starter vtj
  • Start date Start date
V

vtj

I’ve got a situation that does not seem logical but I’m hoping someone can
explain this. I have a table with records that include an account number and
a month number. The same account number can occur with several month
numbers. I am trying to get a query to bring back just the lowest month
number an account number appears. The lines are in the original table in a
random order. When I did a query with grouping that asked for the first of
the month number, I always got the second month. So I did a query that
sorted the table in ascending order by account number and month number. The
result of that query is what I expected to see. Everything is in ascending
order by account number and month number. I then used that query as input to
the query with grouping looking for the first (lowest) month number. I again
got the second month number in the result. Then I used the sort query to
make a table in sorted order. If I then use the sorted table I do get the
first (lowest) month number. Why does it work if I use a sorted table as
input but not when I use a query that sorts into the same order? It seems to
me that the records would be presented to the query in the same order whether
it was from a sorted table or query that sorted the same way.
 
FIRST is not 'First Month' but the First data it finds. If sorted ascending
in a query then it would be first.
You need to use Min for Minimum.
 
The question is why is data in a different order if it comes directly from
the query than it is if the query writes a table and then the table is
queried. I understand that it is the first data it finds, but don't
understand why when the input is already sorted into ascending order. And it
always gets the second item even if there are more than two.
 
Access stores data in a table like a barrel of bricks.
My guess is that the data in the other fields to the left are causing it.
 
Back
Top