Re: Select Query Question

On Mon, 17 Dec 2007 13:38:31 -0800, jndickin
<jndickin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Access 2003

I am trying to do a Select Query. One of the Tables in the query has
multiple listings for one Route# but only one of those is correct. I'd like
my query to only pull the first record for each Route#. How can I modify my
query to get that result?

Tables don't HAVE "first records". A table should be seen as an unordered
"heap" of data. If there are two records in your Table2 with Route# 3301, you
will need some OTHER field within that table to identify which you consider
"correct".

If it's arbitrary, and you just want to pull whatever record Access happens to
choose (uncontrolled and unspecific!!!) you can create a Query

SELECT DISTINCT [Route#] FROM Table2;

or if you're pulling other fields

SELECT [Route#], First([This]) As FirstOfThis, First([That]) AS FirstOfThat,
<etc>
FROM Table2
GROUP BY [Route#];

to pull one record for each value of Route#.

Note that # is a date delimiter and it's best not to use it in fieldnames.


John W. Vinson [MVP]
.