Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Broken Relationships (2003)

    My document control database has a table for names of my colleagues (TabSabco(constructed prior to me knowing about naming conventions!)) and a table for the client's personnel (TabNwr). I have just added a new table (TblTransmittal) to base a report on to print and issue as a document transmittal.

    My problem is that the list of names in TabSabco is used in a Document Owner field but now when a Transmittal is produced I need a list of names for the Transmittal Author but the Document Owner and the Transmittal Author are not necessarily the same person. So I have added another table (TblTransmittalIssuer) which is a copy of TabSabco.

    The structure is also wrong in that the table TblTransmittal needs to be on the many side of a relationship with TblHistory not TabDocDetails. But Iíll fix that tomorrow!

    Is there a way I can structure the table relationships in order to list all individuals in one table? By using the current structure if a new Project Manager joined the team I would need to add his details to two separate tables. This canít be right.

    Any pointers would be great.

    Regards

    Graeme
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Broken Relationships (2003)

    You can add the same table TabSabco to the relationships window twice; the second instance will be named TabSabco_1 but it will be exactly the same table. You can create a different relationship for each of the instances. Again, they will be relationhips for the same table, but showing two or more instances is the way Access uses to visualize different relationships between two tables.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Broken Relationships (2003)

    Second TabSabco table added. Thanks for that. However, sorting out the relationships with the Transmittal table is more complicated than I expected.

    I have a form (FrmTransmittalSelect) and subform based on a query (QryTransmittalSelect )which lists all the documents for each site. There is a check box against each document rev number to select that version of the document with the intention of displaying the information on a transmittal using a Report based on another query (QryTransmittal)

    The Transmittal record does not exist until it has been created by entering a TransmittalAction in TblTransmittal using a text box on SubFrmTransmittalSelect but I need to display all documents and revs in order to select the ones required for the transmittal.

    How do I include the TblTransmittal in my Query (QryTransmittal) but ignore the fact that there is no data in order to display all records and a blank TransmittalAction Text Box on my subform? I have tried using Is Null in the criteria but to no avail.

    Thanks

    Regards

    Graeme
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Broken Relationships (2003)

    You have added a second instance of TabSabCo to the Relationships window, but you have joined both instances to TabDocDetails on the same fields. That is useless, you should use different fields in TabDocDetails: one field for the document owner and another for the transmittal author.

    I don't understand QryTransmittal - it is based on QryTransmittalSelect and on TabHistory but TabHistory is already used in QryTransmittalSelect. That makes no sense to me, sorry.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Broken Relationships (2003)

    Hans

    There is a possibility that not working with Access for a while has left me forgetting more than I ever knew in the first place!

    QryTransmittalSelect is used to list all the available documents on FrmTransmittalSelect. Once a couple of documents have been selected using the checkbox, QryTransmittal is used to list only the selected documents. The plan was to then base a Report on QryTransmittal to print and use as the actual transmittal to issue to the client. The inclusion of TblHistory was just a result of my experimenting.

    Iíll go away and have a re-think.

    Thanks

    Regards

    Graeme

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •