Queries in the same table

  • Thread starter Thread starter Bruenor
  • Start date Start date
B

Bruenor

Is it possible to create a query that combines two columns in the sam
table? Here's what I'm trying to do. I have "Faculty" and "Secon
Faculty" as column names. I want to make it so that there is
"Combined" column that takes the input from the "Faculty" column an
puts it into the "Combined" column. If there is data in the "Secon
Faculty" column then it would put it after the data in the "Combined
column, seperated with a "/". Is there a way to do this
 
You can do this in the query grid by using a calculated field.


Field: FullFaculty: Faculty & ("/" + [Second Faculty])

The & always concatenates (adds together) strings and the + concatenates when
the string is not null, but if a string is null it will return a null.

That means that if Second Faculty is null (looks blank) then you won't get the /
after the Faculty field contents; and if the Faculty field is null, you will get
/SecondFaculty name (but that will alert you to a problem in your data.

That said, you do have a design problem in that you have two fields that contain
the same type of information - Faculty. What happens if the course has three
faculty, four, five, ....

Your design would benefit from having a FacultyTeachingCourses table - which
might have two fields. One field pointing the primary key of the courses table
and a second pointing to the primary key of the Faculty table. That way you
could have zero to multiple faculty members teaching each course.
 
You can build an expression in your query:
combined: Iif([Faculty]is null, [Second Faculty], Iif([Second Faculty] is
null, [Faculty], [Faculty]& " " & [Second Faculty]))

Eechhutti R.Rajasekaran
 
Back
Top