Relationships (1 Viewer)

[gunnar18]

Hello. I am trying to create a relationship between all the tables but some cannot maintain integrity, even though entries exist in both tables. For example linking ENTRY of both symbolnodes to symboltext returns unique index error. They are indexed with duplicates ok.

Lets say I want to delete all records from the class 'Alternator' in the table SymbolData. Now each record has LPNTR column which is the identifier or Entry column in the other tables. I want it so if I delete lets say ALT in SymbolData, using the LPNTR of ALT, the corresponding records in the remaining tables to be deleted as well.

Thanks. I appreciate the help.

 

[JamesMcS]

I haven't looked at the zip yet but have you checked 'cascade delete related records' in your relationships?

 

[gunnar18]

Yes I have tried that. The problem seems to be that only some relationships are allowed ie symbol data to symbol dimension but others are not and I cannot figure out why.

 

[JamesMcS]

There's a few things that could cause problems, data types being one of them, primary keys being another. Have a look in the MS knowledge base and see if that helps:

http://support.microsoft.com/kb/304466

 

[GinaWhipp]

Well, I downloaded the zip file and it will not open. Says it's not a valid file. Can you reupload?

 

[gunnar18]

I read the knowledge base article. Made some changes but it is still not correctly deleting all instances. For instance, I got a relationship to work with symbol data and symbol dimension based on the ENTRY field, ie ALT. I try to create a relationship with LPNTR and it does not work. I have removed and changed primary keys and fiddled around with different methods as well. Reuploaded.

 

[GinaWhipp]

Okay, I got the database and opened it. It makes no sense to me...

1. If *ENTRY* is a Primary Key(PK) then why in so many tables as a PK? It should be in the main table only and the other tables would only have *ENTRY* as a Foreign Key (FK).

2. You have field names that are words used by Access better known as *Reserved Words*. You're going to want to fix that, have a look at...

http://allenbrowne.com/AppIssueBadWord.html

Perhaps it would help to know what this database is suppose to do...

 

[gunnar18]

The format of the database cannot be changed as it is populated in that manner by a program (E3). Currently to delete a symbol that has been added by the program, I have to right-click and hit delete. A tedious task when you have 1000+ symbols. The database I uploaded has 2 symbols. I am trying to get it so that I can delete symbols and their associated data only by deleting the record so I can mass delete by CLASS, The database has not been modified with respect to design, only the amount of records. Thanks, I appreciate your help.

My eventual goal:
Lets say there exists 1000 records in the SymbolData table. I want to delete, for example, all symbols in the CLASS 'Alternator'.
To do this in the program, I have to right-click and press delete in the context menu. This is very tedious.
In the database, I can filter by class and then select all those records simultaneously and delete.
What I'm trying to figure out is that in the SymbolData table, the LPNTR column is the ENTRY column for other tables and by deleting a record, I need it to look for entries based on the LPNTR and ENTRY fields in the other fields and cascade delete.

 

[GinaWhipp]

So the other program assigned the Primary Keys? There are no relationships showing and with all those PK's I can't figure out how to link them. I can't even figure out which is the Main table because that is the one that should have a PK and it is the only one which should have it.

 

[gunnar18]

Yes, with respect to the keys, I have not changed anything. I would assign the primary key to symboldata entry and forget the rest. I will play around again and see if i get anywhere.

 

[GinaWhipp]

Well, the problem becomes, the one that needs the PK doesn't have one... let us know the results after you finish *playing*

 

[gunnar18]

Well my attempts today have not yielded results and this seems to be going nowhere. I think I am going to abandon the idea

 

[GinaWhipp]

Sorry, just not sure what you could besides APPEND the data to properly normalized tables to get the relationships and cascading delete you need.