Create a query that searches many columns and returns many values

[Deirdre]

I have a table in Access set up like this:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Name	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7	Attribute 8
2	John Doe 1	joker	smiles a lot	calm	unique	friendly	smart	funny	special
3	John Doe 2	great friend	leader	talkative	loves to laugh	supportive	funny	smart	nice
4	John Doe 3	quick thinker	loves recess	daydreams	joker	happy	chill	appreciative	friendly
5	John Doe 4	smart	loves to learn	smiles a lot	talkative	great attittude	happy	positive attitude	welcoming
6	John Doe 5	positive attitude	smart	kind	unique	great friend	happy	energetic	enthusiatic
7	John Doe 6	talkative	loves school	positive attitude	appreciative	pretty	happy	great teammate	great friend
Sheet1

I have another table in Access set up like this:

Excel 2007
	A	B
1	Attributes	Corrected attribute
2	smart	smart
3	admire	admire
4	always happy	happy
5	amazing	amazing
6	amazing friend	amazing friend
7	appreciative	appreciative
8	artistic	artistic
9	atheltic	athletic
10	athlectic	athletic
11	athletic	athletic
Sheet2

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!

 

[Joe4]

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.

 

[Deirdre]

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]

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:

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

 

[xenou]

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

 

[Deirdre]

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

A-HA! Now I follow!

Thank you very much!

 

[Deirdre]

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

Thank you!!!