Duplicate Entries in Multiple Columns

[justsometallguy]

So I'm really new to Access and I would greatly appreciate any help! This site has been a big help thus far, but I've come across one problem that I just can't fix due to my unfamiliarity with SQL and all that jazz.

Basically I have a Table called "Database" and each row contains a specific file number called "Document No". I have (4) columns: "Keyword1", "Keyword2", "Keyword3", and "Keyword4" that I want to be able to sort the data by. Across all four keyword columns, there may be duplicate entries, but there will be no duplicates within the same row, and not all keyword columns will have an entry (some may be blank).

For instance, I have:
[Document No] [Keyword1] [Keyword2] [Keyword3] [Keyword4]
1 yellow blue red green
2 blue black yellow
3 yellow red purple
4 green orange

Now if I want to search for all rows that contain "yellow", I would want it to return:
1 yellow blue red green
2 blue black yellow
3 yellow red purple

Is this possible? How would I accomplish this? Again, any help would be much appreciated!! Thanks in advance!

 

[MSAccessRookie]

So I'm really new to Access and I would greatly appreciate any help! This site has been a big help thus far, but I've come across one problem that I just can't fix due to my unfamiliarity with SQL and all that jazz.

Basically I have a Table called "Database" and each row contains a specific file number called "Document No". I have (4) columns: "Keyword1", "Keyword2", "Keyword3", and "Keyword4" that I want to be able to sort the data by. Across all four keyword columns, there may be duplicate entries, but there will be no duplicates within the same row, and not all keyword columns will have an entry (some may be blank).

For instance, I have:
[Document No] [Keyword1] [Keyword2] [Keyword3] [Keyword4]
1 yellow blue red green
2 blue black yellow
3 yellow red purple
4 green orange

Now if I want to search for all rows that contain "yellow", I would want it to return:
1 yellow blue red green
2 blue black yellow
3 yellow red purple

Is this possible? How would I accomplish this? Again, any help would be much appreciated!! Thanks in advance!

The first thing I would observe is that the design of the Table is not normalized. When you have several fields in the same Table that serve the same or similar purposes, and contain Text Data, it is most often better to have another Table of items containing the Text Items that are in the Table, and a Junction Table that lists Primary IDs vs Foreign IDs (from the new Table of Colors).

In this case, it means you would have a new Table containing the Color ID (Numeric) and a Color Name (Text) for each Color that will be used in place of the current Text. You would also have another Table that contains Document IDs and Color IDs that are allowed.

If you were to select all Document IDs that have an entry in the new table of Documents vs Colors, it should give you want you want.

 

[justsometallguy]

The first thing I would observe is that the design of the Table is not normalized. When you have several fields in the same Table that serve the same or similar purposes, and contain Text Data, it is most often better to have another Table of items containing the Text Items that are in the Table, and a Junction Table that lists Primary IDs vs Foreign IDs (from the new Table of Colors).

In this case, it means you would have a new Table containing the Color ID (Numeric) and a Color Name (Text) for each Color that will be used in place of the current Text. You would also have another Table that contains Document IDs and Color IDs that are allowed.

If you were to select all Document IDs that have an entry in the new table of Documents vs Colors, it should give you want you want.

I guess I don't fully understand how this solves the problem. Is there by chance any example you can give me to help clarify? I think this would make since if I had more experience using Access, but as this is not the case, I'm still confused.

To add more to my first post, the list of "Keywords" that can be input are, in fact, their own table. Then, on the "Database" form, I have combo boxes for Keywords 1, 2, 3, and 4 that pull from the "Keyword" table. This makes sure that there is no incorrect spelling of any keyword and therefore, when searching for a specific keyword, all possible results are returned. The problem is that I just don't know how to search for the keyword across multiple columns. Thanks for the reply!

 

[justsometallguy]

Can anyone else clarify this or help me out? Thanks!

 

[SOS]

Can anyone else clarify this or help me out? Thanks!

based on your previous post I don't think it is clear exactly what you have. You might post what you have, or at least a few screenshots to illustrate.