Table normalization/relationships 1

[Nene75]

Hi,

I have following 2 tables:

1. tblReports

ReportID - Autonumber
ReportNumber -Text
Title - Memo
Author - Text
Month - Text
Year - Text
TypeOfReport - Text
Abstract - Memo
Keywords - Text
SoftCopy - check box
HardCopy - checkbox
NumOfHardCopies - Number
ViewDoc - hyperlink
CompanyName - Text
ResearchArea - Text
Organization - Memo
Sponsor - Text

2. tblVideo

VideoID - Autonumber
VideoTitle - Text
RunsTestNumber -Text
DubTestNumber - Text
BegMonth - Text
BegDay - Number
BegYear - Number
EndMonth - Text
EndDay - Number
EndMonth -Text
NumOfCopiesAvailable - Number
ResearchArea - Text
VHS - Check box

I am developing a library database for Reports (Documents) and Videos. I have the above fields for those two tables. The only common field is the Research Area. The datbase contains Add, Edit, Browse, and Search capabilities for Reports and Videos.

Can anyone suggest how should I develop the database tables/relationship in such way when user wants to 1. search all the videos and reports on any one particular Research Area?

Any help provided would be helpful.
Thanks!

 

[fneily]

You could set up a third table called Research_Area. It has two fields: ResearchID and Research Description. Primary key is ResearchID. So this table contains all the possible research areas.
Then in both tables, your ResearchArea would be replaced by ResearchID.
Now you have a one to many between Research_Area and tblReports and a one to many between Research_Area and tblVideo.
The query is then easy. Bring up all tables in a query, have a parameter prompt for the ResearchID. The user then justs types in the ID he wants and it'll "spit" out the result.

Neil

 

[Nene75]

Thanks Neil for the prompt reply!
Unfortunately I was tied up in the other projects so I couldn't reply to you. Your reply helped a lot on sorting out the relationship between the tables.

Thanks a lot!