Relationships in Access

[elamadon0228]

Hello! I'm an access newbie and I'm creating a database for a research project I am designing and I have a question regarding the relationship feature of Access. More specifically, how to link the results of a query to a new table.



I have an inclusion/exclusion table and form. From this, I generated a query to list, by patient id number, all those with that status of "include". I would like data from this query to translate over to a second table for raw data collection. I have successfully executed the query to pull only those patient specified, but I cannot seem to get the patient ID number to link across the board.

Patient ID from inclusion/exclusion table - Subjects to Include query - Subjects to include auto-populate in new table "Data Collection". I plan to print my query and then search my form for this table to pull up the record and record the information. Would it be easier to just do it all in ONE table and then use multiple forms? Then run a query or report that just shows included and all other info? Ideas are welcome!


How do I successfully implement this? Is there something else I should be doing?

Thank you!

[June7]

What do you mean by 'translate over to a second table'? Second table has data records associated with patient? Is PatientID saved in 'second' table? Build another query that links query to second table or link the original tables in first query.

What is new table 'Data Collection' for? Why would you need to populate another table?

[elamadon0228]

I was planning to have a second table collecting data of included study patients. Patient ID is a field in both tables and the query. I would like the patient id from Table "A" to link to Query 1 and then the patient ID from the query to populate into Table "B" for raw data collection. I can do it in a single table if that's easier I guess and create multiple forms. I was just trying to keep that data separate. The point of the first table was to go through inclusion/exclusion criteria. Then I would run a query to determine who, out of the 400 patients I'm assessing would be included. Then have a second table with just these subjects to collect raw data for analysis (study objectives). Make more sense? I did link the query to the table but nothing is populating.

[orange]

Relationships between tables are a representation of your business rules/facts.
For example: from Database answers.org

  
The Business Rules have been drafted and are listed below.  The Area being Modelled is :-
 
Student Activities


A. The Things of Interest include

A.1 Activities
A.2 Forms
A.3 Staff
A.4 Students
A.5 Group Years
A.5 Others to be determined

B. What do we know about The Things of Interest? Facts/Rules -that are used to establish relationships


B.1 Activities are available, and each Activity has a cost, name and description.

B.2 Activities are scheduled as and when appropriate.

B.3 A Scheduled Activity has Contact details, eg e. who is running it, where, what days of the week it is run and for how long.

B.4 A Scheduled Activity can be run by one or many members of Staff.

B.5 Each Form has one and only one member of Staff as the Form Teacher.

B.6 A Student can participate in zero, one or many Activities.

B.7 A Student can have one and only in one Address at a time.

B.8 A Student can have a history of multiple Addresses.

B.9 A Student can be in one and only in one Form at a time.

B.10 A Year Group consists of two and only two Forms.

B.11 Others to be determined

More info on database relationships

I suggest you start with a clear description of the "business" in plain English. Then make sure your database structure --tables and relationships -- match your business requirements;

Good luck.

[June7]

One table of patients. One table of associated data.

Build form/subform arrangement. Main form bound to patients table or a query. Subform bound to data. Filter main form to display only 'included' patients. Master/Child Links properties of subform container will automatically save patient ID to the subform record.

[elamadon0228]

Thank you!!!