Results 1 to 9 of 9
  1. #1
    gxpark
    Guest

    Relationships - I'm very challenged to do this...

    Hi,

    I'm trying to create a relational database for a register of material/tools/etc. check ins/check outs...

    I have the following signigicant tables:

    Inventory
    Register

    How do I create the correct relationship so each row in "Register" can point to multiple rows in "Inventory", and viceversa (or at least the first one)? Both relationships must be reciprocal, I mean, that if there are two given rows from Inventory in one row in Register, those two rows in Inventory should point to the former row in Register.

    Searching through MSKB, this site, and Access help, I found that this is called a many to many relationship, but no matter how much carefully I read the directions, I can't make it work...

    Please help!

    Thanks in advance.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships - I'm very challenged to do this...

    I'm sure other Loungers will have more to say on this, but I'll jump in with the first, brief comment.

    In order for a Many-To-Many relationship to work in Access, you must use a third table that has two one to many relationships to the other two respective tables. For instance, in a school setting one might have two tables: for Students and Classes. Then the Many-to-Many part would come from a third table such as ClassesTaken. This table would have the StudentID and the ClassID of each student taking a particular class. There would be Many Students in taking one class, and each student would be taking Many Classes.

    I'm not exactly sure how to apply this to your situation because I don't know enough about what kind of information you're dealing with. However, I hope this is enough to get you started.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships - I'm very challenged to do this...

    More information is needed. You talk about material and tools and yet your tables are inventory and register. What's the connection?
    Many to many relationships are resolved exactly as MarkJ has illustrated but more specific information would yield a better solution for your problem

  4. #4
    gxpark
    Guest

    Re: Relationships - I'm very challenged to do this...

    Ok, sorry I'm not being very clear, but it's kinda hard for me to speak in english, and even more to try to explain something technical...

    Ok, we have an inventory of the tools and materials we use (hammers, screw drivers, screws, paint, etc.), so we want to have a database that will have those elements registered, along with the stock available.

    Now, the Register table (it's actually called "Remisiones", but I don't know how to say that in english). Each record in this table represents an action taken on some items by some employee. For example, a given employee would want to use a brush and a can of paint, so the record would have registered the employee, the date, and the items taken. The record would be marked as cleared or something when the employee returns the items used (only the brush in this case).

    We also wish that each item in the inventory "knows" who is using it (and viceversa). And I guess I could track the available items by code, increasing or decreasing the quantity as needed.

    I hope that's clear enough :-)

    Thanks for the help!

    BTW, the way MarkJ illustrates (thanks also!) is kinda the same I've been reading elsewhere, and I'm having a lot of trouble understanding it...

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships - I'm very challenged to do this...

    Ok So you should have 3 tables: Inventory, Employee and Register. The Inventory table holds details of the stock items (ID, Description etc), the Employee table has ID, Name, Position, Department and stuff like that. The Register table records the logging out and in of inventory items by employees so will include Inventory ID, Employee ID, Date/Time Out, Date/Time Returned.
    The many to many relationship is actually between the Inventory and the Employee (An inventory item is used by many employees, an employee uses many inventory items). The Register is your link table. It has a many to one relationship with both Inventory and Employee so an Inventory item can be logged out and in many times but each time by only one employee. Similarly, each employee can log out many inventory items but each log out can only be to one employee (you can't have two people using the brush at the same time). So you should have three tables (Inventory, Register and Employee) and the one-to-many relationships are Inventory/Register and Employee/Register.
    I sincerely hope that helps. You could also benefit from reading info about normalisation of data structures. I do have a couple of Knowledge Base documents on this if you think it might be helpful.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships - I'm very challenged to do this...

    I also have a book that I find very useful (although I probably haven't studied it as much as I should! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    It is Access Database Design and Programming by Steven Roman, published by O'Reilly (It has a tamandua on the front cover - funny, it looks like an anteater to me, but then I never was good at animal recognition!).

    This book has quite a nice description of many to many relationships and how to deal with them in Access.

    Kiwi44

  7. #7
    gxpark
    Guest

    Re: Relationships - I'm very challenged to do this...

    thanks for the info...

    I thought I undertood it clearly, but somehow I can't make it work... can you send me the kb documents?

    thanks!

  8. #8
    gxpark
    Guest

    Re: Relationships - I'm very challenged to do this...

    thanks for the info. I'll take a look at it

  9. #9
    gxpark
    Guest

    Re: Relationships - I'm very challenged to do this...

    Good news!

    Experimenting with your explanation, I finally accomplished it!

    Thanks a lot.

Posting Permissions

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