Create a query that searches many columns and returns many values

Deirdre

Board Regular
Joined
Feb 28, 2002
Messages
129
I have a table in Access set up like this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attribute 8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">joker</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smiles a lot</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">calm</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">unique</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">friendly</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smart</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">funny</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">special</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">great friend</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">leader</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">talkative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">loves to laugh</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">supportive</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">funny</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smart</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">nice</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">quick thinker</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">loves recess</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">daydreams</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">joker</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">happy</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">chill</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">appreciative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">friendly</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smart</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">loves to learn</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smiles a lot</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">talkative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">great attittude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">happy</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">positive attitude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">welcoming</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">positive attitude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smart</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">kind</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">unique</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">great friend</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">happy</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">energetic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">enthusiatic</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">John Doe 6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">talkative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">loves school</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">positive attitude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">appreciative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">pretty</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">happy</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">great teammate</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">great friend</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

I have another table in Access set up like this:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Attributes</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Corrected attribute</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> smart</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">smart</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">admire</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">admire</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">always happy</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">happy</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">amazing</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">amazing</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">amazing friend</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">amazing friend</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">appreciative</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">appreciative</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">artistic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">artistic</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">atheltic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">athletic</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">athlectic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">athletic</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">athletic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">athletic</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

I need to write a query that will take the Corrected Attribute data in the 2nd table - search the 1st table, then return the names that meet it.

For example:
"Smart" should return, John Doe 1, John Doe 2, John Doe 4, John Doe 5

If there is a better way I should set up the data or maybe even use Excel, I am open to all suggestions.

Thank you!
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
Re: Create a query that searches many columns and returns one many values

Yes, there is a design issue with your original table. You should not have repeating similar fields going across. It violates the rules of data normalization and makes tasks like the one you are trying to do much harder than they need to be.

Your table should just have three fields:
Name
Attribute Number
Attribute Value


So each name would have 8 records instead of 1 record with 8 fields.
Then, you only need to query against one field.
 
Last edited:

Deirdre

Board Regular
Joined
Feb 28, 2002
Messages
129
Re: Create a query that searches many columns and returns one many values

I am afraid I do not follow. I'm sure it's me as my brain is mush today.
Kids were asked to write down a word that describes their classmates. Some kids wound up with 18 descriptors(attributes), some kids 8. The attribute number is just a header - all of those columns are really the same thing.

The attributes for each child do not repeat across. Which is why some have few than others. If 3 kids thought John Doe 1 was smart, it's only listed once.

My apologies if I made this more complicated. Long day.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
Re: Create a query that searches many columns and returns one many values

The attribute number is just a header -
all of those columns are really the same thing.
Yes, that is a big indicator that they should all be one field, not multiple fields.
Don't get confused by the Attribute Number, it is really just a counter for that person (and really does not have much meaning outside of that). It may be unnecessary altogether.

Your data for the first few people would look like this:
Code:
[TABLE="width: 275"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Attribute Number[/TD]
[TD]Attribute Value[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]1[/TD]
[TD]joker[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]2[/TD]
[TD]smiles a lot[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]3[/TD]
[TD]calm[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]4[/TD]
[TD]unique[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]5[/TD]
[TD]firendly[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]6[/TD]
[TD]smart[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]7[/TD]
[TD]funny[/TD]
[/TR]
[TR]
[TD]John Doe1[/TD]
[TD]8[/TD]
[TD]special[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]1[/TD]
[TD]great friend[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]2[/TD]
[TD]leader[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]3[/TD]
[TD]talkative[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]4[/TD]
[TD]loves to laugh[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]5[/TD]
[TD]supportive[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]6[/TD]
[TD]funny[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]7[/TD]
[TD]smart[/TD]
[/TR]
[TR]
[TD]John Doe2[/TD]
[TD]8[/TD]
[TD]nice[/TD]
[/TR]
</tbody>[/TABLE]
Note now when you are searching for particular values, you only need to search one field (not 8).
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,706
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Re: Create a query that searches many columns and returns one many values

A database table for this table could also be structured like this:
----------------------------------
| Person     | PersonAttribute   |
----------------------------------
| John Doe 1 | joker             |
| John Doe 2 | great friend      |
| John Doe 3 | quick thinker     |
| John Doe 4 | smart             |
| John Doe 5 | positive attitude |
| John Doe 6 | talkative         |
| John Doe 1 | smiles a lot      |
| John Doe 2 | leader            |
| John Doe 3 | loves recess      |
| John Doe 4 | loves to learn    |
| John Doe 5 | smart             |
| John Doe 6 | loves school      |
| John Doe 1 | calm              |
| John Doe 2 | talkative         |
| John Doe 3 | daydreams         |
| John Doe 4 | smiles a lot      |
| John Doe 5 | kind              |
| John Doe 6 | positive attitude |
| John Doe 1 | unique            |
| John Doe 2 | loves to laugh    |
| John Doe 3 | joker             |
| John Doe 4 | talkative         |
| John Doe 5 | unique            |
| John Doe 6 | appreciative      |
| John Doe 1 | friendly          |
| John Doe 2 | supportive        |
| John Doe 3 | happy             |
| John Doe 4 | great attittude   |
| John Doe 5 | great friend      |
| John Doe 6 | pretty            |
| John Doe 1 | smart             |
| John Doe 2 | funny             |
| John Doe 3 | chill             |
| John Doe 4 | happy             |
| John Doe 5 | happy             |
| John Doe 6 | happy             |
| John Doe 1 | funny             |
| John Doe 2 | smart             |
| John Doe 3 | appreciative      |
| John Doe 4 | positive attitude |
| John Doe 5 | energetic         |
| John Doe 6 | great teammate    |
| John Doe 1 | special           |
| John Doe 2 | nice              |
| John Doe 3 | friendly          |
| John Doe 4 | welcoming         |
| John Doe 5 | enthusiatic       |
| John Doe 6 | great friend      |
----------------------------------



The fuzzy matching is another kind of problem - so using ID's as in Joe's query would be what you are aiming for (each user supplied and possibly misspelled or variant description should match to a single ID or a single "Correct Spelling")
 
Last edited:

Deirdre

Board Regular
Joined
Feb 28, 2002
Messages
129
Re: Create a query that searches many columns and returns one many values

A-HA! Now I follow!

Thank you very much!
 

Deirdre

Board Regular
Joined
Feb 28, 2002
Messages
129
Re: Create a query that searches many columns and returns one many values

Thank you!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,487
Members
410,685
Latest member
chandraganji
Top