If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Junction Table -- Having trouble getting it to work
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 |
#2
|
|||
|
|||
Junction Table -- Having trouble getting it to work
On Thu, 17 Jul 2008 12:44:03 -0700, GIraffe
wrote: Thank you for your help upfront. Im trying to grasp how to use a junction table properly, because Ive 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. Its the Junction subform Im 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 (Ive 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 its 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 Ive 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)); |
#3
|
|||
|
|||
Junction Table -- Having trouble getting it to work
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 "Michael Gramelspacher" wrote: On Thu, 17 Jul 2008 12:44:03 -0700, GIraffe wrote: 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)); |
#4
|
|||
|
|||
Junction Table -- Having trouble getting it to work
On Fri, 18 Jul 2008 07:14:00 -0700, GIraffe
wrote: 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 he http://allenbrowne.com/AppHuman.html |
Thread Tools | |
Display Modes | |
|
|