Updating form based on query with Distinct

[eadiem]

I have a form which is based on the following query. The two tables are related: tblContacts has the primary key ID (Autonumber) and since each contact may produce more than one commodity, tblCommodities.ID is a foreign key.

SELECT DISTINCT tblContacts.ID, tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company, tblContacts.Phone, tblContacts.Phone2, tblContacts.Cell, tblContacts.Fax, tblContacts.[E-mail]
FROM tblContacts LEFT JOIN tblCommodities ON tblContacts.ID = tblCommodities.ID
WHERE (((tblCommodities.Commodity)="Blueberries"))
ORDER BY tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company;

I would like to be able to edit the tblContacts fields from the form but it seems the Distinct statement prevents me from doing so. When I remove Distinct I can edit, but of course the problem is duplicate records show up.

Any ideas?

Thanks,
Eadie

 

[eadiem]

Amazingly enough solved this one myself.
Seems I needed to do a couple of things.
First of all edit the table join properties so "Enforce Referential Integrity" is allowed. Then check "Allow Cascading Updates".
Also use Distinctrow instead of Distinct, not sure why this makes a difference but it does. Here is the updated query that allows updates in the form.



SELECT DISTINCTROW tblContacts.ID, tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company, tblContacts.Phone, tblContacts.Phone2, tblContacts.Cell, tblContacts.Fax, tblContacts.[E-mail] FROM tblContacts INNER JOIN tblCommodities ON tblContacts.ID=tblCommodities.ID WHERE (((tblCommodities.Commodity)="Blueberries")) ORDER BY tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company;

 

[traingamer]

Try (not tested):

SELECT tblContacts.ID, tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company, tblContacts.Phone, tblContacts.Phone2, tblContacts.Cell, tblContacts.Fax, tblContacts.[E-mail]
FROM tblContacts  
where [COLOR=red]tblContacts.ID in (Select First(ID) As FirstId
From tblCommodities 
WHERE tblCommodities.Commodity="Blueberries"
GROUP BY tblContacts.ID)[/color]
ORDER BY tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Company;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill