By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,806 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,806 IT Pros & Developers. It's quick & easy.

search multiple columns in table for one item/word

P: 18
I have a table like this:
ID Item1 Item2 item3 Item4
1 pen chair ruler table
2 ruler pencil chair pen
3 table ruler pen chair
4 pencil table chair pen

for example, how do I search for the item 'pen' from fields Item1, Item2, Item3 and Item4

Await for you quick reply.

Maha
May 23 '07 #1
Share this Question
Share on Google+
40 Replies

ADezii
Expert 5K+
P: 8,750
I have a table like this:
ID Item1 Item2 item3 Item4
1 pen chair ruler table
2 ruler pencil chair pen
3 table ruler pen chair
4 pencil table chair pen

for example, how do I search for the item 'pen' from fields Item1, Item2, Item3 and Item4

Await for you quick reply.

Maha
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table1.Item1, Table1.Item2, Table1.Item3, Table1.Item4
  2. FROM Table1
  3. WHERE Table1.Item1 Like "*Pen*" OR Table1.Item2 Like "*Pen*" OR 
  4. Table1.Item3 Like "*Pen*" OR Table1.Item4  Like "*Pen*";
May 23 '07 #2

P: 18
Thank you for the quick reply. I tried it and unfortunately it didn't work. All the fields I mentioned contain dropdown lists. Could it be a problem?
May 24 '07 #3

P: 18
It has also brought "(" brackets like this:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((Table1.Item1) LIKE "*Pen*")) OR (((Table1.Item2) LIKE "*Pen*")) OR 
  2. (((Table1.Item3) LIKE "*Pen*")) OR (((Table1.Item4)  LIKE "*Pen*"));
Just wonder what the problem could be.
May 24 '07 #4

P: 18
I just want to mention that when I typed the items (as in the example I gave first), it worked. It didn't work with the dropdown list created with the lookup wizard. Sorry to send eveything in bits.
May 24 '07 #5

theaybaras
P: 52
I just want to mention that when I typed the items (as in the example I gave first), it worked. It didn't work with the dropdown list created with the lookup wizard. Sorry to send eveything in bits.
In your combobox, which column do you have bound? In your table, is the value "pen" stored in your column? or is the value "1" or something like that? Maybe when you select "pen" in your combobox it is storing the key column data ... i.e. if you have a table called listboxdata and in it you have
Primary Key | List_Item
1 | Pen
2 | Pencil
3 | Marker


Then in a form linked to another table called datafromlist you have three comboboxes that lookup the table listboxdata.
in three records you select (Pen, Pencil, Marker), (Pencil, Marker, Pen), (Marker, Pen, Pencil)

So does your table show
Pen | Pencil | Marker
Pencil | Marker | Pen
Marker | Pen | Pencil

Or

1 | 2 | 3
2 | 3 | 1
3 | 1 | 2

?
May 24 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
Look at theaybaras' explanation as I think it explains your last problem.
More importantly though, WHY do you have that layout of data in your table?
I can't imagine a valid reason for storing it that way. Check out (Normalisation and Table structures) for tips on how that could be done better.
May 24 '07 #7

P: 18
I have solved the problem of searching multiple columns. Actually the database I got is a list of teachers table and a subjects table. Some teachers take 3 or 4 subjects. I have created dropdown list of the subjects in the teachers list. I am thinking to remove this list and create three columns in the subject list - convenor, lecturer and tutor.

The problem here for me is with the query - the staff ID would only link to one field mention above (convenor, lecturer or tutor). Lets assume it has the link to the 'convenor' field. If I add both the teachers and subject tables to a query and do a search on the subject name field, it doesn't bring the records if the convenor column is empty.

How do I do a search and bring the records i want on the subject list even if the convenor field is empty?

I really appreciate all the help you have given me so far.
May 27 '07 #8

NeoPa
Expert Mod 15k+
P: 31,770
I'm afraid you'll have to explain which way you're going before we can help further. If you choose to stay with your earlier design, then I suggest you deal with theAybaras (and respond directly to his post(s). If you decide that the normalised way is worth going for, then you need to respond to my post directly before I can take you any further. I'm happy to do so, but not without your feedback. I assume from your post, that you're not actually very interested in making those changes that would be required (which I understand, it does involve a certain amount of upheaval).
Whichever way you wish to proceed, direct responses to the experts' posts are the best way forward.
May 27 '07 #9

P: 18
Like I said in my previous message there are two main tables - teachers table and subject table. All I need is a database that'll tell me who is the subject Convenor (may have more than 1), Lecturer and Tutors (may have up to 3). For some subjects the Convenor may be the one who is lecturing and also taking the tutorial. For some different people would be doing the above three jobs. I thought to put the teachers' ID number on the subject row under Convenor, Lecturer and Tutors columns.

fields in Teachers table
ID, Name, etc

fields in Subject table
Subject number, Subject, Convenor1, Convenor2, Lecturer1, Tutor1, Tutor2, Tutor3, Tutor4

Columns Convenor1.. to ..Tutor4 are fields where staff ID would be entered. Not all records would have these fields filled as some subjects would not have, for example, Convenor1, Tutor2. Would I be able to do a search on the Subject field and see who are convening, lecturing or tutoring of this subject?

I may be wrong. I am very keen to explore other ways I could do this database. I am doing this database to develop my MS Access skills. Your help would be very much appreciated.
May 30 '07 #10

NeoPa
Expert Mod 15k+
P: 31,770
If you've looked at the Normalisation and Table structures link, you'll suspect that the Subject table is not designed as it should be. I've taken the liberty of changing some of the table and field names.
Table Name=tblTeacher
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. TeacherID; Autonumber; PK
  3. Teacher; String
  4. etc; Various
Table Name=tblSubject
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. SubjectID; Autonumber; PK (Notice field name changed)
  3. Subject; String
Table Name=tblTeacherSubject
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. TeacherID; Number; FK & Compound PK
  3. SubjectID; Number; FK & Compound PK
  4. TSType; String (Allowed entries - C1; C2; L1; T1; T2; T3; T4 matching staff positions)
PK = Primary Key (Unique index)
FK = Foreign Key (Index into another table)

I will put together a basic query to access this, returning the data you started with.
May 30 '07 #11

NeoPa
Expert Mod 15k+
P: 31,770
A basic query to get the data would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT tS.SubjectID,
  2.        tS.Subject,
  3.        Max(IIf(tTS.TSType='C1',tT.Teacher,Null)) AS Convenor1,
  4.        Max(IIf(tTS.TSType='C2',tT.Teacher,Null)) AS Convenor2,
  5.        Max(IIf(tTS.TSType='L1',tT.Teacher,Null)) AS Lecturer,
  6.        Max(IIf(tTS.TSType='T1',tT.Teacher,Null)) AS Tutor1,
  7.        Max(IIf(tTS.TSType='T2',tT.Teacher,Null)) AS Tutor2,
  8.        Max(IIf(tTS.TSType='T3',tT.Teacher,Null)) AS Tutor3,
  9.        Max(IIf(tTS.TSType='T4',tT.Teacher,Null)) AS Tutor4,
  10. FROM (tblTeacherSubject AS tTS
  11.       INNER JOIN tblSubject AS tS
  12.   ON tTS.SubjectID=tS.SubjectID)
  13.      LEFT JOIN tblTeacher AS tT
  14.   ON tTS.TeacherID=tT.TeacherID
  15. GROUP BY tS.Subject
  16. ORDER BY tS.Subject
May 30 '07 #12

NeoPa
Expert Mod 15k+
P: 31,770
Any selecting of the data would have to be inserted as a HAVING line before line 16 and would be something like :
Expand|Select|Wrap|Line Numbers
  1. HAVING 'Mr Micawber' In([Convenor1],
  2.                         [Convenor2],
  3.                         [Lecturer],
  4.                         [Tutor1],
  5.                         [Tutor2],
  6.                         [Tutor3],
  7.                         [Tutor4])
May 30 '07 #13

NeoPa
Expert Mod 15k+
P: 31,770
Any selecting of the data would have to be inserted as a HAVING line before line 16 and would be something like :
Expand|Select|Wrap|Line Numbers
  1. HAVING 'Mr Micawber' In([Convenor1],
  2.                         [Convenor2],
  3.                         [Lecturer],
  4.                         [Tutor1],
  5.                         [Tutor2],
  6.                         [Tutor3],
  7.                         [Tutor4])
Of course (and I promise I didn't think of this earlier), this technique could also have been used on the earlier layout.
Personally, I'd still use the new 'Normalised' structure, but at least this gives you the choice :)
May 30 '07 #14

theaybaras
P: 52
hey mate,
I've got to say, I'm with NeoPa on this for sure... it will definitely be worth the work to normalize the table structure, both for this project and for other reasons. You said you are generating this database to bump up your access skills, and there is almost nothing I think I could suggest that would be more helpful than becoming a practicing believer in normalization! Once you go through the process of normalizing your tables, it gets more and more habit to do it that way from the start, and that's a very good thing! It will make life much easier in future projects! Good luck with the db! :o)

NeoPa! Great advice, and nice query structure, that actually answered a question for me I didn't know I had! Thanks, as always!


May 30 '07 #15

NeoPa
Expert Mod 15k+
P: 31,770
NeoPa! Great advice, and nice query structure, that actually answered a question for me I didn't know I had! Thanks, as always!
No problem theAybaras. I had to laugh at that :D
I was quite pleased at coming up with the reverse In() structure myself, I have to admit :)
May 30 '07 #16

P: 18
More help needed.

I am a bit confused with couple of points

In the normalisation article, mccarthy has pointed out: "Do not use multiple fields in a single table to store similar data". My subject table has got Convenor 1, Convenor2, Lecturer, Tutor1, Tutor2 etc. All these fields would have teacherID numbers. Same staff ID may be repeated in the convenor1 column many times.

NeoPa, in your message (see #11 above) you have said to create a third table

Table Name=tblTeacherSubject
Code: ( text )
1.Field; Type; IndexInfo
2.TeacherID; Number; FK & Compound PK
3.SubjectID; Number; FK & Compound PK
4.TSType; String (Allowed entries - C1; C2; L1; T1; T2; T3; T4 matching staff positions)

Refering to what I said before, do you think it would allow teacherID to have same ID number repeated?
In the database I am doing, the teachers ID (eg, 426565) and subject numbers (eg: 6799) given are not genrated as an auto number.

Thanx. : )
May 31 '07 #17

NeoPa
Expert Mod 15k+
P: 31,770
I see two questions here (in the last paragraph).
  1. Referring to what I said before, do you think it would allow teacherID to have same ID number repeated?
    Yes. The (compound) PK should be made up of the [SubjectID] AND the [TeacherID] fields. This means that [TeacherID]s can be repeated as long as they are attached to different [SubjectID]s.
  2. In the database I am doing, the teachers ID (eg, 426565) and subject numbers (eg: 6799) given are not genrated as an auto number.
    This is something you should really have mentioned before, but is not a problem. Simply change the definition to Number rather than AutoNumber. It doesn't materially effect how this works.
May 31 '07 #18

P: 18
I have never created join fields. This is my first time.

Should the join table be created as a query?
OR
should it be created as a table? Would this update the database when new records are added on to the teachers table and/or subjects table? I thought a query would but not sure with tables.

It would be great if you could send a website that provides instructions with diagrams.

Thanx : )
Jun 1 '07 #19

P: 18
Just one question, not relating to what I wrote before.

How do I do a search and ONLY get the records where it has been filled. For example, on the Convenor1 column in the subject table. I want to get all the Convenors who have been assigned to convene a subject. Ie. I would not be getting the subjects that does not have a convenor.

How would I apply this to text columns?

Thanx : )
Jun 1 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
I have never created join fields. This is my first time.

Should the join table be created as a query?
OR
should it be created as a table? Would this update the database when new records are added on to the teachers table and/or subjects table? I thought a query would but not sure with tables.

It would be great if you could send a website that provides instructions with diagrams.

Thanx : )
Have a look at this tutorial and come back with any questions.

Database Normalisation and Table structures
Jun 1 '07 #21

P: 18
Sorry I meant join tables. I would like to get more information on how to do this.

Thanx : )
Jun 1 '07 #22

P: 18
I have a question here relating relationships

I noticed that when I create a relationship on the relationship window, it says 'indeterminate'. Also when I change the field on any of the tables it changes the type from 'one-to-many' to 'indeterminate'. All the relationships with one-to-many are there because I have chosen Lookup wizard in the Data type

How do I create a one-to-many relationship without going through the Lookup wizard?

Thanx : )
Jun 1 '07 #23

P: 18
Could u please help with the message #20

Thanx : )
Jun 1 '07 #24

MMcCarthy
Expert Mod 10K+
P: 14,534
I have a question here relating relationships

I noticed that when I create a relationship on the relationship window, it says 'indeterminate'. Also when I change the field on any of the tables it changes the type from 'one-to-many' to 'indeterminate'. All the relationships with one-to-many are there because I have chosen Lookup wizard in the Data type

How do I create a one-to-many relationship without going through the Lookup wizard?

Thanx : )
To create a one to many relationship.
  1. Make sure the data types of the fields in both tables are the same.
  2. Make sure the field in the many table has Indexed set to Yes (Duplicates OK)
  3. Click on the field in one of the tables and drag the mouse to the field in the other table. Realtionship should be created.
If it is showing 'indeterminate' this probably means that there is a value in the field in the many table that is not present in the one table. You will have to delete or change the value in this field.
Jun 1 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
Just one question, not relating to what I wrote before.

How do I do a search and ONLY get the records where it has been filled. For example, on the Convenor1 column in the subject table. I want to get all the Convenors who have been assigned to convene a subject. Ie. I would not be getting the subjects that does not have a convenor.

How would I apply this to text columns?

Thanx : )
You could try something like this in the criteria.


Expand|Select|Wrap|Line Numbers
  1. WHERE nz([Convenor1], "") <> ""
Jun 1 '07 #26

NeoPa
Expert Mod 15k+
P: 31,770
Just one question, not relating to what I wrote before.

How do I do a search and ONLY get the records where it has been filled. For example, on the Convenor1 column in the subject table. I want to get all the Convenors who have been assigned to convene a subject. Ie. I would not be getting the subjects that does not have a convenor.

How would I apply this to text columns?

Thanx : )
Firstly you must use the query as before (Posts #12 & #13).
Replace the HAVING clause in Post #13 with :
Expand|Select|Wrap|Line Numbers
  1. HAVING ([Convenor1] Is Null) AND ([Convenor2] Is Null)
Jun 1 '07 #27

NeoPa
Expert Mod 15k+
P: 31,770
My second point :
You talk about joining tables, but this seems to have come out of the blue a bit. I can't see why you ask about that as there appears to be no suggestion to do that. It's a good idea, don't get me wrong, but I'm wondering if you really meant to ask about the compound PK.
What makes it all a little difficult to follow, is the number of posts following one after the other, in quick succession.
Let me know what, if any, issues are outstanding (as clearly as you can).
Jun 1 '07 #28

P: 18
I need some help with date calculation

I have three columns:
Job Start, Job End and the Job Status

What I would like to do here is to display the status of the employee in the Job Status column. For example:
Job Start, Job End , Job Status
22 Feb 07, 31 May 07, Not Employed OR Contract over...
19 Feb 07, 14 Jul 07, Employed

How do I do this?

Thanx :)
Jun 14 '07 #29

NeoPa
Expert Mod 15k+
P: 31,770
The Control source of [Job Status] should be set to something like :
Expand|Select|Wrap|Line Numbers
  1. =IIf(Date() Between Me.[Job Start] And Me.[Job End],"Employed","Not Employed")
Jun 14 '07 #30

P: 18
This one didn't work.....may be I brought the wrong changes.....
Expand|Select|Wrap|Line Numbers
  1. =IIf(Date() Between Me.[Job Start] And Me.[Job End],"Employed","Not Employed")
I have used another strategy for this one. I created a text box with todays date and used it and the JobEnd date in the formula. Let me know if this isn't a good one. I'll have to check tomorrow to see if the date changes.

Now I need little help on this one....
I have tried to make the surname field in uppercase. I used >?????....... However, the problem is with surnames with hyphens '-'. It wont put a hyphen.

Pls help me with this. Thank you :)
Jun 15 '07 #31

NeoPa
Expert Mod 15k+
P: 31,770
This one didn't work.....may be I brought the wrong changes.....
Expand|Select|Wrap|Line Numbers
  1. =IIf(Date() Between Me.[Job Start] And Me.[Job End],"Employed","Not Employed")
I'm not surprised. You've posted in my code directly.
You need to post the code you actually used (after making the changes so that it reflects your control names).
I have used another strategy for this one. I created a text box with todays date and used it and the JobEnd date in the formula. Let me know if this isn't a good one. I'll have to check tomorrow to see if the date changes.
Forget this. Do it properly as suggested.
Now I need little help on this one....
I have tried to make the surname field in uppercase. I used >?????....... However, the problem is with surnames with hyphens '-'. It wont put a hyphen.
Let's leave this until we have the original problem sorted out. Then you can explain it more clearly and we'll go from there.
Jun 15 '07 #32

P: 18
After trying again, it has worked! I only put the field name and deleted whatever came before that.

Below is the code I used

Expand|Select|Wrap|Line Numbers
  1. =IIf(Date() Between [JobSt] And [JobEn],"Employed","Not Employed")
Jun 18 '07 #33

NeoPa
Expert Mod 15k+
P: 31,770
I'm pleased to hear you've managed to get it to work.
It probably IS a good idea to include the Me. bit before the controls, not because it's necessary to work, but because it makes it clearer to any reader that the objects you're referring to are controls on your form and not fields in any RecordSet.
Clearly this is not necessary, but it is good practice.
Here's how it would look done that way :
Expand|Select|Wrap|Line Numbers
  1. =IIf(Date() Between Me.[JobSt] And Me.[JobEn],"Employed","Not Employed")
Jun 18 '07 #34

P: 18
Hi

Thanx for your suggestions.

Question 1:
Another question came up today. Actually I would like to do a query to see who are Employed and who are Not Employed.

I was also wondering if I could use the code in the table. Would be great if you could suggest some options on this.

Question 2:
Do you have any suggestions regarding how to set the input mask to uppercase. I had difficulties with hyphenated '-' names. For eg: ANNA-MARIA

Thanx
Jun 20 '07 #35

NeoPa
Expert Mod 15k+
P: 31,770
Hi

Thanx for your suggestions.

Question 1:
Another question came up today. Actually I would like to do a query to see who are Employed and who are Not Employed.
I'm not clear what you're asking here. A query that selects one or the other type would depend on knowing the layout (MetaData) of the table.
Try to rephrase your question a bit more clearly.
Include MetaData for a query question.

Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Jun 20 '07 #36

NeoPa
Expert Mod 15k+
P: 31,770
Question 1(.5):I was also wondering if I could use the code in the table. Would be great if you could suggest some options on this.
Typically code doesn't work directly within a table. I'm not sure what you're after here either :(
Jun 20 '07 #37

NeoPa
Expert Mod 15k+
P: 31,770
Question 2:
Do you have any suggestions regarding how to set the input mask to uppercase. I had difficulties with hyphenated '-' names. For eg: ANNA-MARIA
You can't use L, ?, A or a as they will not allow the hyphen (-).
What about using > instead? This will force letters to upper case whatever is entered. Press F1 when the cursor is in the Input Mask field for the full info on the subject.
Jun 20 '07 #38

P: 18
Reference: Article no.36
I am sorry I do not understand some of the technical terms used in these programs......

What I have done is ... I created a text box in the form and put the code to display "Employed" and "Not Employed". I was just wondering if I could use is in the table. Anyways, my aim to see who are employed and who are not employed.

Reference: Article no.38
I tried the '>' sign in the input mask - it didn't work. It said "the value you entered isn't appropriate for the input mask '>' specified for this field"

I have tried this on a new table but no luck.

Thanx
Jun 21 '07 #39

NeoPa
Expert Mod 15k+
P: 31,770
Reference: Article no.38
I tried the '>' sign in the input mask - it didn't work. It said "the value you entered isn't appropriate for the input mask '>' specified for this field"

I have tried this on a new table but no luck.

Thanx
Did some testing and the > needs to be followed by n x 'C' for an n character field.
EG. For a ten character field you may want to use ">CCCCCCCCCC". See Help for more details.
Jun 21 '07 #40

NeoPa
Expert Mod 15k+
P: 31,770
Reference: Article no.36
I am sorry I do not understand some of the technical terms used in these programs......

What I have done is ... I created a text box in the form and put the code to display "Employed" and "Not Employed". I was just wondering if I could use is in the table. Anyways, my aim to see who are employed and who are not employed.
In that case, consider the answer to be no.
You can build a query based on the table, however, which could include a field showing "Employed" or "Not Employed".
Jun 21 '07 #41

Post your reply

Sign in to post your reply or Sign up for a free account.