Related Table Structure 2

[Domino2]

I have three tables. The first table contains many fields of which one is a memo field. The memo field contents is split into individual words (to be used later as Keywords). These words are put into Table 2, and Table 3 logs the record numbers where the words are in.

This all works okay. However there is a check done to prevent duplicate words being stored in table 2. This is where my problem is because I still have to log the records record number in table 3 but because table 2 is bypassed I don't have a foreign key for table 3 to enable data entry in the table.

In a bit of a hole?

Table 1 PK1
Table 2 FK1 = PK1
PK2

Table 3 FK2 = PK2
PK3

Hope this makes sense/clear. Cannot insert picture
Thanks

 

[lionelhill]

I think you're on the right lines, but you've numbered your tables in a way that might be confusing you.
Table 1 contains the things that need to have multiple words associated with it.
Table 2 contains words that may be used in multiple places.
You therefore have a many-many relationship between 1 and 2, so 3 is the junction table.
There should be a 1-many relationship between tables 1 and 3 because one thing in table 1 can have many words, and a many-1 relationship between table 3 and 2, and the relationships are

(Table 1)-one----many-(Table 3)-many----one-(Table 2)

Table 1 has its primary key ThingID (and no foreign keys)
Table 2 has its primary key WordID (and no foreign keys)
Table 3 has a primary key that is an autonumber and you never use, and has foreign keys ThingID and WordID maintaining referential integrity.

Access can deal with this, and will make a subform handling table 2, when stuck in a form handling table 1, display only the data from table 2 that corresponds to the correct record.

 

[Domino2]

Many thanks lionehill, you put it very clearly back to me. As I am not using bound forms/tables (using vba) I should be able to sort that out now I have a picture in my head of table joining. I have been away from Access for quite a time and forgot about junction tables.

Thanks again

 

[PHV]

Have a look here:

http://r937.com/relational.html

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

 

[Domino2]

Thanks PHV, just getting my head around vba requirements. Table 1 has the parental data in, Table 3 would be where I put my individual words in. The Table 2 would be the link between finding from a selected word which parent records had it in?

Does it mean the words would get duplicated being put into Table 3.

Without subforms and Access doing it automatically for me it's difficult to grasp a flowchart of the operation

 

[lionelhill]

Whether you end up with duplicate words is a matter of how you add the words. Unfortunately it's quite possible, because the primary key for the words table is probably an autonumber like WordID so you could have (WordID=1, WordText="apple") and another entry (WordID=59, WordText="apple").

This has to be sorted out when you add a new Thing to your first table. At the moment you're adding the words via a free text box (you don't need a memo-field associated with this if it's an unbound form). The procedure you need to follow is probably something like this:
(1) Find the first word in the text-box
(2) Look it up in the words-table to see if it exists
(3) If it doesn't, add it to the words table
(4) Get its WordID
(5) Make an entry in the junction table containing this WordID, and the ThingID of the current Thing
(6) go back to (1) and do the next word

Alternatively you could change your form to select words from a drop-down list populated from the word-table, and using a combo-box, add an event to handle words being typed that aren't already in the list. This is harder work for your users, but it slightly reduces the chance of mistyped words appearing in the word-list (aples as well as apples; aple with have its own ID and appear as a completely new and independent word).

The main point to grasp is that it's your job to add the records in both the word-table and the junction-table; the word-table needn't have duplicates, but the junction-table will have an entry for every time a word is used in any thing, so it will be the longest of the tables.

 

[Domino2]

Thanks Lionelhill,

I am doing the actions in the same order you have suggested, however when the next record is added to the database, the same wanted word (as I am excluding common words, i.e. and,f etc) then the word gets added into the word table again as the junction table has to get another entry to identify the next record being added. So I am going to get duplicated entries of words.

So now I wonder if a junction table is of benefit to me as I am incurring an extra table and not preventing data duplication.

Regards

 

[PHV]

Why not create a unique index on word in the word table.
I'd also create a unique composite index on the two FK in the junction table.

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

 

[Domino2]

Thanks PHV. So I look in the word table, and if the word is there then use it's unique number in the junction table so only duplicating and storing numbers. Sounds a much better idea.

I started with my Junction table having two foreign keys holding the primary keys of each side table. However I kept reading to highlight both items in table design and select the primary key junction, which I did. I did this because I found I could add a record in table A, or Table B without doing anything in the joined Junction table. I think this is still the case, however not worrying because I will keep integrity in my vba coding. Probably if I was using a subform then I would not have noticed this. Regards

 

[Domino2]

I should be much clearer and moving forward, but have some queries, hope I can get answers.
I have 3 tables.

Table 1 is my main table containing reports. It has a primary key ID1
Table 3 will contain keywords that are contained within within a report field in Table 1. Table 3 has a primary key ID3
Table 2 is a junction table, containing Foreign keys from both Table 1 and Table 3 (ID1 and ID3) It also has a primary key ID2 which does not do anything as no other fields are in the junction table.

I am not using subforms, everything is unbound.

I do not want to duplicate the stored Keywords, rather have a unique number as PHV suggested. So I was going to add yet another table and was going to put the individual word in it. I then thought I would use its primary key ID4 in Table 3, and as adding words or finding the word again would then put Table 3's and Table 1's ID's into the Junction table.

Is this all correct or is there another way to do it. I am still not sure if the word also has to be put in the junction table.

Hope it's clear, I cannot post an image as not having a URL

Thanks

 

[lionelhill]

I'm afraid I'm not totally following what you're doing. If all your forms are unbound, then nothing should be added to any table unless you explicitly do so in vba.

Say you're about to add Report27 to your first table, and its keywords are "cat mouse dog": My suggestion was that your vba should first look for word "cat" in the words-table. It can do this via dlookup or a query. If "cat" is found, then you don't make a new entry in the words table, instead you just remember the corresponding ID because you'll need it later. If you don't find "cat" then explicitly add it to the words table (and again, note its corresponding ID). This way there will be no duplication.
Now your vba needs to open the junction table and add a new record with foreign keys for "Cat"'s ID, and for the ID of Report27. The junction table may contain huge numbers of "duplicate" cat/mouse/dog entries, because every Report that refers to a Cat must have a junction-table entry containing the Cat ID (but each will have a different ReportID). Junction tables tend to be long and thin (they don't need many fields, but they have lots of entries).

Submitting a new report means creating one new report entry, as many new word entries as there are NEW words, and as many junction table entries as there are words in total (excluding common words "and" etc., that you've deliberately chosen to ignore; again, you are probably dealing with these by having a 4th table of unwanted words and querying it).

The junction-table situation may upset you if you're worried about duplication, but this sort of duplication isn't bad. Firstly you're storing minimal information, so it doesn't make the database big. Secondly, if you ever decide to redefine a key-word ("Cat" becomes "Feline"), you don't need to change every instance in every report.

You can, of course, use the word itself as a unique index (as PHV suggested), but so far as I'm aware that won't actually get you out of all your problem. If you're doing everything with unbound forms and vba, I think it just means you'll get an error when you try to create a new entry with the same word "Cat", and you'll have to handle the error (which is itself a perfectly valid approach). If you do go this way, I would still pre-process words a bit to avoid having "cat" and "Cat" and "CAT".

 

[Domino2]

Many thanks. I carried on with what I thought was okay and so far seems logical.

I did not want to have multiple words stored, and yes I am doing everything in vba. I am checking tables every time I add a keyword in the table etc. I have adopted a fourth table outside of the 3 linked tables. This table holds just one copy of each keyword, and I use it's primary key to generate a foreign key value in my 3rd linked table. After this is entered in table 3, I put in the primary key value into the junction table (Table 1 and Table 3 Primaries) Just as an added help to start with I am also putting in the Keywords unique number in the junction table.

Hope you can follow this. Just wish Tek Tips allowed easy picture posting, as I do not have a web URL.

Regards and thanks