Get a field only if it equals what I asked

[nst]

I have a table with data of people, in which table two fields contain two (not necessarily different) jobs.

I want to build a query (and later a report based on that query) which will return the name, address, phone of the person, plus only the field of the job that I asked for it.
when executing the query, the user should enter the asked job in a popup-box using the below formula.

Like "*" & [job] & "*"

Note, that there may be records containing in both job fields the same job. I want to get the field only if it contains the asked job.

Any ideas?

Thanks

 

[Rich]

Why do you have two job fields when one will suffice?

 

[nst]

It wouldn't. It is actually their parents job, mother's and father's. So I want to get, for instance, all the records, where either is a doctor, but only have in the report the field of the doctor. Meaning one record will return
father: doctor (without mother: lawyer)
and another record will return
mother: doctor (without father: teacher)

 

[neileg]

I don't think your design is right.

Never the less, if you build two queries, one on each of your two fields, and union them together, you will get what you want.

 

[nst]

I don't think your design is right.

I have a table containing people (e.g. students) and I put in one field the job of their father and in another the job of their mother.
How else could I design it?

Never the less, if you build two queries, one on each of your two fields, and union them together, you will get what you want.

Does this mean to build another query from the two queries?

 

[neileg]

I have a table containing people (e.g. students) and I put in one field the job of their father and in another the job of their mother.
How else could I design it?

Have a separate table with records of parents linked to the main table via the student ID. This way you only have one field to query against.

Does this mean to build another query from the two queries?

Yes but a union is a special type of query. Look it up in Access help.

 

[nst]

Have a separate table with records of parents linked to the main table via the student ID. This way you only have one field to query against.

I have this already done. You may want to look at http://www.access-programmers.co.uk/forums/showthread.php?t=93436 about it.

I will read about unions and come back if needed.

Thanks.

 

[neileg]

I don't understand why you started this thread? The advice that Pat gave you on the other thread is the same as I have given you here.

However, I don't think Ken's advice was sound. You have attempted to normalise your data and his advice takes you in the wrong direction.

If this was my db, I would not be holding the parent ID in the student table, I would hold the student ID in the parent table. There are several reasons for this:
1. The number of parents is not fixed. You may have considered the situation of one or two, but what if you want to hold, say, father and step father?
2. These days, same sex partnerships are not unusual. Does your student have two female parents?
3. You specific task of returning student IDs based on parent profession would be much easier.

 

[nst]

You are right, I didn't see Pat's answer before.

However, I don't think Ken's advice was sound. You have attempted to normalise your data and his advice takes you in the wrong direction.

I'm glad you say that, 'cause I was wondering about it.

I see, you are proposing of having another, separate table for the parents. I guess it is always a tough decision, depending on how much details from the parents will be needed, isn't it so?

 

[nst]

Have a separate table with records of parents linked to the main table via the student ID. This way you only have one field to query against.

This seems to be correct. The problem is that at the beginning it wasn't ment to need the parents' names and their professions to do some specific job other than just retrieving the data from the fields. Sorting and filtering upon professions came up later.

 

[neileg]

This seems to be correct. The problem is that at the beginning it wasn't ment to need the parents' names and their professions to do some specific job other than just retrieving the data from the fields. Sorting and filtering upon professions came up later.

That's what I would call specification creep!

Once you get more experience of developing databases, you will tend to structure your tables to give maximum flexibility. But your users will still catch you out!

 

[nst]

Is there a solution, without having to redesign the whole database and introduce a new tblParents table?
The advice that Pat gives in the other thread assumes that there is a tblParents which cannot exist under the current circumstances.

You need three queries and a union.
query1:
Select ... from tblParents Where ParentProf = [enter profession];

query2:
Select ..., "Mother" As ParentType from tblStudents Left join query1 on tblStudents.MotherID = query1.ParentID;

query3:
Select ..., "Father" As ParentType from tblStudents Left join query1 on tblStudents.FatherID = query1.ParentID;

query4(union):
Select * From query2
Union Select * From query3;

Rather than try to figure out what your names were, I just used example names to make it as simple as possible to see the concept. First you apply the criteria to the parents table. Then you join that query to each of the parent IDs in the student record. Then you union the result so you have both mothers and fathers in the same recordset and you know which satisfied the profession criteria. Don't forget that using this method may return two rows for the same student if both parents are in the same profession. If you want only one record per student, it is possible to change the union or to do a crosstab of the union depending on what you want.

 

[neileg]

I don't think your design is right.

Never the less, if you build two queries, one on each of your two fields, and union them together, you will get what you want.

Did you forget this solution?