Junction Table -- Having trouble getting it to work

[GIraffe]

Thank you for your help upfront. I’m trying to grasp how to use a junction
table properly, because I’ve clearly missed something (this is one of those
new concepts I “thought†I understood until I tried to implement it!).

End Result – I need to generate 2 reports, 1 that will tell me what
agreements I have with what organization and 1 that will tell me all the
agreements I have with a particular organization.

I have 4 tables (simplified below) –
tAgreements
AgreeID [PK; autonumber]
AgreeNo [text]
AgreeWith [links with tAgreeWith.AgreeWithID]
Amendments [links with tAmds.AmdID]

tAgreeWith
AgreeWithID [PK; autonumber]
OrgLine1 [text]
OrgLine2 [text]
POC [text]

tAmds
AmdID [PK; autonumber]
AmdNo [text]

tJunction
JunctionID [PK; autonumber]
AgreeID [links with tAgree.AgreeID]
AgreeWithID [links with tAgreeWith.AgreeWithID]
AgreeWith [combo box; links w/ tAgreeWith.AgreeWithID] (retrieves OrgLine1)

Using a form, I have the Agreements Info and a subform for tAmds and
tJunction (both combo boxes). The Amds piece works fine. It’s the Junction
subform I’m having problems with.

When I have a new agreement, I go into tAgreeWith and enter all the data on
the organization or organizations. Then I go to the Agreements form, enter
all the data/amendments for the Agreement. When I go to the AgreeWith
subform, I am unable to “see†the new organization I added earlier (I’ve
conclude this is because in order to “see†this new org, I need to have it
linked to an AgreeID which does not generate until I enter the info in to the
Agreement form – it’s kindof a chicken and egg thing).

My questions (1) what am I doing wrong and how do I get this to work and (2)
how can I combine OrgLine1 & OrgLine 2 for one “unit†to appear in my
AgreeWith subform?

I hope I’ve explained this okay. Please let me know if you need
clarification or if you have questions.

As always, thank you.

G

 

[Michael Gramelspacher]

Thank you for your help upfront. I’m trying to grasp how to use a junction
table properly, because I’ve clearly missed something (this is one of those
new concepts I “thought” I understood until I tried to implement it!).

End Result – I need to generate 2 reports, 1 that will tell me what
agreements I have with what organization and 1 that will tell me all the
agreements I have with a particular organization.

I have 4 tables (simplified below) –
tAgreements
AgreeID [PK; autonumber]
AgreeNo [text]
AgreeWith [links with tAgreeWith.AgreeWithID]
Amendments [links with tAmds.AmdID]

tAgreeWith
AgreeWithID [PK; autonumber]
OrgLine1 [text]
OrgLine2 [text]
POC [text]

tAmds
AmdID [PK; autonumber]
AmdNo [text]

tJunction
JunctionID [PK; autonumber]
AgreeID [links with tAgree.AgreeID]
AgreeWithID [links with tAgreeWith.AgreeWithID]
AgreeWith [combo box; links w/ tAgreeWith.AgreeWithID] (retrieves OrgLine1)

Using a form, I have the Agreements Info and a subform for tAmds and
tJunction (both combo boxes). The Amds piece works fine. It’s the Junction
subform I’m having problems with.

When I have a new agreement, I go into tAgreeWith and enter all the data on
the organization or organizations. Then I go to the Agreements form, enter
all the data/amendments for the Agreement. When I go to the AgreeWith
subform, I am unable to “see” the new organization I added earlier (I’ve
conclude this is because in order to “see” this new org, I need to have it
linked to an AgreeID which does not generate until I enter the info in to the
Agreement form – it’s kindof a chicken and egg thing).

My questions (1) what am I doing wrong and how do I get this to work and (2)
how can I combine OrgLine1 & OrgLine 2 for one “unit” to appear in my
AgreeWith subform?

I hope I’ve explained this okay. Please let me know if you need
clarification or if you have questions.

As always, thank you.

G

Another idea:

CREATE TABLE Parties (
PartyID IDENTITY (1,1) NOT NULL PRIMARY KEY,
PartyName VARCHAR (100) NOT NULL,
OrganizationName VARCHAR (100) NOT NULL,
OrgLine1 VARCHAR (100) NOT NULL,
OrgLine2 VARCHAR (100) NULL,
POC VARCHAR (100) NULL);

CREATE TABLE Agreements (
Party1 LONG NOT NULL
REFERENCES Parties (PartyID),
Party2 LONG NOT NULL
REFERENCES Parties (PartyID),
AgreementNo VARCHAR (10) NOT NULL,
PRIMARY KEY (Party1,Party2,AgreementNo));

CREATE TABLE AgreementAmendments (
Party1 LONG NOT NULL,
Party2 LONG NOT NULL,
AgreementNo VARCHAR (10) NOT NULL,
CONSTRAINT fk_Agreements
FOREIGN KEY (Party1, Party2, AgreementNo),
AmendmentNo VARCHAR (10) NOT NULL,
FOREIGN KEY (Party1, Party2, AgreementNo, AmendmentNo));

 

[GIraffe]

Hi Michael:

Thank you. Let me be sure I understand your instructions. I only have 3
tables (Agree, AgreeWith(Parties), Amds). I link the PartiesID in the Agree
table. Here's where I need clarification, you mention Party1 and Party2 (2
separate fields, each linked to the tParties.PartiesID), I'm concluding this
would be for the 2 organizations I may have an agreement with. If that's the
case, this could become a rather lengthy set of fields in my Agree table for
the agreements where I have 8 different organizations.

Please let me know if I've misunderstood your suggested setup.

Thank you again for your suggestion.

G

Thank you for your help upfront. I’m trying to grasp how to use a junction
table properly, because I’ve clearly missed something (this is one of those
new concepts I “thought†I understood until I tried to implement it!).

End Result – I need to generate 2 reports, 1 that will tell me what
agreements I have with what organization and 1 that will tell me all the
agreements I have with a particular organization.

I have 4 tables (simplified below) –
tAgreements
AgreeID [PK; autonumber]
AgreeNo [text]
AgreeWith [links with tAgreeWith.AgreeWithID]
Amendments [links with tAmds.AmdID]

tAgreeWith
AgreeWithID [PK; autonumber]
OrgLine1 [text]
OrgLine2 [text]
POC [text]

tAmds
AmdID [PK; autonumber]
AmdNo [text]

tJunction
JunctionID [PK; autonumber]
AgreeID [links with tAgree.AgreeID]
AgreeWithID [links with tAgreeWith.AgreeWithID]
AgreeWith [combo box; links w/ tAgreeWith.AgreeWithID] (retrieves OrgLine1)

Using a form, I have the Agreements Info and a subform for tAmds and
tJunction (both combo boxes). The Amds piece works fine. It’s the Junction
subform I’m having problems with.

When I have a new agreement, I go into tAgreeWith and enter all the data on
the organization or organizations. Then I go to the Agreements form, enter
all the data/amendments for the Agreement. When I go to the AgreeWith
subform, I am unable to “see†the new organization I added earlier (I’ve
conclude this is because in order to “see†this new org, I need to have it
linked to an AgreeID which does not generate until I enter the info in to the
Agreement form – it’s kindof a chicken and egg thing).

My questions (1) what am I doing wrong and how do I get this to work and (2)
how can I combine OrgLine1 & OrgLine 2 for one “unit†to appear in my
AgreeWith subform?

I hope I’ve explained this okay. Please let me know if you need
clarification or if you have questions.

As always, thank you.

G

Another idea:

CREATE TABLE Parties (
PartyID IDENTITY (1,1) NOT NULL PRIMARY KEY,
PartyName VARCHAR (100) NOT NULL,
OrganizationName VARCHAR (100) NOT NULL,
OrgLine1 VARCHAR (100) NOT NULL,
OrgLine2 VARCHAR (100) NULL,
POC VARCHAR (100) NULL);

CREATE TABLE Agreements (
Party1 LONG NOT NULL
REFERENCES Parties (PartyID),
Party2 LONG NOT NULL
REFERENCES Parties (PartyID),
AgreementNo VARCHAR (10) NOT NULL,
PRIMARY KEY (Party1,Party2,AgreementNo));

CREATE TABLE AgreementAmendments (
Party1 LONG NOT NULL,
Party2 LONG NOT NULL,
AgreementNo VARCHAR (10) NOT NULL,
CONSTRAINT fk_Agreements
FOREIGN KEY (Party1, Party2, AgreementNo),
AmendmentNo VARCHAR (10) NOT NULL,
FOREIGN KEY (Party1, Party2, AgreementNo, AmendmentNo));

 

[Michael Gramelspacher]

Hi Michael:

Thank you. Let me be sure I understand your instructions. I only have 3
tables (Agree, AgreeWith(Parties), Amds). I link the PartiesID in the Agree
table. Here's where I need clarification, you mention Party1 and Party2 (2
separate fields, each linked to the tParties.PartiesID), I'm concluding this
would be for the 2 organizations I may have an agreement with. If that's the
case, this could become a rather lengthy set of fields in my Agree table for
the agreements where I have 8 different organizations.

Please let me know if I've misunderstood your suggested setup.

Thank you again for your suggestion.

Yes, I think you are correct in that is will not work with more than two
parties. You will have a table for Parties, a table for AgreementParties and a
table for Agreements, which may be what you already had. Maybe something like
Allen Browne's model here: http://allenbrowne.com/AppHuman.html