search multiple columns in table for one item/word

[mate]

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

[ADezii]

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

SELECT Table1.ID, Table1.Item1, Table1.Item2, Table1.Item3, Table1.Item4
FROM Table1
WHERE Table1.Item1 Like "*Pen*" OR Table1.Item2 Like "*Pen*" OR 
Table1.Item3 Like "*Pen*" OR Table1.Item4  Like "*Pen*";

[mate]

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?

[mate]

It has also brought "(" brackets like this:

WHERE (((Table1.Item1) LIKE "*Pen*")) OR (((Table1.Item2) LIKE "*Pen*")) OR 
(((Table1.Item3) LIKE "*Pen*")) OR (((Table1.Item4)  LIKE "*Pen*"));

Just wonder what the problem could be.

[mate]

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.

[theaybaras]

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

?

[NeoPa]

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.

[mate]

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.

[NeoPa]

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.

[mate]

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.

[NeoPa]

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

Field; Type; IndexInfo
TeacherID; Autonumber; PK
Teacher; String
etc; Various

Table Name=tblSubject

Field; Type; IndexInfo
SubjectID; Autonumber; PK (Notice field name changed)
Subject; String

Table Name=tblTeacherSubject

Field; Type; IndexInfo
TeacherID; Number; FK & Compound PK
SubjectID; Number; FK & Compound PK
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.

[NeoPa]

A basic query to get the data would be :

SELECT tS.SubjectID,
       tS.Subject,
       Max(IIf(tTS.TSType='C1',tT.Teacher,Null)) AS Convenor1,
       Max(IIf(tTS.TSType='C2',tT.Teacher,Null)) AS Convenor2,
       Max(IIf(tTS.TSType='L1',tT.Teacher,Null)) AS Lecturer,
       Max(IIf(tTS.TSType='T1',tT.Teacher,Null)) AS Tutor1,
       Max(IIf(tTS.TSType='T2',tT.Teacher,Null)) AS Tutor2,
       Max(IIf(tTS.TSType='T3',tT.Teacher,Null)) AS Tutor3,
       Max(IIf(tTS.TSType='T4',tT.Teacher,Null)) AS Tutor4,
FROM (tblTeacherSubject AS tTS
      INNER JOIN tblSubject AS tS
  ON tTS.SubjectID=tS.SubjectID)
     LEFT JOIN tblTeacher AS tT
  ON tTS.TeacherID=tT.TeacherID
GROUP BY tS.Subject
ORDER BY tS.Subject

[NeoPa]

Any selecting of the data would have to be inserted as a HAVING line before line 16 and would be something like :

HAVING 'Mr Micawber' In([Convenor1],
                        [Convenor2],
                        [Lecturer],
                        [Tutor1],
                        [Tutor2],
                        [Tutor3],
                        [Tutor4])

[NeoPa]

Any selecting of the data would have to be inserted as a HAVING line before line 16 and would be something like :

HAVING 'Mr Micawber' In([Convenor1],
                        [Convenor2],
                        [Lecturer],
                        [Tutor1],
                        [Tutor2],
                        [Tutor3],
                        [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 :)

[theaybaras]

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!

[NeoPa]

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

[mate]

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

[NeoPa]

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.

[mate]

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

[mate]

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

[MMcCarthy]

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

[mate]

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

Thanx : )

[mate]

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

[mate]

Could u please help with the message #20

Thanx : )

[MMcCarthy]

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.

[MMcCarthy]

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.

WHERE nz([Convenor1], "") <> ""

[NeoPa]

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 :

HAVING ([Convenor1] Is Null) AND ([Convenor2] Is Null)

[NeoPa]

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

[mate]

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

[NeoPa]

The Control source of [Job Status] should be set to something like :

=IIf(Date() Between Me.[Job Start] And Me.[Job End],"Employed","Not Employed")

[mate]

This one didn't work.....may be I brought the wrong changes.....

=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 :)

[NeoPa]

This one didn't work.....may be I brought the wrong changes.....

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

[mate]

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

Below is the code I used

=IIf(Date() Between [JobSt] And [JobEn],"Employed","Not Employed")

[NeoPa]

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 :

=IIf(Date() Between Me.[JobSt] And Me.[JobEn],"Employed","Not Employed")

[mate]

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

[NeoPa]

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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

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 :(

[NeoPa]

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.

[mate]

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

[NeoPa]

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.

[NeoPa]

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