Access Database 2010 relationships help

[ChazTheGeek]

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

[Webdevguy]

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

[Paul.Cov]

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.

[Webdevguy]

This may help too..

[ChazTheGeek]

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.

[ChazTheGeek]

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.

[Paul.Cov]

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.

[ChazTheGeek]

Thanks.