Simple stuff

[gdemets]

Hi all,
I have a simple question:


I have two table: Authors from campus 1 and Authors from campus 2
I have a third one called papers. I want it to search the autors names in both (campus 1 and 2) in a single combination form, because I have several authors for a single paper, belonging frequently to different campi.
Anyone could help me?
Regards,
Greg

[weekend00]

select Author from [campus 1]...
union all
select author from [campus 2]...

[ssanfu]

Why do you have two tables for authors? What are you going to do when you have a third campus?

It sounds like you need to first normalize your structure.

Table Authors
----------
A_ID (Autonumber) PK
A_LastName (Text)
A_FirstName (Text)
A_Campus (Long) FK to table Campus


Table Campus
----------
C_ID (Autonumber) PK
C_CampusName (Text)


As far as what you have now, you could use a form with 2 sub forms. Or a union query of the two tables.

[gdemets]

Thanks,
I need two tables or more because some authors are researchers, others are students, others are external collaborators and so on. The info about them are different in kind...I need different stuff from these different kinds of authors, this is why I have created different tables. In the field authors however, all the names should figure in, independently of its kind. I need personnal info for other tables and querries.

[orange]

You may want to do a little research on Normalization.
Here is a tutorial that may be useful.
http://www.databaseanswers.org/approach2db_design.htm

[ssanfu]

I don't think you will be able do what you want to do even using a union query. If you had two Jon Smiths, in two different tables, how would you know which PK to use???

The Authors info should be in one table, with PK-FK links to other tables with the additional info you want to keep on the authors. (like a separate table for Phone numbers, linked to the Authors table.)