Many To Many Relationships in Access

[wkiernan]

Users are filling in a form based on tables in a one to many relationship:
tblNarrative (one)
tblCriteria (many)

The data is being populated into tblNarrativeCriteria.

I want to now use the data in tblNarrativeCriteria to populate tblTestResults via a form built on a query that includes tblNarrativeCriteria.

Example: Narrative #1 has Criteria #1, 2, & 3 associated with it.
The user is ready to test the Criteria related to Narrative #1.
The user selects Narrative #1, and the form is automatically populated with its associated Criteria (1,2,3). Users then report test results for each criteria (e.g. pass/fail, fail reasons).

Each Narrative and its associated Criteria(s) will be tested "many" times. The "one" side will be the test cycle.

Any suggestions are appreciated.

Thank you.

 

[Remou]

Your test table is:

[tt]TimeStamp )
NarrativeID ) Primary key
CriteriaID )
TestResult
<...>[/tt]

So clicking the button:

dteNow=Now()

strSQL="INSERT INTO tblTest (Timestamp,NarrativeID,CriteriaiD) " _
& " SELECT #" & Format(dteNow, "yyyy/mm/dd hh:nn:ss") _
& "#, NarrativeID, CriteriaID " _
& "FROM tblNarrativeCriteria " _
& "WHERE NarrativeID=" & Me.NarrativeID

CurrentDB.Execute strSQL, dbFailOnError

http://lessthandot.com

 

[wkiernan]

Thank you for your response, but I'm having trouble understanding it. Please bear with me.

The NarrativeID is listed as the primary key, but the narratives will be selected and tested more than once.

Also, how am I linking the test table to the NarrativeCriteria table?

The information in tblNarrativeCriteria should be looked at as a new "one" because the Narrative and its "many" Criteria will always be linked together, and will be used to conduct the testing (of a new system).

In the test table, the test cylce becomes the "one" where I can then select any Narrative, see its criteria, and enter the pass/fail information against each criteria for that narrative.

Am I adding a button to the form to run the code?

Thank you for your patience.

 

[Remou]

The NarrativeID is listed as the primary key, but the narratives will be selected and tested more than once.

The three combined fields :

TimeStamp )
NarrativeID ) Primary key
CriteriaID )

Are listed as primary key. The table will look like this:

[tt]
TimeStamp NarrativeID CriteriaID
27/01/2009 15:53:10 1 1
27/01/2009 15:53:10 1 2
27/01/2009 15:53:10 1 3
27/01/2009 17:03:10 1 1
27/01/2009 17:03:10 1 2
27/01/2009 17:03:10 1 3[/tt]

Also, how am I linking the test table to the NarrativeCriteria table?

Using NarrativeID and CriteriaID, assuming that the Narrative Criteria holds that information, if not, you may wish to read

http://r937.com/relational.html

The information in tblNarrativeCriteria should be looked at as a new "one" because the Narrative and its "many" Criteria will always be linked together, and will be used to conduct the testing (of a new system).

You've lost me here.

The tables I can see are:
Narrative
NarrativeID ) PK
<...>

Criteria
CriteriaID ) PK
<...>

NarrativeCriteria
NarrativeID ) FK ) Combined as PK
CriteriaID ) FK )
<...>

Testing
As described above[/tt]


In the test table, the test cylce becomes the "one" where I can then select any Narrative, see its criteria, and enter the pass/fail information against each criteria for that narrative.

See above.

Am I adding a button to the form to run the code?

Yes, that is what you mentioned.

http://lessthandot.com