 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006update query question
Hi I have two tables: tblOrganizations Organization OrgID PK tblContacts Person Employer (=Organization) OrgID FK I inherited this database, so I just went through and updated the Organizations table, which was missing a bunch of the organizations for the contacts (which had already been entered). Now I'd like to use that to update the Contacts table, but I'm not sure how to do it. What's tricky is that some of the Employer fields in the Contacts table have duplicates for the same organization, eg one will list "Metro" and another will have "The Metro". And since the Organizations table hadn't been completely filled out, a lot of the hundreds of contacts entered don't have an OrgID assigned to them. Any help would be appreciated! My head doesn't wrap well around query logic. Thanks! Unfortunately, there is not going to be a "quick and easy" way of doing this. You are going to have to fix some of your records manually. You could make a query that joins the employer and Organization together and update your IDs that way, but you'd have to be careful to ensure that you don't have multiple organizations with the same name. You wouldn't want to give an individual the wrong ID. Once you get your data fixed, I would strongly suggest that you elimiate the Employer field altogether. All you are doing is storing the same info in two different places, which is not good database design. Hope that helps a little. > Hi > [quoted text clipped - 21 lines] > > Thanks! Thanks for your input! I agree the Employer field seems redundant, and I think I may have figured out why it's there. It's in case our Contact's employer is not the Organization that we associate them with (we're a nonprofit watershed protection group, so a lot of volunteer tracking). I would still like to try joining the two tables in a query-- would that be an update query? I don't have much experience writing query code. Thanks! > Unfortunately, there is not going to be a "quick and easy" way of doing > this. You are going to have to fix some of your records manually. You [quoted text clipped - 35 lines] > > > > Thanks! I would strongly recommend that you make a copy of your tables before you do this, just in case something gets messed up you can revert back to the way your tables were before you started. Yes an update query is what you would use. In the query design, pick both tables and join the Employer and the Organization together. Now double click the OrgID for tblContacts. In the Update To box, put [tblOrganizations].[OrgID]. You will probably want to put Is Null in the criteria of the field as well. That way you are not overwriting OrgIDs that are already there. Run the query. > Thanks for your input! > [quoted text clipped - 47 lines] > > > > > > Thanks! OK I ran the query as you said and it ran, but the message said it was only going to update "0" rows. I don't get it. Thanks for your help! > I would strongly recommend that you make a copy of your tables before > you do this, just in case something gets messed up you can revert back [quoted text clipped - 58 lines] > > > > > > > > Thanks! Well, in that case you either don't have any records where the Employer name and the Organization is exactly the same, or you have something wrong in your query design. You can post your query if you'd like. > OK I ran the query as you said and it ran, but the message said it was only > going to update "0" rows. I don't get it. [quoted text clipped - 63 lines] > > > > > > > > > > Thanks! |
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.