Select Query Question

[jndickin]

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?

Following is a VERY simplified "picture" of what I am trying to accomplish.
I don't know if this will still be legible when I post so . . . fingers
crossed!

TABLE1
Route#
3301
3302
3303

TABLE2
RSR Name Route#
Correct 3301
Incorrect 3301
Correct 3302
Correct 3303

QUERY
Field: Route# Route# RSR Name
Table: TABLE1 TABLE2 TABLE2
Total: Group By Group By Group By

RESULT I DON'T WANT
Route# RSR Name
3301 Correct
3301 Incorrect
3302 Correct
3303 Correct

RESULT I DO WANT
Route# RSR Name
3301 Correct
3302 Correct
3303 Correct

Does this make any sense to anyone? Thanks!

 

[KARL DEWEY]

Try this ---
SELECT Table2.[Route#], Table2.[RSR Name]
FROM Table2
WHERE (((Table2.[RSR Name])="Correct"))
ORDER BY Table2.[Route#];

 

[John W. Vinson]

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]

 

[jndickin]

I think this is what I'm looking for. I'll try this out and report back
later today. Thanks!

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]

 

[jndickin]

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!

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]

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?