If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Relationships

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks I will try this tonight!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
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.
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,650
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
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.
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,650
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
You guys mind if I interject something here?
Quote:
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
Reply With Quote
Reply

Tags
relationships tables form

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On