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.

 

[Pat Hartman]

There is no advantage to using a single table that I can see. There is no efficiency to be gained. If the formats are identical, you could still reuse the maintenance form even if you used separate tables. You can't enforce RI which is a big downside. You are also giving up Cascade Delete for the notes if your parent tables allow deletions. You will be making special exception logic every time you use the table. Every query that selects from the table will require criteria to specify what table's notes are required so you can't even reuse queries unless you use parameters.

Whether you use one table or one for each note source, you can still create a report that prints out all the notes of every type by user or by date if you use a union query. But combining notes from different sources probably doesn't make sense because they would be unrelated to each other.

To combine all the notes int0 a single table, include a column with "table" so you can use that as criteria for most purposes so you can extract only the notes for a specific table type as Maj suggested earlier. Then keep CreateDT and CreatetBy columns as well as the ID of the parent record so you can join to the parent table.

When you keep notes, you need to decide if they can ever be changed and if they can be changed, can only the original author change them or can anyone change them?

 

[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...

 

[Pat Hartman]

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.

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.

 

[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

 

[Pat Hartman]

I made the mistake of thinking a common notes table would be "more efficient". Luckily, I didn't get too far into it before I realized it was actually more trouble.