Solved How to make editable query of Contacts linked to table Notes and Table EmailAccounts?

[MsAccessNL]

I want to make a seperate table to store al my notes "T_Note". The Table 'T_Note" is linked to every table wich should have a Note field. So for instance table: T_Company can have a note and T_Contact can have note.

Next i want also a seperate table "T_MailAccount" for all the email adresses, one contact can have multiple emails. If I make a table contacts (T_Contact) and a Table Notes (T_Note) with a FK "ContactID" it works fine, but when i make a query with T_Contact (PK) connected to T_MailAccount (FK) and T_Notes (FK), it's no longer editable.

So how do i make a query to make an editable Form with al the contact info, including Contact-Note and Contact-EmailAccounts?

 

[mike60smart]

Hi

When you need Notes for T_Company then you need a related table T_CompanyNotes
The same apples for when you need Notes for T_Contact then you need a related table for TContactNotes

 

[MsAccessNL]

Hi

When you need Notes for T_Company then you need a related table T_CompanyNotes
The same apples for when you need Notes for T_Contact then you need a related table for TContactNotes

That's shitty, i wanted one table with all my notes linked to different tables...

 

[MajP]

You can have one table. Why not? Simply add a field to hold the related table name and filter your query to the appropriate table.
You just cannot enforce referential integrity

 

[mike60smart]

I always thought that if you cannot Enforce referential Integrity then you are open to adding unrelated data.

 

[MsAccessNL]

You can have one table. Why not? Simply add a field to hold the related table name and filter your query to the appropriate table.
You just cannot enforce referential integrity

It will work when i link T_Contact to T_Note but if I also link to T_EmailAccount it does't.

 

[mike60smart]

It will work when i link T_Contact to T_Note but if I also link to T_EmailAccount it does't.

Hi
Can you upload a copy of your db?

 

[MsAccessNL]

Hi
Can you upload a copy of your db?

I understand your question, but it's one big mess of everything i am trying...Thnx anyway

 

[CJ_London]

You just cannot enforce referential integrity

I always thought that if you cannot Enforce referential Integrity then you are open to adding unrelated data.

you can create a child record without a parent by leaving the foreign key as null (not zero or zls). When the FK is populated, that is when referential integrity is enforced.

It will work when i link T_Contact to T_Note but if I also link to T_EmailAccount it does't.

not sure under what circumstances you would need to do this, but assuming your TNotes tables has an FK for contacts and another FK for email, in the query grid, bring the tnotes table through twice, left join contacts to one and email to the other. You will also need to join contact to email as well because you can't have a cartesian query and left joins.

To make it editable, you may need to change the recordset type to 'dynaset - inconsistent updates' and perhaps change the SELECT to SELECT DISTINCTROW, but no guarantees that either will work. You would need to try it and see.

The general rule for forms is one table, one form. So think you would be better looking at using subforms rather than a relatively complex query.

 

[MsAccessNL]

you can create a child record without a parent by leaving the foreign key as null (not zero or zls). When the FK is populated, that is when referential integrity is enforced.


not sure under what circumstances you would need to do this, but assuming your TNotes tables has an FK for contacts and another FK for email, in the query grid, bring the tnotes table through twice, left join contacts to one and email to the other. You will also need to join contact to email as well because you can't have a cartesian query and left joins.

To make it editable, you may need to change the recordset type to 'dynaset - inconsistent updates' and perhaps change the SELECT to SELECT DISTINCTROW, but no guarantees that either will work. You would need to try it and see.

The general rule for forms is one table, one form. So think you would be better looking at using subforms rather than a relatively complex query.

assuming your TNotes tables has an FK for contacts and another FK for email, in the query grid, bring the tnotes table through twice, left join contacts to one and email to the other. You will also need to join contact to email as well because you can't have a cartesian query and left joins

Thnx, this brings me closer to the solution. I thought this would be an easy query, how wrong i was...

 

[MsAccessNL]

Sorry, forgot to address this part. The reason the query is not updateable is because you have two parallel relationships. Contact is related to mailAccount and it is also related to notes But mail account and notes have no relationship to each other. Simply having the same FK doesn't make them related. Here's a silly example I've posted a number of times so you can see that the relationships are parallel. A student may have classes and he may pets. If we create a query that joins Student to pets and Student to classes, we end up with this:

Joe, Math, Spot
Joe, English, Spot
Joe, History, Spot
Joe, Math, Blinky
Joe, English, Blinky
Joe, History, Blinky
Sue, Math, Echo
Sue, History, Echo
Sue, Math, Patches
Sue, History, Patches

However, a query that joins Customer, Order, OrderDetails works just fine because OrderDetails is a child of Order and Order is a child of Customer. They are related hierarchically.

And finally, you could consider instead of having a table just for email, have the table include different types of contact methods such as email, fax, bus phone, cell, home, etc.

Thnx, well explained..

 

[MsAccessNL]

This is the first time i had the idea to make a seperate notes table, to have an easy overview of all the customer related notes, i really didn’t see this (problem) coming. It has cost me a lot of time, but like always , the learning experience is worth a lot