Contacts::Contact_ID = Artists::Contact_ID and
Contacts::Artist_ID = Artists::Artist_ID and
Contacts::Lastname = Artists::Lastname and
Contacts::Firstname = Artists::Firstname.
Please explain that relationship. Why do you need to match by 4 different pairs of fields in one relationship?
All you need is for one of those fields to not match and you don't have data to look up.
You should be able to link records just by one of the two pairs of ID fields used here.
And you must have the cursor in one of the above fields before selecting ReLookUp if you are doing this manually.
Whenever I used only-just Contacts::Contact_ID = Artists::Contact_ID OR only-just Artists::Artist_ID = Contacts::Artist_ID, I got "There are no fields that look up values based on the field "Artist_ID" (or conversely "Contact_ID").
So, I then added the Contacts::Artist_ID = Artists::Artist_ID match, thinking that would satisfy Relookup but still nothing updated. Whenever I did the Relookup (with cursor in appropriate field) I would get "In the 250 records that you are browsing copy new values for every field that has a lookup based on this field "Artists_ID?".
At that point, I added the lastname-match with similar results and then I add the firstname-match in a vain attempt to get it to work but still, no go. That's the condition I came to this post with.
I tried deleting the Relationship(s), entirely, and started from scratch with just the single-pairs of the above ID-match fields but, I still get "There are no fields that look up values based on the field "Artist_ID?".
Is it possible that this problem is due to the difference of leading zeros in the ID fields?
There are any number of possible reasons. To start, the relationship you currently have is incorrect. I would guess that no records in the one table match to any of the records in the other via the relationship you are describing here. In at least one field for each possible matchup, the data is not identical and you have no matching records. To repeat, you should only need to match records by one or the other ID fields, but that assumes that you actually have that relationship defined and the data in the ID fields truly matches.
When you establish this relationship:
Contacts::ArtistID = Artists::ArtistID
If you put the cursor in Contacts::ARtistID, select Relookup and then get "There are no fields that look up values based on the field "Artist_ID" ", it means that there is no field in Contacts that has a looked up value field option that copies data from a table occurrence named Artists--the table occurrence name used in the above relationship.
You need to describe your data and what you are trying to accomplish with this relookup operation.
Contacts::ArtistID = Artists::ArtistID - results in "There are no fields that look up values based on the field "Artist_ID".
The tables are: Contacts - Artists - Titles - Impressions - Packets - Drawers - Frames - Vendors - Invoices
I want a lookup in Contacts to lookup/update/sync with Artists:ArtistID so, I created Contacts::ArtistID as a lookup field so whenever I add a new artist record, a new Contact record is also created and vice-versa (Contacts has all address, phone info, etc.. Artists has only Names and Gallery Affiliations and is related to Titles::ArtistID). I have a Group field, in Contacts, which define each contact as Artist or Gallery or Collector or Patron or Family or Friend, etc..
Contacts::ArtistID(lookup) = Artists:ArtistID (I've also tried Contacts::ContacID = Artists::ContactID)
Artists::ArtistID = Titles::ArtistID
Titles::TitleID = Impressions::TitleID
Impressions::ImpID = Packet::ImpID
Packets::PktID = Drawers::PktID (Frames and Invoices are related to Impressions with ImpID, Vendors to Frames and Invoices)
I've included a screenshot.
That doesn't really make sense.
If your relationship matches records by ArtistID, you can't use this same relationship to look up a value into artistID as you have to have matching values in the two artist ID fields before anything can be looked up using that relationship and if you have the matching values, Artist ID does not have to look up a value as it already has the matching value. (This is a bit like using email from your computer to tell the internet service provider that your internet service is out of commission. )
I created Contacts::ArtistID as a lookup field so whenever I add a new artist record, a new Contact record is also created
Even if the data look up worked, this would not create any new records. It only copies data from an existing record on the other side of the relationship into the already existing record on this side of the relationship.
It looks like you need to rethink your basic design here.
Hmmm... I guess that puts me back to square-one using the First and Last names as the match-fields but... "You should be able to link records just by one of the two pairs of ID fields used here", you said.
"Even if the data look up worked, this would not create any new records. It only copies data from an existing record on the other side of the relationship into the already existing record on this side of the relationship." - I see it as a means to an end. A way to populate the Contacts:ContactID into the Artists table (or vice-versa). It is my understanding that once a Lookup has been performed, the looked-up values become static so, could I not then change the Lookup field(s) into a non-lookup auto-serial-number-field which could then be used as a proper ID match-field and utilize the "create new records via this relationship" option?
I'm not sure where to start with a response. I don't really know exactly how your tables are set up, how they are currently related and what changes you are trying to make. I do realize your project sounds familiar, but am not about to assume that I can remember the details correctly.
From here, I see no need for both an artistID and a contactID field. One or the other should suffice to link your records. First Name and Last Name fields should only exist in your contacts table and not be used as match fields in relationships.