 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / April 2008Design Problem -- only get partial results on multiple join
Thank you for your time and patience. This board has been a wealth of information and knowledge. I hope you can help me with my latest Access challenge. I’ve been troubleshooting for the last 3 days and have exhausted my troubleshooting knowledge. I’d appreciate any suggestions you may have. Bottom line – I have 1 table with 2 other tables linked to it by a common field. One of the 2 tables retrieves all the data correctly, the 2nd of the 2 tables only retrieves part of the data. Here’s my structure: Relationships (All are 1 > Many) tAgreements.AgreementID > tAgreementsAmds.AgreementID tAgreements.AgreementID > tAgreementWithJoin.AgreementID (referential integrity) tAgreementsWithJoin.AgreementWithID > tAgreementWith.AgreementWithID tAgreements AgreementID [autonumber] (Primary Key Links w/ tAgreementsAmds.AgreementID & tAgreementWithJoin.AgreementID) AgreementDate [date] AgreementNo [text] tAgreementsAmds AgreementsAmdsID [autonumber] (Primary Key) AgreementID [number] (Links with tAgreements.AgreementID) AmendmentNo [text] AmendmentDate [date] This structure works fine … For every agreement, I get all the Amendments associated with the agreement. It’s the next join I’m having problems with. tAgreementWithJoin AgreementWithJoinID [autonumber] (Primary Key) AgreementID [number] (Links with tAgreements.AgreementID) AgreementWithID [number] (Links with tAgreementWith.AgreementWithID) tAgreementWith AgreementWithID [autonumber] (Primary Key) (Links with tAgreementWithJoin.AgreementWithID) AgreementWith [text] I often have an agreement with more then 1 organization. For each agreement, using a combo box in Datasheet form, I choose each organization that is part of each agreement. Here’s my problem, for each agreement, only the last organization I choose will appear in my search queries or in my report. Thank you for your help. It looks normal The names are distracting me a bit so excuse me if I paraphrase. Given that the amendments are OK What I'd expect to see for the other 3 tables is tAgreements: AgreeID (PK) Agreement tOrganization OrgID (PK) Organization tAgreementOrganization AgreeOrgID (PK) AgreeID (Join from tAgreements to this field) OrgID (Join from tOrganization to this field) Other unjoined fields Your form should be based on tAgreementOrganization. You should have one combo box based on tAgreements and one based on tOrganization Is that (in essence) what you have? Evi > Thank you for your time and patience. This board has been a wealth of > information and knowledge. I hope you can help me with my latest Access > challenge. I've been troubleshooting for the last 3 days and have exhausted > my troubleshooting knowledge. I'd appreciate any suggestions you may have. > Bottom line - I have 1 table with 2 other tables linked to it by a common > field. One of the 2 tables retrieves all the data correctly, the 2nd of the [quoted text clipped - 20 lines] > This structure works fine . For every agreement, I get all the Amendments > associated with the agreement. It's the next join I'm having problems with. > tAgreementWithJoin > AgreementWithJoinID [autonumber] (Primary Key) [quoted text clipped - 9 lines] > agreement, using a combo box in Datasheet form, I choose each organization > that is part of each agreement. Here's my problem, for each agreement, only > the last organization I choose will appear in my search queries or in my > report. > > Thank you for your help. Hi Evi: Thank you SO much for your response. Sometimes I get a little "wordy" on my filed names. Thanks to this board, I have learned some critical things about field names ... and have stumbled in to some problems when I previously used special characters, etc. So I'm getting better. I appreciate your paraphrasing. For the most part you have it. However, I do not have a combo box based on tAgreements. I do have a combo box for tOrganization. My tAgreements table is basically my "primary" table that everything either links in to or is based out of. I think it's some kind of relationship thing. I'm just not clear how to figure this out. > It looks normal > The names are distracting me a bit so excuse me if I paraphrase. [quoted text clipped - 72 lines] > > > > Thank you for your help. I don't know what Agreements are (and probably wouldn't get it, if you tried to explain) but if each Agreement only exists when an Organization is added, so that you never need to add an Agreement more than once to Agreements table and it never needs to exist seperately from an organization , then your structure is quite right and you only need a combo box for the organization and a seperate organization table (I think that's your AggreementWith table, if I understand rightly) So your ammended structure will be: > > tAgreements: > > AgreeID (PK) > > Agreement OrgID (Joined From tOrganization in the Relationships window) > > tOrganization > > OrgID (PK) > > Organization Using the tAgreements, if you filter by orgID, you will see all the agreements which that organization has. But if Agreements can exist before they are added to an organization (eg you have different types of Agreements available and an Organization can choose which type they want) then you need the 3-table structure. Evi > Hi Evi: > [quoted text clipped - 88 lines] > > > > > > Thank you for your help. Hi Evi: I usually add an agreement first to tAgreement table, then scroll through my organizations via the combo box, if an organization does not exist, I go to my organization form (filling the organization table) and add the organization. The other problem I have is when I go back to my form for the tAgreement table, the organization does not appear in the combo box. I usually have to do an append query to append the new organization to the new agreement (at least I have a work around for this piece). I do not have OrgID in the tAgree, because I link tAgreementWithJoin (which is the in between table with tAgree & tOrg) using AgreeID. I'll try adding the OrgID from tAgreementWithJoin and see if it makes a difference. However, isn't this a double join for the same info? One thing I have learned through this exercisie is that the relationship setups can sure mess up how the output looks! I appreciate your patience and I thank you for all your help. > I don't know what Agreements are (and probably wouldn't get it, if you tried > to explain) but if each Agreement only exists when an Organization is added, [quoted text clipped - 128 lines] > > > > > > > > Thank you for your help. Oh I see. OK, all you need to do is go into the Properties of the Organization combo, Click on Got Focus, choose an Event Module, click just right of that to open up a code page and just above where it says End Sub type Me.TheNameOfYourCombo.Requery. Then, as soon as you click back onto your combo, the new name will appear. For my own use, I often use the DoubleClick Event to open my connected forms and the code I put in the double click event first undoes what I tried to type into the combo before finding it didn't have my choice, then puts my cursor in the previous field to the combo so that I'm all ready to enter it, causing it to requery. For that, above the End Sub of the DoubleClick Code section type Me.YourCombosName.Undo Me.TheNameOfYourPreviousField.SetFocus DoCmd.OpenForm "TheFormthat contains your organizations" (there are other ways to do this using NotInList but this will get you started) Evi > Hi Evi: > [quoted text clipped - 148 lines] > > > > > > > > > > Thank you for your help. Many, Many thanks. I'll give this a try and let you know how it goes. I truly do appreciate your patience. Curious, why do you choose the DbleClick vs. SingleClick option? May be I'm overlooking something. Thanks. > Oh I see. OK, all you need to do is go into the Properties of the > Organization combo, Click on Got Focus, choose an Event Module, click just [quoted text clipped - 195 lines] > > > > > > > > > > > > Thank you for your help. Because everytime you enter the field, you could easily fire the single click option without wanting to. Eventually, you will want to put some nice NotInList code to the combo but my slip-shod methods will get things moving at this stage. Evi > Many, Many thanks. I'll give this a try and let you know how it goes. I > truly do appreciate your patience. [quoted text clipped - 203 lines] > > > > > > > > > > > > > > Thank you for your help. Hi Evi (I don't know if I need to restart this thread or not ...) I finally got around to trying the "GotFocus" suggestion you gave me last week. I appreciate you providing me with the code string, because I do know VB. I tried both the code suggestions (1 at a time) you provided me. For the first one, my line looks like: Me.AgreementWith.Requery I ran a test under each code attempt, and my new organization addition (Test) does not appear in the ComboBox. Historically, when I needed to add a new Org, I would go to my Org form, add it, put the agreement info in the agreements form, then, then do an append query to pull the 2 together. And I can continue to do this if necessary. What could be wrong? Can you give me some thoughts on what to look for or try to troubleshoot? Your VB suggestion seems like it would work really well and provide me with a few less steps (ie, going to my "add new" button, which takes me to the form to add a new Org, then when I'm done the form closes and takes me back to my agreement form to finish adding the agreements data, then on to my append query). Thank you Evi. You have been wonderfully patient. > Oh I see. OK, all you need to do is go into the Properties of the > Organization combo, Click on Got Focus, choose an Event Module, click just [quoted text clipped - 195 lines] > > > > > > > > > > > > Thank you for your help. Is your combo named AgreementWith (go to Properties and look at its Name on the Other tab)? The On Focus only happens if you have clicked back onto your combo after clicking off it (the code that sets focus on another control, ensures that you do that but you can do it yourself while you are testing the code.) Are you closing the Org form (or pressing Shift and Enter) so that the record is saved to your table for the combo to read when it requeries? Evi > Hi Evi (I don't know if I need to restart this thread or not ...) > [quoted text clipped - 219 lines] > > > > > > > > > > > > > > Thank you for your help. Hi Evi: Yes, my combo box is named AgreementWith. Yes, I close the Org Form after I've entered the new record (the Agree Form, where the combo box is, is closed when I'm entering data in to the Org Form). I'm not sure it'll help, but let me give you a little more info on my combo box: My combo box is based off of a query -- should that matter? My combo box has 5 columns showing Org, POC (pt of contact), AWID (Org ID), AWJID (Org Join ID), & AgreeID. What currently shows when I go to the dropdown arrow of my combo box is the 5 columns I have above. If an Org has more then 1 Agreement, it's listed twice. One other clarification from my previous post -- I do NOT know VB (Sorry for the obvious oversight). Thanks Evi. > Is your combo named AgreementWith (go to Properties and look at its Name on > the Other tab)? [quoted text clipped - 266 lines] > > > in > > > > > my your combo being based on a query is not a problem, nearly all of mine are. You shouldn't need to close the form containing the combo - that's why you have the Requery code, but if you still aren't seeing the data after re-opening the closed form then your coding isn't the problem. It *sounds* as if your combo's query is based on the wrong table if each Org is listed more than once. It should be based on the Org Table which should list each org only once. You can link other tables to Org query if you need to (In a combo containing names you might want to link the Address from the address table) but each Org would only be used once. The usual structure for a combo would be the Primary field first (OrgID) - this is usually hidden in the combo by making its Column Width Property 0cm so that the field you actually see is the User Friendly field - Org - then any other fields which could be handy.. The Control Source of this combo would be OrgID which should be a Foreign Key field in the table on which your form is based. The important field in the combo, the one that does all the work, is usually the first (ugly, hidden) one so if you are putting the OrdID number into your form using your combo then OrgID should be the first field in the Combo. Or have I missed something important (perfectly possible) Evi > Hi Evi: > [quoted text clipped - 287 lines] > > > > in > > > > > > my Hi Evi: I don't think you're missing anything important ... I think I've missed something pretty critical somewhere. My combo box is based on the Org Table & OrgID is the Foreign Key to Org ID in the Agree Table. After reading your reply, I'm beginning to think I have a bigger problem then I started this inquiry with (argh!). Evi, thanks to your help, I can see that my original design of this DB was flawed, and I can't thank you enough for helping me. You've been very gracious with your time and advice -- I hope will you bear with me a little more. For starters, part of the multiple listings in Org Table is me ... when I originally created this DB 4 years ago, I didn't realize things would blossom in to what this has become (nor did I have the knowledge on DBs I have today). So, for my Org Table, if I had the same Org, however, a different field office or point-of-contact, I listed the Org twice, with new new field office or POC. That's PART of the problem. I know how to link an Org table to an OrgAdd table, however, for this situation, won't that require a combo box within a combo box -- can you do that? The other part of the problem, the same Org if it has more then one agreement, I do not know. So I'd like to start with what my query for the combo box looks like: tOrgWith (this is my conjuction table between Org and Agree) OrgWithID [PK] AgreeID OrgID tOrg OrgID [PK] [FK to tOrgWith.OrgWithID] OrgLine1 OrgLine2 I use tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2) tOrg.POC tOrgWith.OrgWithID tOrgWith.AgreeID tOrgWith.OrgID For this query, if I do not use the the 3 fields in the OrgWith Table, nothing will show up in my combo box. Secondly, my combo box is built in to a separate subform from the Agree Table. The subform contains: tOrgWith.OrgWithID tOrgWith.AgreeID tOrgWith.OrgID OrgWith combo [based off of above query] I added the combo box to get my Org name to show up, because I wouldn't know what ID # which Org was. So that's the intricacies of how all this works (or doesn't work). Once gain, Evi, I thank you for all your help. G > your combo being based on a query is not a problem, nearly all of mine are. > [quoted text clipped - 266 lines] > > > > > > > > > What I'd expect to see for the other 3 tables is > > > > > > > > > tAgreements: You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg in your combo - not the OrgID from TblAgreementWith. Don't use any fields from TblAgreementWith if you are trying to put OrgID into TblAgreementWith using your combo. That's like trying to scratch your left elbow with your left elbow! If OrgID - your Primary Field key -is Unique (as it must be, if it is a Primary Key) then having each OrgName mentioned twice can be overcome until you have time to redesign your db. All you need you need to do is to create a query for your combo. If your Org Table has OrgID, OrgName and POC then add OrgID to your query first. In the next column type OrgFOC: [Org] &" " & [POC] You can concatenate as many fields as you need here to give each record a unique name in your combo. A handy tip I learnt on the Report newsgroup is to link your fields using Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField])) If any of the fields are null then + inside the () means that it won't show anything, including the space. Use the Trim to get rid of a final space if POC has a value but AnotherField doesn't. When you make the combo, add OrgID and then your concatenated field but close the OrgID column by dragging it closed in the Wizard so that it won't show in your combo. Will this help? If your Org table does not have a Unique Primary Key field (OrgID) then Access can't possibly know which of the two OrgIDs to link to your AgreementWith table. It can be rectified but I won't start on that (long) process until I know it is needed. I've had to do it a quite a few times, when I got a design wrong :( Evi > Hi Evi: > [quoted text clipped - 330 lines] > > > > > > > > > > What I'd expect to see for the other 3 tables is > > > > > > > > > > tAgreements: Hi Evi: I've read over your reply several times and I had wondered if my OrgWith table was causing some of the problem. I am going to copy my database (so if I blow something up I have something to go back to) and give this a try. I understand your explanation and am looking forward to giving this a try. I'll be back in touch next week. Have a great weekend! G > You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg > in your combo - not the OrgID from TblAgreementWith. [quoted text clipped - 270 lines] > then > > > puts Hi Evi: I'm obviously missing something. First, I chose not to concatenate my fields, I wanted to see if I could get the principle of this to work first. I created the query [qOrg] putting 2 tables in the query [tOrg] & [tOrgWith], figuring I needed the tOrgWith table because it's a conjunction table for tAgreements & tOrg, even tho I didn't use any fields from it. From tOrg I chose OrgID, Org, POC. I made my combo box in my fAgreements, not as a subform, but as a combo box. I chose to use my above query, chose my 3 fields, related them by OrgID, chose to remember the value for a later use, went in to the Column Widths of the Properties box and made my first column (my OrgID) 0". So now only my Org shows up (don't know why POC's not showing up). However, the Org that's showing up is not the correct Org for the Agreement (on none of the agreements). Do I need to rechoose each Org for each agreement? Secondly, just to test this, I went in to my Org table and added a new Org, when back to my form and it does not show up . And lastly, some agreements have more then one Org, how can I do this in the combo box? What am I missing? Thanks for your help Evi. G > You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg > in your combo - not the OrgID from TblAgreementWith. [quoted text clipped - 270 lines] > then > > > puts I have this horrible feeling that we are both misunderstanding something. Oh for a crystal ball so I could see what is happening! Let's check that we've got the structure right. (the thread is getting long and my brain is short!) TblAgreement should contain each Agreement only once PK AgreementID TblOrg should have each org only once - PK OrgID TblAgreement With should have the foreign key fields AgreementID and OrgID The structure of your Form should be like this (basically) Give this a try and you'll see what I mean. Create a datasheet form based on the AgreementWith table. Don't add fields from the other tables just yet until you get a real feel for this sort of structure. Add a combo to it in Design View. It should be based on TblAgreement (or on a query that is based directly on that table - don't add fields from the other tables for now) It's first field should be AgreementID, pull the column closed in the Wizard, its next field (the one which you will actually see in the combo) can be any comination of fields in TblAgreement which will help you to identify that Agreement. When you get to the relevant page in the Wizard, agree to store the value in AgreementID Similarly, add a combo based on TblOrg or a query based only on that table- again make the first field OrgID, hide it and then add the OrgName use the combos to fill in the 2 Foreign Key fields in TblAgreementWith. Is this bit working, G? Evi > Hi Evi: > [quoted text clipped - 297 lines] > > then > > > > puts Hi Evi: Boy, to have you looking over my shoulder would sure have made this sooo much easier to solve. Sorry for the long thread .... Okay, here's what I have: My tables are setup exactly as you describe, with the exception of in my TblAgreementWith I also have an AgreementWithID as PK; however, I don't THINK that'll make a difference .... right? I THINK I created the datasheet form correctly. Here's what I have: Data based on TblAgreementWith AgreementWithID AgreementID OrgID Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo) Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th field, POC) In View mode of this datasheet I have AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get this fixed, I will be combining several fields in one for my Orgs) If I go to either AgreementNo or OrgLine1, I get a dropdown option and I can actually change either one on a record. And, guess what Evi? ALL my Org show up ... even the tests I setup as we've been working through this. This is a FIRST. I'm very excited. Please tell me I have it right so far. G > I have this horrible feeling that we are both misunderstanding something. Oh > for a crystal ball so I could see what is happening! [quoted text clipped - 271 lines] > > > box > > > > > is Hi G, That sounds really good. Evi > Hi Evi: > [quoted text clipped - 307 lines] > > > > box > > > > > > is Hi Evi: Thank you. I've very excited that this may actually work. So I go in to my form to add this new subform and for each existing record the right agreement # shows up with the right organization (Yes!). Then I go to add a new record, and I am unable to choose an organization in my new subform. My column headings show up (agreementno and organization), however, no drop down options for either column, so I am unable to choose the organization that goes with my new agreement. I checked the properties and the Master and Child Links are okay (AgreementID). What else should I check? Thanks Evi, I couldn't have made it this far without you. G > Hi G, That sounds really good. > [quoted text clipped - 276 lines] > > > > > > > > > > > > Once gain, Evi, I thank you for all your help. Have a look down this list Giraffe and see if they are all true Your main form needs to be based on TblAgreement. (or a query based only on that table) Your Subform needs to be based on TblAgreementWith (or its query) and linked by AgreementId Add all the fields from TblAgreementWith for now to this subform, you can always Hide unwanted columns using the Format menu. Your combo needs to be based on TblOrg (or its query) and should have from TblOrg, OrgID, Other fields from TblOrg. In Properties on the Data tab, its ControlSource should be OrgID. Next to Bound Column, it should say 1. Evi > Hi Evi: > [quoted text clipped - 294 lines] > > > > > > > > > > > > > > Once gain, Evi, I thank you for all your help. Hi Evi: For starters, I am **extremely** thankful for your patience and help and I'm sorry if I've been trying. I've not worked with conjuction tables before, so this is all very new. I can answer True to each one of your statements. And this morning, after I realized I had all true answers to your questions, I just about threw the towel in...thinking I was not going to be able to get this to work. After going over our last exchanges and seeing that everything you have given me was correct, I decided I had nothing to lose and re-created the form from scratch. I added the new subform we did and, I'm not sure why, but it works. I then added my amendments subform and it still works. I tested this by adding a new agreement and it works, and finally, I added a new org and it *still* works. I'm not sure if something got blitzed in my previous form and trying to bring in new subforms was just confusing the process or what. All I know is it works now. A humble and sincere thanks to you, Evi. This is the most difficult challenge I've had yet on Access and without your help, I never would have made it through this. And only because I don't know why it's working now and (I admit I'm wondering if it's a fluke), I've provided my subform structure below to see if anything jumps out at you that's going to come back to bite me. Here's my subform structure [for the record, I am not using any queries for this subform]: Data based on TblAgreementWith [this is my conjuction table] AgreementWithID [PK] AgreementID [PK from TblAgreement] OrgID [PK TblOrg] Combobox: TblOrg.OrgID (2nd field, OrgLine1) In properties on the data tab for the combo box, I have: ControlSource: OrgID RowSource: Select TblOrg.OrgID; TblOrg.OrgLine1 FROM TblOrg ORDER BY [OrgLine1]; BoundColumn: 1 In properties on the data tab for the subform, I have: ControlSource: TblAgreementWith In View mode of this datasheet I have AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 Sincerely, G > Have a look down this list Giraffe and see if they are all true > [quoted text clipped - 268 lines] > > > > > > > have > > > > > > > > a bigger problem then I started this inquiry with (argh!). That looks great,Giraffe. You've really stuck at it! It is possible that your form was just poorly. I've had that once or twice when a report or query suddenly acted strangely and unpredictably. It was nearly always easiest to recreate the thing from scratch after which it behaved as it should. I guess there are just some things we are not meant to know. Evi > Hi Evi: > [quoted text clipped - 320 lines] > > > > > > > > have > > > > > > > > > a bigger problem then I started this inquiry with (argh!). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.