A separate, raw copy of the database will be sent to each site which will be returned to us once the study is complete and all data has been entered.
Each copy of the database should contain a table for the Site (or Sites). This table would have fields like;
SiteID (Primary Key)
SiteName
plus any other fields you need for the general information about the Site. The other table (or tables) that store data about whatever information you are collecting at each Site should have a Foreign Key field that stores the SiteID (and
only the SiteID - no other data from the Site table should be stored in the related tables). If you know ahead of time which Site(s) each copy is going to be collecting data for, then you could actually enter the Site data ahead of time so the user wouldn't even need to enter it. If there is more than one Site for each copy of the db, then you would likely set up a main form/sub form where the main form would be based on the Sites table and the sub form(s) would be based on the related table(s). The Master/Child link of the Subform Control would automatically handle the insertion of the SiteID value in the FK field of the related tables. If each db copy only tracks info for
one Site, then it would be even simpler because you could just make the SiteID value the default for the FK field in the related tables.
If you're going to attempt this (loading the Site info ahead of time for each copy) then you must make sure that every different Site (in each of the different db copies) has a unique ID value, so you can't use Autonumber for this, you must define your own values. The SiteID would be the most important piece of data when it comes to combining all this information together, so you would need to have careful control over that.
Here are some links if you want to do some research on normalization and table design.
A tutorial series by Crystal (Access MVP)
Fundamentals of Relational Design
Allen Browne's Access page
The Access Web
Also, a second study may take place later which will require multiple user access. Do you have any advice on how to make an access database multi user friendly & protect data(base) integrity?
That's going to depend on the nature of the network. Access/Jet works well over a LAN as long as you don't have a large number of concurrent users, but it is not well suited for WAN. If you have the former then you will need to
split your database. There would be one copy of the back end (tables and relationships) on a network server and each user would have their own copy of the front end file on their machine.