Results 1 to 8 of 8
  1. #1
    Not in dev/null yet ChazTheGeek's Avatar
    Join Date
    Nov 2011
    Location
    Christchurch
    Posts
    1,365

    Unhappy Access Database 2010 relationships help

    Hi all, I'm doing an assessment for school and it's making a relational database using access 2010. I'm having a slight problem which is making my database fail. I link up 2 feilds (PaymentID and CustomerID) to the corresponding fields (PaymentID and CustomerID. They are for foreign keys for linking) in the main orders table. Well the whole database acts if it doesn't recognize that the relationships even work. No linking. Nothing. Nothing is in sync with the other tables. I tried entering data into the orders table and when I check for linking I see nothing. I'm not sure if you guys deal with databases or not but if you don't, I was wondering if you could direct me to some place that could assist me. Thanks once again

    Some screen shots attached
    The database I'm using in the pictures isn't my actual one. Just one I made at home that has exactly the same tables and problem. The customer records also differ.

    I hope if explained enough. If I haven't let me know
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Database Relationships.JPG 
Views:	185 
Size:	32.8 KB 
ID:	5659   Click image for larger version. 

Name:	Example.jpg 
Views:	130 
Size:	21.0 KB 
ID:	5660  
    ASUS P8P67-M Pro | i5 2500k 4.2GHz | Hyper 212 EVO | GTX 970OC | 500B SSD | 2x 1TB WD Caviar Blue | | 16GB RAM | Corsair 400R | RM850

  2. #2
    Senior Member
    Join Date
    Oct 2013
    Location
    Way out West.
    Posts
    1,486

    Default Re: Access Database 2010 relationships help

    Quote Originally Posted by ChazTheGeek View Post
    Hi all, I'm doing an assessment for school and it's making a relational database using access 2010. I'm having a slight problem which is making my database fail. I link up 2 feilds (PaymentID and CustomerID) to the corresponding fields (PaymentID and CustomerID. They are for foreign keys for linking) in the main orders table. Well the whole database acts if it doesn't recognize that the relationships even work. No linking. Nothing. Nothing is in sync with the other tables. I tried entering data into the orders table and when I check for linking I see nothing. I'm not sure if you guys deal with databases or not but if you don't, I was wondering if you could direct me to some place that could assist me. Thanks once again

    Some screen shots attached
    The database I'm using in the pictures isn't my actual one. Just one I made at home that has exactly the same tables and problem. The customer records also differ.

    I hope if explained enough. If I haven't let me know
    Chaz, does part of your assignment include problem solving? As well as understanding how a relational database works...

    If so you may want to consider learning how to search on Google first?

    Fwiw, problem solving is also a prior requisite of many IT, software developing and database related jobs

    Just a thought
    Working on a few different projects.

  3. #3
    Senior Member Paul.Cov's Avatar
    Join Date
    Aug 2006
    Posts
    2,709

    Default Re: Access Database 2010 relationships help

    One-to-many relationships can seem very bizarre at first glance. Having two different tables (customers and payments) feeding into a single table with 'many' links is going to cause chaos.

    Generally speaking it's best to keep your table links spreading like branches on a tree. ie the One trunk can have Many major branches, each major branch may have Many smaller branches, each smaller branch can have many smaller branchlets. But no single twig can have 5 trunks.

    In the real world, payments can be really messy. People may make a single payment which covers 3 or 4 orders, or only part of a single order, or 10 payments to pay off a single order.

    I'd suggest you make your Customers the primary 'trunk' of the tree (the One part of all the relationships). Then allow many orders. Then allow many payments, but linked back to the customer ID in each case. It will be tidier if the payments table holds a field to record the Order ID to which each payment (or part payment) pertains. This should NOT be linked with a relationship between Payments and Orders.

    Many to Many relationships are very special, and in most cases should be avoided whenever possible.

    Receiving a payment of $100 might contain $20 on invoice1, $20 on invoice2, $20 on invoice3, so the single payment might need multiple entries made in the payments field, or alternately have two payments tables, one which is the One, the Trunk, which records each physical payment, and has a Many link to another payments table, which in turn can record the breakdown of the invoices covered in the one physical payment. Most of the time it'll be one payment on one invoice, but not every time.

    Clear as mud?

    Now bend your mind around how to handle when Customer1 wants to pay for his and Customer2's invoices with a single cheque. It can happen. THe real world gets messy, and a database needs to flex to accommodate these odd cases.

  4. #4
    Senior Member
    Join Date
    Oct 2013
    Location
    Way out West.
    Posts
    1,486

    Default Re: Access Database 2010 relationships help

    This may help too..
    Working on a few different projects.

  5. #5
    Not in dev/null yet ChazTheGeek's Avatar
    Join Date
    Nov 2011
    Location
    Christchurch
    Posts
    1,365

    Default Re: Access Database 2010 relationships help

    Thanks guys. I'll look into all that. I think I have solved it though. One issue though is displaying two columns in a combo box. I can get two to show when I us the drop down but when I close it, Only the first column will show.
    I want to be able show the first and last names of a customer in one field.
    ASUS P8P67-M Pro | i5 2500k 4.2GHz | Hyper 212 EVO | GTX 970OC | 500B SSD | 2x 1TB WD Caviar Blue | | 16GB RAM | Corsair 400R | RM850

  6. #6
    Not in dev/null yet ChazTheGeek's Avatar
    Join Date
    Nov 2011
    Location
    Christchurch
    Posts
    1,365

    Default Re: Access Database 2010 relationships help

    Paul's tip probably will get me extra points. But I would have to redesign my whole database and redo my planning. I think I could still get an excellence with what I've got (when it's done). Maybe next year.
    ASUS P8P67-M Pro | i5 2500k 4.2GHz | Hyper 212 EVO | GTX 970OC | 500B SSD | 2x 1TB WD Caviar Blue | | 16GB RAM | Corsair 400R | RM850

  7. #7
    Senior Member Paul.Cov's Avatar
    Join Date
    Aug 2006
    Posts
    2,709

    Default Re: Access Database 2010 relationships help

    Quote Originally Posted by ChazTheGeek View Post
    I want to be able show the first and last names of a customer in one field.
    So you may want a field that uses a calculated result, or concatenated text, achieved by referencing other fields (that don't need to be visible on the form, but do need to be in the underlying table/query), or a comboControlSource that is calculated like =[Surname]&", "&[FirstName]. The bound column that is shown in the result of the combobox needn't be visible in the drop down lists (ie width of zero in the drop down list)

    This calculated result typically is not a bound field (not entirely true), but will reference two or more other bound fields. All that really needs to be bound (somewhere on the form) is the CustomerID, the rest is looked up and generated as needed, via your formulas and underlying relationship links.

    Combo box source query/tables don't necessarily need to be in any way related to the underlying table that the form is built around.

  8. #8
    Not in dev/null yet ChazTheGeek's Avatar
    Join Date
    Nov 2011
    Location
    Christchurch
    Posts
    1,365

    Default Re: Access Database 2010 relationships help

    Thanks.
    ASUS P8P67-M Pro | i5 2500k 4.2GHz | Hyper 212 EVO | GTX 970OC | 500B SSD | 2x 1TB WD Caviar Blue | | 16GB RAM | Corsair 400R | RM850

Similar Threads

  1. Access database
    By Brucem in forum PressF1
    Replies: 3
    Last Post: 15-04-2012, 12:00 PM
  2. Medical database using access
    By Ripdog in forum PressF1
    Replies: 12
    Last Post: 22-08-2006, 05:44 PM
  3. Access Database
    By Stu927 in forum PressF1
    Replies: 6
    Last Post: 03-12-2005, 10:44 AM
  4. Access 97 database
    By Steve in forum PressF1
    Replies: 1
    Last Post: 04-07-2002, 04:49 PM
  5. Access Database
    By DavidS in forum PressF1
    Replies: 6
    Last Post: 02-07-2002, 12:46 PM

Posting Permissions

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