Results 1 to 8 of 8
  1. #1
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12

    Relationship problem with Tables

    Hi,
    I'm very new here, and I apologize if I'm not in the correct area. Here is my dilemma. I have 4 tables with the following fields.



    tblOfficerMain
    OfficerRecordID - (primary key)

    tblMainInventory
    KitID - (primary key)

    tblOfficerInventory
    KitID - (same as KitID in tblMainInventory)
    RecordID (AutoNumber) - (primary key)

    tblKitIssuedtoOfficer
    OfficerRecordID - Same as OfficerRecordID in tblOfficerMain (Primary Key)
    RecordID -Same as RecordID in tblOfficerInventory (Primary Key)
    KitID - Same as KitID in tblOfficerInventory

    I have relationships set up for the following:

    tblOfficerMain.OfficerRecordID to tblKitIssuedtoOfficer.OfficerRecordID (1 to many)

    tblMainInventory.KitID to tblOfficerInventory.KitID (1 to many)

    tblOfficerInventory.RecordID to tblKitIssuedtoOfficer.RecordID (1 to many)

    tblMainInventory.KitID to tblKitIssuedtoOfficer.KitID (1 to many)


    My first problem is that I need to create a relationship between tblOfficerInventory.KitID and tblKitIssuedtoOfficer.KitID. It won't allow me to do this because there is already a relationship between these 2 tables (using RecordID).
    Since tblMainInventory.KitID is the same as tblOfficerInventory.KitID, is it possible to assign the value required for tblOfficerInventory.KitID by using tblMainInventory.KitID?

    Secondly,I have a main form with a subform. The main form is using tblOfficerMain as the RecordSource. The RecordSource that I am using for the subform is:
    SELECT DISTINCTROW [tblKitIssuedtoOfficer].[OfficerRecordID], [tblKitIssuedtoOfficer].[RecordID],[tblKitIssuedtoOfficer].[KitID],[tblKitIssuedtoOfficer].[OfficerKitIssued], [tblKitIssuedtoOfficer].[OfficerQuantityIssued], [tblKitIssuedtoOfficer].[Deducted], [tblKitIssuedtoOfficer].[KitReturned], [tblOfficerInventory].[KitID],[tblOfficerInventory].[Item],[tblOfficerInventory].[Quantity] , [tblMainInventory].[KitID] FROM tblOfficerInventory, tblMainInventory INNER JOIN tblKitIssuedtoOfficer ON tblOfficerInventory.RecordID=tblKitIssuedtoOfficer .RecordID;

    This subform was working somewhat, but it kept telling me that a field was required from tblOfficerInventory. Now, the subform is a blank screen.

    Also, there is a combo box on the subform. Its RowSource is:
    SELECT DISTINCTROW [tblOfficerInventory].[KitID], [tblOfficerInventory].[Item] FROM tblOfficerInventory ORDER BY [Item] ASC;

    I really need to get this working, so any help would be greatly apreciated.

    Thank you
    comteck

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't understand the need for tblOfficerInventory.

    Please clarify relationship between officers and kits.
    Each officer can have many kits and each kitID can be assigned to only one officer?
    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
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    I am posting screenshots of all 4 tables, plus the relationship screen. Hopefully, this will clarify things a little more.


    On the screenshot, the tblKitIssuedtoOfficer will have a different name. It is tblKitIssuedtoOfficerwithExt.

    I apologize. I am trying to post some scrrenshots of all 4 tables plus the relationship screen. but the forum is telling me that I have exceeded my limit. So, I am sending this in 3 posts. The first file is attached to this post.


    Thanks again.
    comteck

  4. #4
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    Here is post number 2 with the some more screenshots.

  5. #5
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    And the 3rd file with screenshots.

    Thanks again.

  6. #6
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    OK...All 3 files seem to have uploaded. I hope this helps clarify things. Thanks again for any help on this.

    Thanks
    comteck

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why make me analyze your table properties and following that relationship diagram makes me cross-eyed. Can you simply answer my question about relationship?

    Also, could have made the 3 doc files one doc and if large, zip. Zip of 2mb allowed.
    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.

  8. #8
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    Thanks, but I figured it out.

    comteck

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

Similar Threads

  1. One to many relationship problem
    By Catherine in forum Access
    Replies: 3
    Last Post: 12-27-2011, 04:08 AM
  2. Relationship between tables
    By kpk in forum Database Design
    Replies: 3
    Last Post: 10-14-2011, 11:49 AM
  3. Relationship Problem?
    By j3lena in forum Database Design
    Replies: 1
    Last Post: 01-14-2011, 05:27 PM
  4. Relationship Problem
    By hawzmolly in forum Database Design
    Replies: 4
    Last Post: 07-18-2009, 05:39 PM
  5. Relationship problem?
    By amangill1984 in forum Access
    Replies: 0
    Last Post: 03-04-2009, 08:57 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