Results 1 to 4 of 4
  1. #1
    robbfamily is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Location
    Philadelphia, Pa
    Posts
    6

    Do I have too many tables for what I'm trying to do?

    I'm attempting a 'Document Translation' managment database.

    I've attached a file that contains only my tables because I don't know if what i'm starting with will give me the solution that i'm hoping to end up with.

    I've related those tables in order to create a functional system for building a database of document translations. my responsibilities include. managing the docuements that we have, managing the corresponding translations of that document, the versions of each origional/translation. logging document translation requests, ordering from verndor translation companies, and sending translated documents copies to departments who sent the original request, in our network, via email (or might develop an intranet depository/folder for all translations that we have to-date).

    so, a typical process will be: (dept & document names provided for example only)

    - We get a request from RADIOLOGY for a cambodian translation of a Pre-Procedure Preparation document.
    - I search the database to see if we already have that form translated, and which languages it was translated into already.

    - If we have it, I'll attach that request to original English document that we already have in the document table, log date time, etc., and forward the requested language translated document to that department by email. At that point, the only thing thas changed in the database is a new request for the existing original English document was logged (would already be in the Document Table) and the translation sent to fulfill the request was logged.

    - If we do not have the original document yet, it becomes a new Record entry into the document table. the translation request from the department will have to be related to this new document record. A new language will have to be identified if it is not already (i have most possible lanuages in a vlookup in the language field).

    - I would then have to send a request to the translation company for a quote on the translation. Both the request and the quote will have to be related to the original document

    - When an acceptable quote returns, I'll attach that quote/invoice to the specific language that is being requested, which should already be related to the original document. Payment should be tracked and related to this translation request, but the PO # is generated by the business office.

    etc....

    you see everything related to this translation managment database logs, attaches to, tracks, or relates to, in some way, to the original English document that needs to be translated to a foreign language.

    MY PROBLEM/QUESTION is, whether i need to have every part in a separate table, as I do now, and how to relate those properly, and how to create queries, forms and reports based on the information in these tables.



    I'm tackling a large project for knowing as little as I do about the process.

    Thank you for any help that someone can give. I've had paid help with some excel projects, but i didn't see a "commercial" option here in the access forum.

    David

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    VLookup is Excel function, Access has DLookup.

    This is a quite specialized db application. There are document management dbs (electronic file cabinet). Search web. However, they probably have a purchase cost and still might not be right for your requirements.

    Do you need to maintain record for each translation request? Not seeing a table for that.

    What are Room and Version tables for?

    You did not set up Relationships. I am not sure what table is related to other tables. For instance, is LangFormat field in Language supposed to store the primary key from Format table? Problem here is FormatID in Format table is set as primary key and is an autonumber, however, LangFormat is text field. Since the primary key should be saved as foreign key and the fields must be same type (in this case numeric), these tables cannot be correctly related.

    AttachementID field name in Attachment table is misspelled.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    robbfamily is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Location
    Philadelphia, Pa
    Posts
    6

    Re: Do I have too many tables for what I'm trying to do?

    June7,

    Thanks for the quick reply, and identifying problems.

    I may have attached wrong version of the db. I'll check and upload correct one with relationships.

    one department has many locations. each location has many rooms. A document might relate to only one specific room of a location/dept, even though that dept is spread out over several locations/campus.

    The format, I intended to help track which version of an original form is the most current, and which version was used for a specific translation.

    I need a lot of help getting this right, if you're willing, you'll be an angel.

    David

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I'll offer what I can.

    Re: PK/FK

    Alternative is to use the text Format field as primary key and leave the related foreign key field as text type.

    There a benefits/drawbacks to each approach.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  2. Replies: 5
    Last Post: 11-26-2013, 11:11 PM
  3. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  4. Replies: 10
    Last Post: 07-12-2011, 11:09 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums