Hello everyone. I wasn't sure what topic I should post this question under, so I'm posting it here in the General subforum. I'm not even sure if my title accurately conveys my issue (If you can think of a better one, let me know and I'll change it!).
I have two tables that are not related, "Faculty" and "Publications". For simplicity's sake, I'll just list the applicable fields in each.
The 'Faculty' table has two fields: FullName and PubName.
The 'Publications' table has two fields: Authors and Title.
Each publication in the table has several authors (some of which are in my 'Faculty' table and some not). The Authors field list all of the authors by their abbreviated Pub Name. I was in an extreme time crunch when I created this database, so I had no other choice but to list all authors in one field. I realize that this is probably terrible designing on my part.
My initial task was to identify if any of the people listed on my 'Faculty' table had any publications listed in the 'Publications' table. I was able to accomplish that by setting up a filter query and using the DLookUp function. Basically, I would choose a FullName from a combo box, and the queries use the associated PubName and looks it up in the Author field of the 'Publications' table. No problem.
My second task however, has left me scratching my head, as I don't know how to accomplish it. Or if it's even possible, given my current design. What I need now is to identify any publication listed in the 'Publications' table that has two or more authors also from my 'Faculty' table.
Any ideas?? I'm quite new to the Access databases, so I apologize in advance if something is not clear. Let me know, and I'll try my best to clarify.
Thanks!
Ryan
I have two tables that are not related, "Faculty" and "Publications". For simplicity's sake, I'll just list the applicable fields in each.
The 'Faculty' table has two fields: FullName and PubName.
The 'Publications' table has two fields: Authors and Title.
Each publication in the table has several authors (some of which are in my 'Faculty' table and some not). The Authors field list all of the authors by their abbreviated Pub Name. I was in an extreme time crunch when I created this database, so I had no other choice but to list all authors in one field. I realize that this is probably terrible designing on my part.
My initial task was to identify if any of the people listed on my 'Faculty' table had any publications listed in the 'Publications' table. I was able to accomplish that by setting up a filter query and using the DLookUp function. Basically, I would choose a FullName from a combo box, and the queries use the associated PubName and looks it up in the Author field of the 'Publications' table. No problem.
My second task however, has left me scratching my head, as I don't know how to accomplish it. Or if it's even possible, given my current design. What I need now is to identify any publication listed in the 'Publications' table that has two or more authors also from my 'Faculty' table.
Any ideas?? I'm quite new to the Access databases, so I apologize in advance if something is not clear. Let me know, and I'll try my best to clarify.
Thanks!
Ryan
