MS Access 2000 cannot edit fields

[aconway]

I am using MS Access to interface with an ODBC connected MySQL database. I have several tables that I need to view and in a single query spreed sheet. But, access is not letting me edit the fields. When I try to edit my computer goes "bing".

So here is a run down of my tables and my query.

tables: contact, email, phone, address, report, report_validate, report_error

contact can have multiple emails, phone numbers, addresses and reports.

reports relates to report_validate and report_error as one-to-one on both tables.

For the query I want to only show the primary phone, primary email, primary address, all reports and the associated report_validate and report_error

so my query is kind of like this:

SELECT contact.name, contact.last_name, phone.phone, email.email, address.address, address.city, address.state, address.zip, reports.*, report_validate.*, report_error.*
FROM (((((contact LEFT JOIN reports ON reports.report_contact = contact.contact_id) LEFT JOIN reports_validate.report_id = reports.report_id) LEFT JOIN report_error ON report_error.report_id = reports.report_id) LEFT JOIN email ON email.email_contact = contact.contact_id) LEFT JOIN phone ON phone.phone_contact = contact.contact_id) LEFT JOIN address ON address.address_contact = contact.contact_id
WHERE address.is_primary = 1
AND phone.is_primary = 1
AND email.is_primary = 1


I was able to edit fields when I removed the address, phone and email content.

 

[JordanCN2]

That is not an editable query. You have what appears to be a one to many query so Access cannot edit the records.

 

[PHV]

Try to replace the LEFT JOIN whith INNER JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aconway]

@JordanCN2
I believe you are right, I tried for hours rewriting this query. It became editable once I removed the phone, address and email tables.

 

[PHV]

Again, due the WHERE clause you don't have to use LEFT joins ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886