Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: One-to-Many Relationships Issue

    PROBLEM: The "one-to-many" relationships should be reverse. The "1" should be on the table where the "infinite" is.

    See the attached thumbnail to see how my relationships are currently configured.

    DETAILS:

    The SITE INFO table contains information pertaining to our client's accounts.

    The LABOR and MATERIALS tables keeps an unlimited number of invoice details from each client.

    The LABOR, MATERIALS, and the other tables should be reversed.
    Attached Thumbnails Attached Thumbnails Relationships.JPG  

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    When you define relationships, the table with the PrimaryKey is the one side of the relationship and the table with the other table's PK as a ForeignKey becomes the many side of the relationship.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    May 2009
    Posts
    17
    My site info table represents all of our client base. Our clients can have many labor and material charges, so the "one" should be on the "Site info" table, but it is the opposite. It seems that access can't differntiate the difference between my primary and foreign keys. They need to be swapped. Any ideas?

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Why do each of the other tables (Labor, Materials, etc) have a FK into the Site Info table?

    I think that is where the problem is.?!?

    C

  5. #5
    Join Date
    May 2009
    Posts
    17
    Oh. I am still learning access. I thought you need to have identical fields to link the tables together. Each table has its own PK. How would I link the Site info table together with the others and have the one to many relationship that I am looking for?

    Each site (client) can have many labor and material.

  6. #6
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Dave--

    You caught me on that one...Try linking the SiteID field from LABOR to the SiteID field from SITE INFO. Then remove the relationship of LABORID to LABORID.

    I think that is what you are looking for.

    C

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The fault isn't with Access, its with the way you have desinged your tables
    specifically you have't normalised your data

    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design

    waht you are looking for is whats called an intersection table
    it contains the primary key of other tables to contain details of antyhign relevant to that intersection. to have multiple chemical treatments for one site you would have a site table, a chemical table and a table that records the chemical used on a site.

    you have normalisation issues in as much as you have 3 prices fro chemicals
    you have 3 chemiclas in your chemical table per row.

    I would expect in thsi sort of desing you would have, say
    a customer table containing details of the customer (eg invoice address, contact details and so on)
    a customer site table (identifies the customer , site address, and anything else specific to that site (eg local site contact)

    I'd expect a job table which identifies what jobs have been done on a specific site (it could also include forward jobs (eg jobs to done in the future)

    hanging off that job table I'd expect intersection tables for chemcials, labour etc...

    that brings to the next question, what is there that is different between say chemicals and materials, are the differences significant enough to merit two distinct tables, or is an alternative design to have a materials table, and a sub table(s) below that identifying what makes say a chemical distinct to an other material
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2009
    Posts
    17
    I tried removing the foreign keys from the SITE INFO table and then linking the SITE INFO table to the other tables using "SiteID" as a primary and foreign keys with the other tables, but they did not work.

    Here is a little more details about why I am creating this database to begin with. I work for a company that has approximately 1,300 employees. We are a non-profit organization. During recent years we have experienced a lot of growth and are experiencing lots of "growing pains." Currently, my company has 5 major departments, each with their own system. When I started, we had no real system. The manager before kept records on Excel (fairly shoddy ones at that)! We have no reporting system at all, and have no real means of tracking much of anything.

    My expectations for this database:

    My department does not actually invoice our clients for the work that we do in our department. We have a AR/AP department for that. Our department is required to instruct the AR/AP department on what to bill our clients, the amounts, and other details. They do the billing. So, the intent is not to have a complete billing database, but to maintain records of the work we do with each account. I need to keep my own records of the work we have done and then report the details.

    Here is an overview of what I intend each table to represent:

    SITE INFO - Maintain basic information about the various accounts in our
    department (i.e. contact information).

    LABOR - Maintains records of billable labor for each client in SITE INFO table.
    Each account (client) can have many labor charges.

    MATERIALS - We bill our clients cost of materials used plus markup. Each client (account) can have many material charges.

    JOB CODES - A list of codes used to identify each task performed.

    CHEMICAL - A record of pesticide applications performed at any given account listed in the SITE INFO table.

    PESTICIDE APPLICATORS - A list of employees (and pertinent info) who perform chemical applications on sites.

    PROPOSALS - Maintains records of proposals given to each client listed in the SITE INFO table.

  9. #9
    Join Date
    May 2009
    Posts
    17
    Here is an error message I received when I attempt to add a "one-to-many" relationship with the other tables (see thumbnail).
    Attached Thumbnails Attached Thumbnails Error Message.JPG  

  10. #10
    Join Date
    Mar 2007
    Posts
    277
    How many records are in each table. In order to enforce RI, the FK in the Child table *must* be present in the Parent table. You can have Parents without children but no children without parents.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Dave0620
    Here is an error message I received when I attempt to add a "one-to-many" relationship with the other tables (see thumbnail).
    Hi Dave0620,

    Someone correct me if I am wrong, but from looking at your Table Relationships visually your Relationships are showing as they should. By this I mean that the ParentTable of the PrimaryKey is supposed to be on the One side in that table, and should show as the Infinite (ForeignKey) in the other table. Like in the LaborTable, you have LaborID and it is set as the PK as it should be as that is it's parent table or table or origin. Then in the Site Info table it should be the Infinite (FK) just as is shows. In TableDesign you should have them set correct for it to show as it does...meaning LaborID in the LaborTable should be set as Indexed Yes - No duplicates, then LaborID in the SiteInfo table should be Indexed Yes - Duplicates OK. Without seeing all of the fine detailed structure of your database it's not totally easy to figure out what is wrong, but seems to suffer from Normalisation problems are mentioned earlier. Please check out the links given early and try to get to understand it really good as that is the foundation of making a good, workable database that functions as it should. Not sure what else to tell you at this stage, and with me being at work can't really dive into it any further. Listen to Healdem, he is a pro at this kinda stuff. Remember, when all else fails, ALWAYS GO BACK TO SQUARE ONE. Review it and make sure it makes Logical sense to you.

    Have a nice one,
    BUD

  12. #12
    Join Date
    May 2009
    Posts
    17
    Quote Originally Posted by RuralGuy
    How many records are in each table. In order to enforce RI, the FK in the Child table *must* be present in the Parent table. You can have Parents without children but no children without parents.
    I figured out how to create one-to many relationships with most of my tables, with the exception of one. I get an error message with it (as shown in a prior message thumbnail). I am not sure how to fix the error yet, but am working on that.

    I have attached a JPEG to show how my relationships look right now. I still need to link LABOR together with SITE INFO and make sure my tables are appropriately joined, before I go further and create forms, queries, etc.
    Attached Thumbnails Attached Thumbnails untitled.JPG  
    Last edited by Dave0620; 05-04-09 at 15:26.

  13. #13
    Join Date
    May 2009
    Posts
    17
    If someone would like to check more under the hood, here is a link to my database (with data removed). I will have to redo some of my forms totally (jumped a little ahead of myself). Forms are the easier part.

    Here is a link to download it:

    http://dl.getdropbox.com/u/800232/DATABASE%205-6-09.mdb

    I am new to this company and am kind of thrown into the fire and under pressure to get something created that will report effectively and accurately. When I accepted the offer for this position as a manager of the department, I was told that reporting was lacking, but wasn't told that it was NON-EXISTANT!

    Thanks for the help so far from everyone. I am looking into all of the advice given to see what I need to do.

  14. #14
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    I'd look at changing your DB design a little bit. Obviously feel free to change table names to suit your needs, but you definitely have to split the clients from the site visits as you'll have to duplicate the client information for every visit you do to their site.

    tblClient: This table would store the contact information for each client you want to track. 1 client can have many visits, but each visit can only be to 1 client.

    tblSiteVisit: This table would store each billable visit per client. Here you would put your job information which would include the materials, chemicals, labor information etc... that are specific to that visit. Now some of this you'll know better than we will so you'll have to expand where appropriate.

    When it comes to your materials and stuff you use on the job, this is where it gets more complex and is based on your work situation. In your materials table, you are storing mathematical equations. That is not a good thing. You really should be using a query to calculate that information. For one, it breaks normalization rules, and 2, anytime something changes, you need a lot of code to re-run the math (i.e. you'd have to write a afterupdate event to recalc the price for every text box that could affect your pricing).

    If you also want to track vendors for product costing, then you're going to need some additional tables (i.e. if you can buy chemical A from Vendor X or Vendor Y), you'll need a table that joins the chemicals to vendors (something like tblChemicals2Vendors) in a many-to-many relationship, which would be something like the following:

    tblChemicals2Vendors
    lngChemicalID (primary key)
    lngVendorID (primary key)

    hopefully that will get you started in a better direction.

  15. #15
    Join Date
    May 2009
    Posts
    17
    Quote Originally Posted by rendrag
    ...you definitely have to split the clients from the site visits as you'll have to duplicate the client information for every visit you do to their site.

    tblClient: This table would store the contact information for each client you want to track. 1 client can have many visits, but each visit can only be to 1 client.

    tblSiteVisit: This table would store each billable visit per client. Here you would put your job information which would include the materials, chemicals, labor information etc... that are specific to that visit. Now some of this you'll know better than we will so you'll have to expand where appropriate.
    In my database, I thought I have the site information (SITE INFO) in one table and MATERIALS and LABOR associated with those sites separate and in other tables. Are you saying this would be incorrect or should be changed? Are you saying that I should combine LABOR and MATERIALS together?

    Am I understanding you correctly? Should it be as follows? Are my relationships correct?

    tblClient = SITE INFO table
    tblSiteVisit = LABOR and MATERIALS tables (combined)

    I am sorry if I missed something. I have been working a ton of hours with my job and trying to unbury myself from my company's lack of reporting tools. Another user posted some material, but I have only been chipping away at it since I have been so busy.

    Here is what I have so far:
    http://dl.getdropbox.com/u/800232/PHC%205-6-09.mdb
    Last edited by Dave0620; 05-05-09 at 11:58.

Posting Permissions

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