Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: Problem with relationships between tables

    Hey all

    Thanks for giving folks like me an avenue to seek assistance when we try to do more than our limited abilities allow. My issue is hopefully a simple one that someone can assist me with.

    I have created an Access 2007 database. The database will be used only by my with the hopes of making life a bit easier. What I am doing is trying to store client credit check data. Client could be either individuals or businesses. My database is set up this way.

    A client table to store data for individuals [Social security (primary key)name, address]

    A business table [Business Name (primary key), directors, lead person]

    The 2 aforementioned tables store information about the person/business the credit check is being performed on.

    The results from the credit check will be stored in a separate table called Bank. The Bank table contains the SOCIAL field from the Customer table and Business Name fieldfrom the business table. I am thinking this will allow the records to be properly linked. I query several banks for credit check data. So each client/business will have multiple bank records. Credit check data from client and business are identical so I thought it would make sense to store all that data in one table.

    The PROBLEM.
    Data goes in fine. When generating a report, however, ALL the information from the BANK table is shown. It does not matter for which client/business that information was originally entered. I do not have a primary key in the Bank table. I have set up the relationships. There is a one-many relationship between the Social field in Customertable and Social Field in the Bank table and a one-many relationship between the Business Name field in the Business Table and Business Name field in the Bank table. When entering data in the datasheet view the little plus sign to expand connected tables works fine. So I am really unsure where the screw up is.

    This is the pertinent issue I am facing. There are others of course

    Thanks a million for you help.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your problem here is that you have two tables containing records relating to each Bank record, but only one of them will be used per record. This is a bad design.

    You need to change the primary keys of your Customer and Business tables to be incrementing numbers. Then change the Bank table to remove references to the other tables, and give it a primary key as above. Finally, create an intersection table that holds the following:
    • BCID (Integer)
    • BusCust (single character text)
    • BankID (Integer)

    This table will then handle the links between the other three.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2012
    Posts
    7
    Quote Originally Posted by weejas View Post
    Your problem here is that you have two tables containing records relating to each Bank record, but only one of them will be used per record. This is a bad design.

    You need to change the primary keys of your Customer and Business tables to be incrementing numbers. Then change the Bank table to remove references to the other tables, and give it a primary key as above. Finally, create an intersection table that holds the following:
    • BCID (Integer)
    • BusCust (single character text)
    • BankID (Integer)

    This table will then handle the links between the other three.
    Thanks for the reply. If you don't mind me asking as I have only a basic understanding. Is there any primary key in the intersecting table? I am assuming that BCID will serve as link between primary key in Customer table and the intersecting table. BusCust will serve the same purpose for the business table. BankID will link to Bank table.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm.

    If you are only going to perform one credit check per bank/[customer|business] combination, then each record can form the primary key. Otherwise you should set up a separate key column. I would still index the three main columns, though.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Apr 2012
    Posts
    7
    Thanks again for the assist. I suspect I may not have explained clearly about the structure of the database and I apologise. If you don't my doing so again.

    I call many banks when performing the credit checks. So Customer_1 will have information from Bank_1, Bank_2, Bank_3 etc. Customer_2 will have information from Bank_1, Bank_2, Bank_3 etc. Business_1 will have information from Bank_1, Bank_2, Bank_3 etc. Business_2 will have information from Bank_1, Bank_2, Bank_3 etc.

    What occurs in my current structure as follows. When printing report for Customer_1, Bank information for everyone else shows up. I think I understand your explanation but again, the last time I attempted to create a database was at university in 1999. Go figure. . Thanks again for sharing your knowledge.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Nothing you've stated here contradicts what you said to start with, or presents a situation that would cause my proposal to fall over.

    If you have an intersection table to deal with the relationship between banks and customers, and the relationship between businesses and customers, you can easily build a report with a subreport to show each customer/business's credit checks with each bank. Each customer/business's section (or page, depending on how you set the report up) would ONLY show their credit checks.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Apr 2012
    Posts
    7
    Ok thanks again. I will need to properly implement you original suggestion and I should be well on my way. I will give you an update once I do that. Take care.

  8. #8
    Join Date
    Apr 2012
    Posts
    7
    Hello again weejas.

    Though I understand the theory behind your solution, I really don't know how to implement it as it has been such a long time since I did anything of the sort.

    If you don't mind my asking for some noob questions I would be eternally grateful.

    In the Individual Client table I have ID (Autonumber--primary key) as you suggestd. Social, Name, Address

    In the Business Table I have BID (Autonumber--primary key), Business Name.

    In the Bank Table I have ID (Autonumber--primary key) as you suggested, Bank Name, Loan Type, Loan Amt.

    In the Intersect Table I have the fields you suggested (BCID, BusCust, BankID). I have no primary key. Which fields in Client/Business should be linked to the fields in Intersect table? Also which fields link the Intersect Table should be linked to which fields to Bank Tables.

    Thanks a million again.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Thinking about it, I've led you astray. The Intersection table should have three numeric columns:
    BusinessID
    ClientID
    BankID

    The ID fields from the other tables then form foreign keys in the Intersection table. You can then set all three fields in the Intersection table to be its primary key.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Apr 2012
    Posts
    7
    Thanks a million again. I found another solution that I seem to have implemented well enough. What I realised though, with your solution a user would have to manually enter data for the intersect table so it wouldn't really work the way I intended. But your guidance and assistance was greatly appreciated. Thanks.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome, although if you set up the relationships between the tables and the data sources for the form, the Jet database engine will handle that for you. I've certainly never had to double-enter data into that kind of table before.

    Glad that you have a working solution!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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