update query question

[Christine]

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!

 

[Jeff L]

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.

 

[Christine]

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!

 

[Jeff L]

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.

 

[Christine]

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
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!

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!

 

[Jeff L]

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.

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
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!

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
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.


Christine wrote:
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!