Pulling identical numbers if "text" found

  • Thread starter Thread starter tekari
  • Start date Start date
T

tekari

Hi,

I'm not too well versed in SQL so I'm not sure if thats the method to
accomplish what I'm looking for, but I can't seem to do it with just
the design view of a normal query.

I have a query setup to pull information from a single table.

Example of table setup:

NUMBER CODE DATE
24311 VG 1/1/07
24311 ABC 1/1/07
24311 DEF 1/1/07
1055 BG 1/1/07
1055 DEF 1/1/07
1055 EF 1/1/07
6677 VG 1/1/07
6677 PC 1/1/07


What I'm looking for is a way to pull all of a group of numbers where
there is a CODE equal to VG.

So in the above case it would pull every single 24311 line because VG
is in one of the code lines. It would also pull all of the 6677's
because VG is there. However it would not pull anything from the 1055
line.

Is there anyway to accomplish this? Am I making this more difficult
then it needs to be?

Thanks as always for any help!
 
Just put

"VG"

in the criteria line of the "code" field. If the number is the same
regardless of what the code letter then it shouldn't matter too much or don't
I understand?
 
You don't indicate how your are going to do this, or whether a simple query
is all you need. My guess is that it is not a "simple" query, but more
likely a query run based on input from a field in some form. The "simple"
part would look like:

SELECT * FROM yourTable
WHERE [Number] IN (SELECT DISTINCT [NUMBER] FROM yourTable WHERE
Code: 
 =
"VG")

Another way to do this would be:

SELECT T1.*
FROM yourTable T1
INNER JOIN (SELECT DISTINCT [NUMBER] FROM yourTable WHERE [Code] = "VG") T2
ON T1.Number = T2.Number

You might want to try each of these, and see which is quicker with your
dataset and indexing scheme.

The down side of #2 is that if you switch between the SQL and Query Grid and
make changes, you may get an error message (something about brackets or the
From clause).  The reason for this is that Access (Jet I assume) interprets
this query and replaces the "(SELECT....)" with "[SELECT ....]."   If you get
this error message, go back to the SQL view and change the brackets to
parenthesis and delete the period after the closing bracket.

HTH
Dale
 
One method would use a subquery in the WHERE clause to identify the numbers
that have "VG" as a code.

SELECT *
FROM YourTable
WHERE [Number] IN
(SELECT [Number] FROM YourTable Where Code = "VG")


In the query grid, you would need
Field: [Number]
Criteria: In (SELECT [Number] FROM [YourTable] Where
Code: 
 = "VG")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks guys,

This worked perfectly. I'm trying my best to learn the SQL part of
Access because i think it will come in handy in the future.
 
Back
Top