Normalization Problem???????

[Gilrucht]

I have two forms bound to different tables. I will use an example to illustrate my problem.
I have 4 people who have each sent me 5 documents. I do not currently know the identity of these 4 people so I can not create a lookup table. In the future they will continue to send me docuements. In addition, other people, currently unknown, will send me documents.
My first form records the author and the second form records the document info. I have 3 problems. The first is setting up a procedure to make sure the authors name does not already exist. The second is creating a link between the forms so that the authors name is linked to the document the first time. (If you have read any of my other posts you know this is part of law office db and everything is already linked by client and case so this would be a third link for these two forms. All 3 links are necessary here because the same authors may send docuements on more than one case). And my third problem is setting up some sort of query or lookup procedure so that I can link the author to the docuement. I cant use the author as a lookup table because I dont know the authors ahead of time and there will constantly be additions to the authors. Its complicated bythe fact that I need to check each docuement to see if its written by an existing author or new author. I'm almost tempted to combine the tables and use one form but that would be serious violation of normalization. Some Authors will be sending in excess of 50 documents. Can anyone help me. I am completly at a loss as to how to do this.

 

[pafcpilgrim]

Solution to Problem 1:

Create an onchange even that uses a DLookup to check the entry you have enterred to see if it exists then prompt where necessary.
Or leave it blank and set the field property "Required" to Yes(NoDuplicates) then you will get an error when trying to save anyway if the Author already exists.

Solution to Problem 2 & 3:

You will have to assign each document you link to with the Authors's name or ID or else you will lose track of everything. What i would do is create a form to add new Authors.
Then create a second form that you open to add documents, select the Author from a drop down list and the document details, then create a third form which you can select an Author from a drop down box get it to re-query a list box which will then display all their documents and then double click the list box to open a form and display the documents details.

Anymore help or questions just ask.

 

[Gilrucht]

Thank you for your reply. I have been studying it and and mapping it out on paper on paper the past few days. I think I have come up with an answer that is not quite what you suggested but is based on your concept. First, I should put my example in context. I used the above example so the issue would be clear. The "authors" in the example are Doctors. The "docuements" are the clients medical records. A new client gives us a list of Doctors he is seeing for his injuries. These Doctors are recorded in the doctors form. This occurs during the initial interview. The medical records are not obtained and recorded until months later.

I realized I hadn't framed the issues correctly and once I did the solutions became obvious. The real issue is that this table only records the identity of the Drs the client is seeing so it really is a lookup table. My thought is to populate the DrInfo table now with known Drs who treat people injured in auto accidents in our geographic area-knee specialists,back,neurosurgeons,chiropractors, etc. It is a finite number. I set the the popup property to yes. I then create a second table and form linked to my client and case . This form is based on a query that looks up the dr information in the first table, which is now a lookup table. The not in list event is coded to pop up the dr info form and requery the combo box after the drs info for the new dr is entered. The advantage to this solution is that the drs info is only being entered once no matter how many clients are seeing the same Dr. The only thing being recorded in the second table is the dr id whereas now all of the Doctors Info is being recorded in the Doctors table each time a different client sees the same Doctor.

With regard to the client's medical records the issue and solution is pretty much the same. I use a combobox that looks up the drs info in the dr lookup table and records the drid. If the doctor is not in list the same code to popup the dr, info form then requery the combobox after the info on the new dr is entered.

Will this accomplish my purpose and am I correct that this form of normalization possible?