Query with rowsource issue?

[mafhobb]

Hi everyone.

I am adding a query in my db and I am having a problem that I can't figure out. I am trying to get data from two tables. You can see the tables below. The common field is ContactID and the query simply lists three fields from one table and one from the other. When I create the query I get the error listed below basically telling me that the two tables are not related...but they are...I think...

I think that the problem is happening because while the table "Contacts" uses an autonumber for the field "Contact ID", the table "Calls" is displaying a rowsource from the "Contacts" table showing another column from that table instead of the actual value of "ContactID"

Is this what is happening? How can I get around this when creating the new query? I'd rather not modify the tables as they are because I do not know what else will be affected

Thanks

mafhobb

 

[Severin]

have you looked at database tools\relationships?~)

 

[gemma-the-husky]

your query should join contactid in both tables

then you should be able to see all the calls made by/to each contact

dont use the wizard. design the query manually. add the tables. drag contactid to the other table to connect the link.

i presume the wizard cannot do it because you need to set a relationship in the relationship window to do so. I rarely use wizards, so I am not sure. Wizards are not necessary to design queries, generally.

 

[mafhobb]

There is no relationship created between the two "ContactID" fields. If I try to create one from the Contacts Table to the Calls table it allows me to create a one-to-many, but "enforce referential Integrity", "Cascade Update related Fields" and "Cascade Delete related fields" are greyed out.

mafhobb

 

[mafhobb]

Gemma, you were right, when I created the table using Query Design, I was able to do it right away. I did not know the wizard could not handle this.

Thanks!

mafhobb

 

[mafhobb]

All right, now I have another question...this is the SQL I have:


SELECT Contacts.CustomerID, Contacts.FirstName, Contacts.LastName, Contacts.ContactTypeID, Contacts.PostalCode, Contacts.EmailName, Calls.SKU
FROM Calls INNER JOIN Contacts ON Calls.ContactID = Contacts.ContactID
GROUP BY Contacts.CustomerID, Contacts.FirstName, Contacts.LastName, Contacts.ContactTypeID, Contacts.PostalCode, Contacts.EmailName, Calls.SKU
HAVING (((Contacts.ContactTypeID) Like "1") AND ((Contacts.PostalCode)<>IsNull("*")) AND ((Contacts.EmailName)<>IsNull("*")) AND ((Calls.SKU) Like "ACE*"))
ORDER BY Contacts.PostalCode, Calls.SKU;

Now, how can I do it so the e-mail is not repeated? That is, if the same person (who has one unique e-mail) is in this list more than once how can I do it so the query only shows it once?

mafhobb

 

[Severin]

select distinct

 

[Severin]

if you get two rows that are different then you have to look at removing some group bys..

 

[mafhobb]

I do get multiple rows which are basically the same except for one field, SKU.

What I want to do is once the CustomerID has been found once, do not display any more search results that include that same Customer ID

 

[mafhobb]

Pat: Yes you are correct, but unfortunately I need to check the SKU field as it indicates if that customer had any relationship with a family of SKUs (ACE* in this case).

How can I get around that?

Maybe keep the current query and then have a second one that calls this one and pulls only the distinct name, customer ID, ZIP and e-mail?

mafhobb

 

[gemma-the-husky]

There is no relationship created between the two "ContactID" fields. If I try to create one from the Contacts Table to the Calls table it allows me to create a one-to-many, but "enforce referential Integrity", "Cascade Update related Fields" and "Cascade Delete related fields" are greyed out.

mafhobb

if the enforce RI is greyed out, it is probably because you have some calls recorded for contacts which do not exist in the contacts table. once you resolve these (either delete the calls, or create the contact) you will be able to set up the RI

use an unmatched query to find the offending rows.

you see a similar issue (but at a different point) if you try to create a unique index on a table that would have duplicate vlaues in the index you are trying to create.