Join issue/question

  • Thread starter Thread starter Esaw
  • Start date Start date
E

Esaw

I have the following query:

SELECT ArtSubmissions.DateSent, ArtSubmissions.SentTo,
ArtSubmissions.FormSent, ArtSubmissions.StyleNumber,
ArtSubmissions.StyleDescription, ArtSubmissions.SentBy,
ContactList.ContactName
FROM ArtSubmissions INNER JOIN ContactList ON ArtSubmissions.ID =
ContactList.ContactName;

Which is bringing up the mismatch error because the join is between
ArtSubmissions.ID and ContactList.ContactName These two fields (from two
different tables) have a relationship and I understand why there is an error
(different types of fields), but I don't know how to run this so the
ContactName related to the ArtSubmissionsID shows up in the query. Right now,
if I take out the join, nothing shows in the ContactName field, but the the
info from the ArtSubmissions table shows up.

In the end, I would like to be able to create a report that groups the
ArtSubmissions by ContactName.

Could someone help me with this?
thanks so much for you time!
 
how are the two tables linked

i take it ArtSubmissionsID is an auto number and contactname is a
textfield

is there an id in the ArtSubmissions table that stores the contactid

Regards
Kelvan
 
Does your ContactList table have an ID that goes with the ContactName? If
so, then join ID to ID.
 
Thanks guys, I went back and created a relationship by ID and then joined
them by ID in the query and ContactName is still not showing up in the query.
Here's the query now.

SELECT ArtSubmissions.ID, ArtSubmissions.DateSent, ArtSubmissions.SentTo,
ArtSubmissions.FormSent, ArtSubmissions.StyleNumber,
ArtSubmissions.StyleDescription, ArtSubmissions.SentBy,
ContactList.ContactName
FROM ArtSubmissions LEFT JOIN ContactList ON ArtSubmissions.ID =
ContactList.ID;

I've tried it INNER and RIGHT JOINs as well and no go.
 
I went back and created a relationship by ID
Did you populate the ID field?
Your ContactList table would have ID and ContactName related in a
one-to-many to the ArtSubmissions table.
 
Back
Top