Customers and Orders tables - simple relationship

[MikeLeBen]

Hello,

I'm new to the place as I am to Access, and this is what I'm trying to accomplish:

I've created a table named Customers that stores various information, and has a primary key named IDCustomer.

Then I created an Orders table (with a primary key named IDOrders) and set a one to many relationship from the first one, relating Customers.IDCustomer -> Orders.IDCustomer.

My question is: do I need to populate the Orders table with the values stored in the Customers.IDCustomer field (like with an append query), given my purpose is to use various select queries to filter customers on a geographic base to plan phone calls and possible deliveries (to be stored in the Orders table)?

 

[jzwp22]

Welcome to the forum!

You would use forms bound to their respective tables to enter data. When you use bound forms, Access enters the data into the tables for you; there is no need for append queries. Of course, if you are using unbound forms, you would need append queries.

Do you have a table that holds the various geographic regions?
How are you relating the customers to those regions?
Can a customer have a presence in many regions?
Can a region be home to many customers?
Will an order consist of many items?

May I suggest taking a look at Crystal's site; she has several tutorials that might be helpful for someone just starting out with Access.

 

[MikeLeBen]

Welcome to the forum!

You would use forms bound to their respective tables to enter data. When you use bound forms, Access enters the data into the tables for you; there is no need for append queries. Of course, if you are using unbound forms, you would need append queries.

Do you have a table that holds the various geographic regions?
How are you relating the customers to those regions?
Can a customer have a presence in many regions?
Can a region be home to many customers?
Will an order consist of many items?

May I suggest taking a look at Crystal's [linkage]; she has several tutorials that might be helpful for someone just starting out with Access.

Thanks for the welcome, and the reply

So far I only have the two aforementioned tables on the database.
The zone field in the Customers table holds related information.
One customer can only be in one region, and there can be several customers in one region.
The order can consist of many items, though for the time being I don't plan on dealing with that with Access.

Meanwhile I'll check that website out

 

[jzwp22]

One customer can only be in one region, and there can be several customers in one region.

Based on the above, having a separate table to hold the regions makes sense and then join that to your customer table. This helps in your data consistency and also saves on retyping the regional names over and over for each new customer.

 

[MikeLeBen]

Cool, thank you, though I'm not sure I'll need it because no new zones will be needed for the DB and so far I managed with a combobox with fixed entries (I doubt these are the technical terms for what I'm talking about, but I mean when a droplist opens and gives you a list of possible choices).

I am installing access on this machine too so i can do some testing and practice remotely (the machine I work on is at my workplace), thanks for your support

 

[jzwp22]

A word of caution, if you are using dropdowns (list/combo boxes) at the table level it is generally not a good idea (even though Access has the capability). This site goes into detail as to the problems it can cause. The list and combo boxes are best left for your forms.

 

[MikeLeBen]

Gotcha.

Therefore I should be able to arrange a query to do the dirty job for me and assign them in the customers table as an external key? I'm not sure I'll be able to, but I am certainly going to try.

 

[jzwp22]

I would use a form and on the form use a combo box bound to the external key field (fkRegionID) of the customer table. The combo box's row source would be the region table.

Customer
-IDCustomer primary key, autonumber
-fkRegionID foreign (external) key to tblRegions

tblRegions
-pkRegionID primary key, autonumber
-txtRegionName

 

[jdraw]

You may want to look at these models to get some ideas re relationships between tables.
http://www.databaseanswers.org/data_models/customers_and_orders/index.htm
http://www.databaseanswers.org/data_models/customers_and_partial_deliveries/index.htm

and take a look at the Business Rules stored with the models.

 

[MikeLeBen]

I am supposed to migrate the region data from the customers.zone field to a dedicated table: is there a quicker way to populate the new table with the unique region names i've typed in so far? I have like a 1000 records on my tblcustomers and about 25 regions or so.

 

[jzwp22]

You can use a query similar to the following

SELECT DISTINCT regionfieldname
FROM customertablename

Then change the query type to append and then select your region table as the destination table. This will append the regions to your region table.


Now you need to adjust the customer table.

Add a new field to your customer table to hold the primary key of the region table as a foreign key (fkRegionID in my earlier post)

Create a new select query that joins the region table and the customer table via the region name fields in both tables. Add the fkRegionID field of the customer table to the query grid. Change the query type to update, and in the update to field use the tblRegion.pkRegionID field. This will update the fkRegionID field of the customer table with the appropriate region value.

Then you can remove the text field that holds the region name in your customer table. You can then establish the relationship between the two tables in the relationship window.

 

[MikeLeBen]

Thank you so much.
It took me a couple minutes to figure out I had to modify the SQL for the join query as the design view wouldn't enable me to properly join the results (Crystal's advice.. always take a look at the SQL );

besides, it's starting to look like a proper database, and I owe it to your patience and dedication. My next task is to design proper forms to achieve the following:

-filter customers by regions (perhaps a ComboBox with tblRegion.pkRegionID as row source will do? Need to look into it).

-manage orders on filtered results (ideally the user will have a list of customers of a region and their phone numbers to contact them and offer the products).

 

[jzwp22]

Do you have your tables for orders and products set up? I would recommend getting the table structure finalized before jumping into forms. Otherwise, you might end of having to scrap the forms if you find you need to change your table structure.

 

[MikeLeBen]

Actually I don't have a products table yet.

I guess I ought to go by the template jdraw linked earlier, though honestly that would mean also exporting the address field from the Customer table which is, well, holding all that.
Is it really necessary?
I'm not sure about it, while I can see the advantage in a products table.

 

[jzwp22]

Is it really necessary?

It depends on your data/application. If you have a customer who has multiple locations/address, then you have a one-to-many relationship which is handled with a separate but related table.

You might want to take a look at this site. It has an overview of the rules of normalization which govern how you should set up your table structure.

 

[MikeLeBen]

I am going to look into it;

meanwhile here's how my relationships diagram looks like (I doubt you need help with the translation at this point), for someone experienced like yourself a quick glance should be more than enough.

ht tp://img137.imageshack.us/img137/1723/relationshipsdiagram.jpg

 

[jzwp22]

You should have a one-to-many relationship between the order table and the order_products table. You should not have the productID field in your order table, and therefore you should not be joining the order table to the product table, the order_products table handles the products related to an order.

 

[MikeLeBen]

I think this should be it then.
It's still not very clear to me what the order_products table's purpose is, I just saw it on an example sales db and basically copy pasted it from there. I guess it stores the orders product-wise, that is, how many item A orders and how many item B orders there will be?

ht tp://img688.imageshack.us/img688/9079/relationshipsbl.jpg

Assuming this is correct, I ought to proceed building forms to make it.. accessible

 

[jzwp22]

Your structure is now correct for the order_products, but it is only correct if that is what you need. The order_products table is a junction table that relates the items ordered for a particular order. What the junction table does is allows you to associate many products to an order and a product can be tied to many orders. For example, let's say that you have 2 customers and each one places an order.

Customer A places the following order:

5 units of Product A
10 units of Product C


Customer B place the following order:

10 units of Product A
20 units of Product C
30 units of Product G


With the structure you have, you can now capture the above orders using the order_products junction table.

 

[MikeLeBen]

Your structure is now correct for the order_products, but it is only correct if that is what you need. The order_products table is a junction table that relates the items ordered for a particular order. What the junction table does is allows you to associate many products to an order and a product can be tied to many orders. For example, let's say that you have 2 customers and each one places an order.

Customer A places the following order:

5 units of Product A
10 units of Product C


Customer B place the following order:

10 units of Product A
20 units of Product C
30 units of Product G


With the structure you have, you can now capture the above orders using the order_products junction table.

cheers, best explanation.
I am having a bad time with forms, as I'm probably trying to achieve a result beyond my capabilities: checkboxes to filter results.

user has a number of checkboxes to specify the area she/he wants to look for customers in.

The result should then be loaded in a subform including phone numbers which lets her/him call the customers and possibly manage orders if any are placed.

This is basically all my database is needed for, for the time being.

P.S.: should I post this in the Forms section of the forums to attain "normalization"?