Finding a value in several different columns

  • Thread starter Thread starter Nick Xylas
  • Start date Start date
N

Nick Xylas

Sorry if this is a really easy question, but I'm still learning when
it comes to Access 2003's capabilities. I have a table from which I
want to create either a report or a query to show the record number
(primary key) of all instances where the value "1" occurs in field A
OR field B OR field C, followed by all instances of "2" then "3" and
so on. I'd imagine it's an easy thing to do for an experienced user,
but I'm a novice, so any help would be appreciated.
 
Nick -

You didn't say what to do with duplicates (that is, if a given record number
has a 1 in Column A in one record and also a 1 in Column A in another record
- would you want that record number to be listed once or twice?

Start with this (but use your table and fieldnames):

SELECT RecordNumber, 1 As Value_Check
From YourTableName
Where (nz([FieldA],0)=1 OR nz([FieldB],0)=1 OR nz([FieldC],0)=1)

If you only want one record returned if there are duplicates, change the
SELECT to SELECT DISTINCT. This should give you all the records with a 1 in
either FieldA, FieldB, or FieldC. Once you get this working, then you will
want to copy/paste the SQL into a new query in SQL View (don't select any
tables in query design view). Paste it in three times, putting a UNION ALL
between each pair of queries, and removing the semicolon from the first two
queries. Change the "1" to "2" and "3" in the other queries. It will look
something like this (but with your table and field names):

SELECT RecordNumber, 1 As Value_Check
From YourTableName
Where (nz([FieldA],0)=1 OR nz([FieldB],0)=1 OR nz([FieldC],0)=1)
UNION ALL
SELECT RecordNumber, 2 As Value_Check
From YourTableName
Where (nz([FieldA],0)=2 OR nz([FieldB],0)=2 OR nz([FieldC],0)=2)
UNION ALL
SELECT RecordNumber, 3 As Value_Check
From YourTableName
Where (nz([FieldA],0)=3 OR nz([FieldB],0)=3 OR nz([FieldC],0)=3)

That should give you what you need. If you have issues, paste your SQL
into your next posting.
 
Nick -

You didn't say what to do with duplicates (that is, if a given record number
has a 1 in Column A in one record and also a 1 in Column A in another record
- would you want that record number to be listed once or twice?  
Click to expand...
I'm not sure I quite understand the question. If one record has a 1 in
column A and another record also has a 1 in column A, I would like the
primary key autonumber for both records to be listed. Is that what you
are asking?

And I should have said that the numbers go all the way up to 147. Is
there a way to automate the process so that I don't have to type all
the numbers from 1 to 147 in manually? The numbers represent people
who can fall into one of three categories, and I am trying to bring up
every record in which each person appears.
 
Where (nz([FieldA],0)=3 OR nz([FieldB],0)=3 OR nz([FieldC],0)=3)

That should give you what you need.  If you have issues, paste your SQL
into your next posting.
Click to expand...

I got a syntax error. This was my SQL

SELECT RecordNumber, 1 As Value_Check
From Subplots
Where (nz([Character 1],0)=1 OR nz([Character 2],0)=1 OR nz([Character
3,0)=1)
UNION ALL
SELECT RecordNumber, 2 As Value_Check
From Subplots
Where (nz([Character 1],0)=2 OR nz([Character 2],0)=2 OR nz([Character
3,0)=2)
UNION ALL
SELECT RecordNumber, 3 As Value_Check
From Subplots
Where (nz([Character 1],0)3 OR nz([Character 2],0)=3 OR nz([Character
3,0)=3)
 
Nick -

If you table only has one record for each person, then you don't need to
worry about duplicates. Since I don't know the data, I was asking in case
you could have one person in the table multiple times.

The reason this is so difficult is your table structure is probably not
correct. Since I don't know what A, B, or C are, but they are used in the
same way for this query or report, then my thought would be instead of a
table that looks like this (I am using Person for the primary key just
because I don't know your data):

Person A B C
Mary 1 1 2
John 12 1 4
Jack 2 5 8


The table would be like this:
Person Type Value
Mary A 1
Mary B 1
Mary C 2
John A 12
John B 1
John C 4
Jack A 2
Jack B 5
Jack C 8


Then your queries would be very simple:

Select Distinct Value, Person from <your table name>
Order by Value, Person;
 
Nick -

If you table only has one record for each person, then you don't need to
worry about duplicates.  Since I don't know the data, I was asking in case
you could have one person in the table multiple times.

The reason this is so difficult is your table structure is probably not
correct.  Since I don't know what A, B, or C are, but they are used in the
same way for this query or report, then my thought would be instead of a
table that looks like this (I am using Person for the primary key just
because I don't know your data):
Click to expand...

OK, let me give you a little background. This database is for an
ongoing comic I have been plotting for some time and which features a
huge cast of characters and many intertwining subplots. In order to
help me keep track, I created a table named Subplots, which links to
another table named Characters. Each record in Subplots features three
fields, named Character 1, Character 2 and Character 3, which use the
primary key from the Characters table as their data. But the character
with the number 47 (to pick a random example) as his or her primary
key in the Characters table (which uses an Autonumber as the primary
key) could be Character 1 in one record and Character 3 in another. I
want to be able to search for the value "47" in the Character 1 OR
Character 2 OR Character 3 field in the Subplots table, but if
possible, I'd like to be able to do the same for all the records in
the Characters table, starting with the first and going onto the last.
Though I'm beginning to wonder whether it might not be simpler to do
it manually,,,.
 
Hi Nick,

     I am rather curious; is there some reason that you have not tried my
solution?
Click to expand...
Mostly because I wasn't sure how to adapt it from the generic to the
specific (ie what to replace with my actual field and table names).
 
Hi Nick,

     Pretty simple.  Create the table as mentioned and the secondnumbers
query; which you will name ""qryNumbers 0-999".  Then your query will be:

SELECT [qryNumbers 0-999].The_Number, Subplots.RecordNumber, Subplots.
[Character 1], Subplots.[Character 2], Subplots.[Character 3]
FROM Subplots, [qryNumbers 0-999]
WHERE (((Subplots.[Character 1])=[The_Number])) OR (((Subplots.[Character 2])
=[The_Number])) OR (((Subplots.[Character 3])=[The_Number]))
ORDER BY [qryNumbers 0-999].The_Number, Subplots.RecordNumber;

     For the future, it may be easier to tell us your actual table and field
names instead of trying to be generic.
Click to expand...
I realise that now. I thought the solution would be a lot simpler than
it actually was.
     If you are still confused, post back on where you need further
clarification.

             Clifford Bass
Click to expand...
I ran the query and it asked me to input parameters. What am I
supposed to enter into these boxes?
 
Back
Top