AnsweredAssumed Answered

Database challenges

Question asked by storywizard on Apr 13, 2009


Database challenges


<!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:Verdana;      panose-1:2 11 6 4 3 5 4 4 2 4;      mso-font-charset:0;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:536871559 0 0 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:Verdana;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-bidi-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->

I am building a database that tracks the production of alternate format texts from a paper copy of a book; from receiving the book to the point that the student receives the completed text. The database has to document who worked at which task, how long the task took, start and completion dates. The file must also track which student each format of the book is for, sometimes there is more than one student per format of the text; and what extra cost a particular task may incur, such as getting a book sliced up so that we can scan the pages.


My initial plan was for a Books table linked to Clients, Personnel, Tasks and Expenses. After some professional help it was suggested to me that I needed a couple of join tables so that I could have more than one format of the text and more than one client for each book.


I now have two join tables: BooksFormat and BooksClients. There is a portal on the Books table that has BooksFormat on it, where the format/client link is made. Now each text has is own ID number and is linked to a particular client.


On the BooksFormat layout I have another portal from the Tasks table that shows Task name, Personnel name, Status, Due date, Start date, Completion date, Pages narrated(for audio books), Pages proofread (for Etext) and a column for time taken on each task.


I have two issues: on the portal in the BooksFormat table I can find no way of entering the whole personnel name in its column. I can get first names but have had no luck with any calculation field or field merging techniques.


The second challenge which I have not tried yet is to put two clients on the same record for a format. I suppose I could go simple and just put two or more fields on the BooksFormat layout for client 1, 2, 3 etc...but that seems to defeat the purpose of the BooksClient join table.


I have attached a clone of the database to this message, sorry it is so long, but I am trying to give as much detail as I can.The clone only has a few Personnel, the real database has 300+. I tried using a field as a value list, could not get it to work...


Thanks for any suggestions...