Finding a value in several different columns

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.

Hi Nick,

Interesting question. Here is one way to do it. Create a table named
"tblNumbers" with one number (integer or long integer) column. Fill it with
ten records with the values from 0 to 9. Next create a query that yields
enough values to cover all the values you want to report. So if you are
reporting for values 0 through 99 your query, named "qryNumbers 0-99" would
look like this:

SELECT [Tens].[The_Number]*10+[Ones].[The_Number] AS The_Number
FROM tblNumbers AS Tens, tblNumbers AS Ones;

Or if you you need 0-999 you would have:

SELECT [Hundreds].[The_Number]*100+[Tens].[The_Number]*10+[Ones].[The_Number]
AS The_Number
FROM tblNumbers AS Hundreds, tblNumbers AS Tens, tblNumbers AS Ones;

The above method of creating sequential lists of numbers was provided by
another poster--sorry I do not remember his name. Then you can use that
query with your table to check each field for each number value like so:

SELECT [qryNumbers 0-99].The_Number, tblMy_Table.PK_Column, tblMy_Table.A,
tblMy_Table.B, tblMy_Table.C
FROM tblMy_Table, [qryNumbers 0-99]
WHERE (((tblMy_Table.A)=[The_Number])) OR (((tblMy_Table.B)=[The_Number])) OR
(((tblMy_Table.C)=[The_Number]))
ORDER BY [qryNumbers 0-99].The_Number, tblMy_Table.PK_Column;

This of course will show only the numbers that are found in the columns.
If you need to report missing numbers with blank primary key values, use the
numbers query on the left side of a left outer join on the above query,
joining on The_Number.

Hope this helps,

Clifford Bass

Nick Xylas wrote:
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.

--
Message posted via http://www.accessmonster.com

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.

--
Daryl S


"Nick Xylas" wrote:

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.
.

On 22 Apr, 18:27, Daryl S wrote:
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? *

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.

Nick,

Did you try my solution?

Clifford Bass

Nick Xylas wrote:

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

On 22 Apr, 18:27, Daryl S wrote:

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.


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;

--
Daryl S


"Nick Xylas" wrote:

On 22 Apr, 18:27, Daryl S wrote:
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?

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.
.

On 27 Apr, 17:38, Daryl S wrote:
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):


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?

On another note; what happens when you have four or five characters in a
particular subplot? I concurr with Daryl--you need to fix your table
structure. Then the whole problem will go away.

Clifford Bass

Nick Xylas wrote:

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,,,.

--
Message posted via http://www.accessmonster.com

On 27 Apr, 21:58, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi Nick,

* * *I am rather curious; is there some reason that you have not tried my
solution?

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).