One Time Qry Needed

[Navyguy]

One Time Qry Needed.

I am attempting to further normalize a table that I have. Previously I had a check box that indicated that a document was received. As the years go by now, we have started collecting multiple documents and they are all indicated by a check box. I want to move the check boxes out of my main table and into a table called documents (Tbl-Documents).

I wanted to use an append qry to do this, but when doing the selection, the results are wrong. I tried doing a select qry for each of the documents and then putting them into the append qry with the same results.

To keep the question as clear as possible, I will just use some generic terms

Tbl-People
PK-PeopleID
Document1 (y/n)
Document2 (y/n)
Document3 (y/n)

Plus a number of other documents

* It is possible for people to have any combination of the documents checked as yes and not every record will have at least one of them checked as yes.

Want to take the above (y/n) from Tbl-People and put them into Tbl-Documents.

Tbl-Documents
FK-PeopleID
PK-DocumentID (Auto)
Document1 (y/n)
Document2 (y/n)
Document3 (y/n)

Plus a number of Documents more

Once the append qry is completed I will amend my other qrys to point to the right Tbls and delete these fields from Tbl-People.

If this was not originally set-up this way, I think I would have had a Tbl-Documents (1 to many) with Tbl-People and “named” the documents in a field called DocumentsName or something like that, but there is too many records to rename I think at this point.

As always, all help is appreciated, and please feel free to criticize my approach to this situation.

Navyguy

 

[mresann]

Tbl-People
PK-PeopleID
Document1 (y/n)
Document2 (y/n)
Document3 (y/n)


Tbl-Documents
FK-PeopleID
PK-DocumentID (Auto)
Document1 (y/n)
Document2 (y/n)
Document3 (y/n)

You have a problem with repeating documents in fields. Basically, with your system, you have to re-design the table to add the new documents, which is bad normalization process. Since you will add new people, as well as new documents on a recurring basis, you need to create a structure that allows you to enter the new data without affecting your table design. So let's look at how to proceed.

First, I would suggest divorcing all document information from Tbl-People. That table should only have information related to the person, such as first names, last names, addresses, etc. Of course, it should have a primary key that is unique to that person as you lready have it, so we will use your format and call it PeopleID.

Next, I would revise your Tbl_Document table to just include the following:

DocumentID (PK)
DocumentName

So far, so good. Now to create the Join table, and call it Tbl-People_Doc_JOIN. Next, create the three fields as follows:

PeopleID (FK to Tbl_People)
DocumentID (FK to Tbl_Document)
DocReadStatus (yes/no field)

Since this is a join table, you shouldn't make a single-field primary key. What you SHOULD do is to make a Primary key COMBINATION. The co-Primary keys for the Tbl-People_Doc_JOIN table are the PeopleID and the DocumentID fields. This means you can have more than one record with the same People, as well as more than one record with the DocumentID, BUT you CANNOT have more than one record with BOTH the PeopleID and DocumentID matching.

For instance, the following records would be allowed (please allow for formatting):

PeopleID DocumentID DocReadStatus
1 1 Y
1 2 N
2 1 N
2 2 N
2 3 Y
2 4 Y

However, this would NOT be allowed:

PeopleID DocumentID DocReadStatus
1 1 Y
1 1 N
2 1 N
2 2 N
2 2 Y
2 4 Y

As you can see above, the records contain duplicate PeopleID and DocumentID fields in the first and second rows, as well as the fourth and fifth rows.

Now you can add unlimited documents and unlimited names. This will allow you to do the following:

When you append (add) a new name to the table, get the ID number that is created. Determine which of the documents you wish to 'assign' to the new person. Append the JOIN table with the new PeopleID number with the DocumentID number of the assigned documents for that person.

When you create a new document, you will have to append a new document to the Document table. Once you append the new document, you will have to determine which of the people (any or all) will need to read the document. Append the JOIN table with the new DocumentID number with the PeopleID number of the assigned people for that document.

When a person reads a document, you need to UPDATE the join table. Use an update query to check the Y/N box on the join table according to the PeopleID and DocumentID. No other actions need to be taken.

HTH

 

[Pat Hartman]

You need to review your business rules. The rote conversion of your existing data will add rows for N as well as for Y. Normally tables of this type are sparse. That means that they would not contain the No rows. They would only contain the Yes rows. So, presence means yes, absence means no. That makes the Y/N flag redundant. I would replace it with a ReceivedDate field. This will provide more information. For the conversion, decide on a date and create records for all Yes values and assign the same conversion date to them. Modify your form so that it saves a date for new records.

 

[Navyguy]

Thank you both so far. Both have made very valid comments. It is certain that I will over the years need to add additional documents, so mresann, your point regarding table structure is well taken. Like I said, I am trying to work with what was created without trying to do a wholesale change.

Pat, I certainly would like the fields to have more purpose, but currently all that is needed is whether the document is received or not. I understand about having fields with redundant data in it (ie y/n), but not sure the best way around that either.

As far a reports go, all I need to know is whether the document was submitted or not. But as you accurately pointed out, as the document list grows so does the amount of space that is not required for documents that are not required for all people.

I think perhaps I will create a similar table like I have for qualifications, and use an entry field on the form with a dropdown to indicate that a named document was received (one to many with Tbl-People). In my original post, I was looking for a simple answer to extract the positives (yes checkmarks) so I could get that information out of the Tbl-People and into a Tbl-Documents.

Thank you both for your comments, I think you both convinced me that I should go about doing it right and bite the bullet and take the time to sort through all the records as necessary.

Cheers