Results 1 to 11 of 11

Thread: Relationships

  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Thumbs up Relationships

    Hello so here is my first post, I am looking to create a database that would populate multiple tables into a single form. ( i wish i could draw it!)

    Example:
    Table 1: 3 Columns -Customer-
    Customer ID, Customer Name, Customer Address,

    Table 2: 2 Columns -Customer Special Delivery Message-
    Customer ID, Customer Message

    Table 3: 5 Columns -Customer Order Form-
    Customer ID, Customer Name, Customer Address, Customer Message,
    Customer Request

    So on the final Customer order Form; When I enter the Customer ID it should show: Customer ID, Customer Name, Customer Address, from Table 1. And show the customer Message from Table 2,(the customer special delivery message varies and should match the Customer ID from Table 1), the user should be able to enter the customer request and submit it.

    So far I have only accomplish to display the information from Table 1. I assume and from what i have seen on youtube is that i have to create a relationship between the tables.

    The issue i am having now is that i am suposse to have a "Primary Key" for each table, I was planning to use the Customer ID as the primary key for the tables but when I tried to make it the primary key i get an error message saying that it cant save the request (because they would create duplicate values in the index, primary key, or relationship. Change the data in the field that contain duplicate data ... or redefine the index to permite duplicate entries and try again), I look trought the records and didnt find duplicates, so i am not sure how to fix it.

    Assuming that I fix that part is it ok to have the primary key for each table to be Customer ID. connect all the tables via that primary key and than when i enter the customer ID in the Form it would pull all the information i need?

    Hopefully is not too confusing. if you have any other suggestions in how i can accomplish this please feel free!

    Thanks!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,177
    1. Remove the customer's address and name from Table 3. They are redundant, and if you feel you must display it, you can always obtain it from your relationship's join, or with a query.

    2. You do not need a primary key for each table. In your scenario, in fact, it would defeat your purpose. In both Table 2 and 3, the customer id is a foreign key, not a primary key. The relationship from Table1 to each of the other tables should be a one-to-many relationship based on customer id.

    Sam

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    Further to Sam's reply, I would suggest a couple of amendments:
    • The primary key of the orders table ought to be an order number of some sort. Retain the customer ID as a foreign key.
    • The primary key of the messages table ought to be a compund of the customer and order IDs. This assumes that each order can have one message.

    NB - If there is a 1-1 relationship between orders and messages, consider moving the delivery message into the orders table, and dropping the message table altogether.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Nov 2012
    Posts
    3
    Thanks I will try this tonight!

  5. #5
    Join Date
    Nov 2012
    Posts
    3
    ok so i have a little issue i have 2 tables customer table and customer special message table ... i have connected both of this tables so when i enter a customer id it would load the customer special message into a text box... and there is a one to many relationships between them.

    So i am looking to create a 3rd table that is going to STORE the orders.
    The order table is suppose to save the cust id, cust name, cust phone, cust add, cust city,cust state and the special message and finally a text box which will store the customer request i have created a couple failed miserably, for some reason when i enter the cust id nothing comes up. this is what i have tried:

    from the form design view, in the table properties for cust id i have this.
    = Customer_ID «Expr» [Customer]![Customer_ID]

    which in my mind is suppose to bring the customer id from the customer table along with the other information and than once i hit save is suppose to save the new customer order.

    i also tried to create a one to many relationship between the customer table and the request table as follow.

    Customer
    Cust ID *primary key* to Customer Request Cust ID (not primary key)


    any suggestions ... like i said before dont know much about access so sorry if what i tried sounds silly ...

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    First of all, there's no point in storing customer information (other than the customer ID) in the Orders table. You can always link back to it from the customer ID!
    Secondly, if you have more than one message per customer, you need some way to combine all the relevant records from the special message table that relate to the current supplier. Otherwise, you're trying to display multiple records in one text box, which isn't how Access is supposed to work.
    For your order form, you need a combo box. The row source should be a SQL statement that extracts the customer name and number from the customers table. It should store the customer ID in the orders. If you want to see the rest of the customer details, set up a bunch of unbound text boxes. These can either contain DLookup functions to retrieve the relevant fields from the customers table, or they can be actively populated from the Change/LostFocus events of the combo box.
    Finally, what is the customer request?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,434
    Sorry i disagree
    There can be a perfectly good reason store data such as address or contact or contact number in an order. Ferinstance a customer may specify a different delivery address or special instructions.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    There can, indeed. However, in that business model I'd divorce customer addresses from customer accounts, and link them via another intersection table
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,434
    fair enough
    however an invoice or customer order can be part of a legal process where the precise appearance and contents of the document can be of critical importance. So I always store 'stuff' locally within the order / invoice process, not just delivery / contact details but also stuff like prices, goods description AND tax charges. so you cannot let a change occur retrospectively to that document (ferinstance the goods must reqamin as described at the time of the order, you cannot let the tax rate change and so on). nothing destroys a (legal) arguments credibility than documents that have different data on or just as bad missing data because that address was deleted. yes it may help sell yet more disk storage but lost customers/business is vastly more expensive than a few terrabyte disk drives

    Although normalisation would suggest that you shouldn't duplicate data, I'd argue that once an order is placed and a sales transaction starts you have created a new entity. Mind you I'm also a great fan of dumping things like sales invoices into an indexed PDF. Customers do appreciate the ability to wazz across a copy of the invoice (by email or fax) as they are speaking to their errant customer
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    There are pros and cons to both methods. For example, I'm a fan of audit tables on sensitive data :P

    I see what you mean about sales invoices, and every system I've used has taken this approach. (This was much to the confusion of a colleague in Accounts, who couldn't understand why retrospectively applying a customer's discount to the pricing system didn't update the prince on invoices that had already been issued...)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,177
    You guys mind if I interject something here?
    The order table is suppose to save the cust id, cust name, cust phone, cust add, cust city,cust state and the special message and finally a text box which will store the customer request
    If you're going to do this, you're going to have a lot of repetetive data every time the customer orders.

    A better suggestion would be similar to what we did where I used to work. Have a seperate table, maybe called tblCustDelData, to house the customers' delivery info. The customer number would be part of the index; the other part would be the location code. Eg. the first (or only, if not multiple) location might be 1. If there's a second location for the same customer, you can always add a 2.

    In the order table, you would have the customer code already in there. Add a field for the delivery location code. All you have to do now is combo-box to the delivery location data table for the code. It doesn't exist yet? Fine, add it. Your relationship would be a one-to-many from the order table to the delivery location table, based on customer number and location code.

    Be aware that I'm not addressing the special message or the customer request fields. They may be better off in the order table, if every delivery (I assume you don't always fulfill orders in one delivery) gets the same request/message info. If not, maybe they'd be better off in a seperate table that houses only delivery info. Of course, if they're dependant only on the delivery location, by all means store them in the delivery location table.

    Sam

Tags for this Thread

Posting Permissions

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