Re: Select Query Question Continued

Thank you so very much! I'm so close I can "taste it!" I wrote a query to
pull out one unique route number along with the FirstOf fields from my
original Route Info table using the suggested method below -- let's call it
the Route Info query. Now, of course, I have a new issue. It can't possibly
be that hard to solve . . . but I obviously can't figure it out on my own . .
.. :-(

Previously I was performing a Select Query on a table consisting of a list
of equipment and the routes to which each piece was assigned -- let's call it
the Equipment table. This query took the route number from the Equipment
table, counted all the pieces of equipment and then combined those results
with the route information on the original Route Info table with which I had
the multiple route number issues. Let's call this the Combo query.

I have since adjusted the Combo query to pull in the route Information from
the Route Info query I created with John's help rather than the original
Route Info table. Now I no longer get the multiple route lines, however, not
ALL of the routes in the Equipment table are actually in the Route Info table
so . . .

When the Combo query had been getting route info from the Route Info table,
it would just list those route numbers and their equipment counts with no
corresponding route information. Now, with the Combo query getting route
info from the Route Info QUERY, it just leaves out any route that does not
have route info on the original Route Info table.

Whew! I don't know if I explained this in any recognizable fashion but I
was so thrilled with the advice on my earlier problem, I just had to give
this a shot!



"John W. Vinson" wrote:

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]

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?

.