Multiple Tables Confusion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jenniferspnc - 08 May 2008 15:52 GMT

I am stuck and I've tried reading on table design but I get terribly confused
on how everything connects. I get the idea of Primary Key and Foreign Key so
I can create relationships. So for example, perhaps I assign an autonumber
as the PK in the Client table. That would be the FK in another table
correct?

Could someone help me with the tables based on the fields captured? I know
that a client will have multiple sales orders and each order can have
multiple parts. A sales order will only be associated with one client and a
part can be associated with many orders.

Or if there is a really in depth guide I am opening to reading again, just
having a hard time wrapping my mind around this (stuck in excel mode)...as I
tried creating this earlier and put it all in one table, which obviously
didn't work very well at first try.

Thanks.

I do have a Clients table, this is a predetermined set of clients.
I have a Countries table as well so it creates a drop down list.

Client Name
Personnel First Name
Personnel Last Name
Ship to Country
Manufacturer
Product
Product description
Product P/N
Approval Date
Status
Date Shipped
Denied Date
Sales Order #
initials of person entering info.

Reply to this Message

Jerry Whittle - 08 May 2008 17:01 GMT

>> That would be the FK in another table correct?

Most likely but not always true. Some tables are on top of the "food chain"
and their PK aren't used as the FK in other tables. Often these "top" tables
have FK from other tables.

Using M as Many, here's what I see as the table relationships.

Clients 1-M Orders
Orders M-M Parts

A Client can have many Orders. Good.

The problem is the second line as an Order can have many Parts and a Part
can be in many Orders. This is the dreaded Many-to-Many relationship. To make
this work you need to insert another table between them. This table is known
as the linking, bridging, or connecting table. Lets call it OrderParts. It
will have the FK from both the Orders and Parts table.

Orders 1-M OrderParts
Parts 1-M OrderParts

It is confusing and takes some getting use to. I highly recommend getting
some relational database training or reading "Database Design for Mere
Mortals" by Hernandez before proceeding any further on this database.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I am stuck and I've tried reading on table design but I get terribly confused
> on how everything connects. I get the idea of Primary Key and Foreign Key so
[quoted text clipped - 31 lines]
> Sales Order #
> initials of person entering info.

Reply to this Message

jenniferspnc - 08 May 2008 19:21 GMT

I've done more reading from www.profsr.com which is somewhat broken out.

Based on that I've done the following:
tbl_clients:
Client_ID (PK - autonumber)
Client - looks up from a table called clients (predetermined list)
First Name
Last Name

tbl_salesorder
Sales_Order (PK)
Client_ID (FK)
Country - looks up from a table called countries (to avoid mistypes)
Date Approved
First Shipped
Denied Party List
Status

tbl_parts
Part Number (PK)
Manufacturer
Product Description
ECCN

tbl_OrderParts (the bridge you mention)
Part Number
Sales Order

Here are my relationships (which I know there is something wrong here)
tbl_Clients 1:M tbl_salesorder (based on Client_ID)
tbl_salesorders 1:M tbl_OrderParts (based on Sales Order)
tbl_parts 1:M tbl_OrderParts (based on OrderParts)

And since I created lookup fields for Client and Country there are those
tables that are linked.

What's wrong in my relationships? I tried entering data into one table
(tbl_clients)...selecting the client, entering the first name, last name,
then clicking on "+" and entering sales order, country, date approved, first
shipped, recent shipped, denied party list, and status....however then I
click on the next "+" it only shows Part Number which when I try to plug in I
get an error message that reads "You cannont add or change a record because a
related record is required in table tbl_parts".

Thanks for the patience...and I'm continuing to read, just needing a little
more help.

> >> That would be the FK in another table correct?
>
[quoted text clipped - 57 lines]
> > Sales Order #
> > initials of person entering info.

Reply to this Message

Beetle - 08 May 2008 22:58 GMT

You're making progress, but it looks like you still need to tweak a few things.
Here are some additional thoughts (for what it's worth).

First, it appears you are using the dreaded "lookup field" in your tables.
Lookups
(aka combo boxes) can cause problems when used in tables. Combo boxes
in forms are good, combo boxes in tables are bad. For more on this, see this
link;

http://www.mvps.org/access/lookupfields.htm

Speaking of forms, that is what should be used in Access for data entry.
Tables
are for storing data only. It's OK to enter data directly in a table just
for testing
purposes when you are still in the design phase (which is where you are), but
just be aware that you will want to create forms for the actual data entry
once
you have your tables properly setup.

As far as your tables go, I'm a little confused by your Clients table;

>tbl_clients:
>Client_ID (PK - autonumber)
>Client - looks up from a table called clients (predetermined list)
>First Name
>Last Name

This table is called tbl_Clients, but the "Client" field is a lookup to
another
table aslo called Clients? Plus, what is the Client field for? Doesn't the
First
Name, Last Name, etc. basically constitute the client?

As far as the rest of your tables, see comments inline;

>tbl_salesorder
>Sales_Order (PK)
[quoted text clipped - 4 lines]
>Denied Party List
>Status

The Country field should not be a lookup, it should just store the CountryID
as
a foreign key to tblCountries. The rest looks OK as far as I can tell,
although
I don't know what all of your fields are. For example, depending on what
Denied Party List is, perhaps that would belong in another table.

>tbl_parts
>Part Number (PK)
>Manufacturer
>Product Description
>ECCN

You should probably have a table for Manufacturers (since most manufacturers
make more than one part), and the above table should have ManufacturerID
as a foreign key.

>tbl_OrderParts (the bridge you mention)
>Part Number
>Sales Order

>Here are my relationships (which I know there is something wrong here)
>tbl_Clients 1:M tbl_salesorder (based on Client_ID)
>tbl_salesorders 1:M tbl_OrderParts (based on Sales Order)
>tbl_parts 1:M tbl_OrderParts (based on OrderParts)

Your relationships appear to OK, although in the last line above you state
that the relationship is based on OrderParts. I assume that you meant it
is based on Part Number.

>And since I created lookup fields for Client and Country there are those
>tables that are linked.

>What's wrong in my relationships? I tried entering data into one table
>(tbl_clients)...selecting the client, entering the first name, last name,
[quoted text clipped - 3 lines]
>get an error message that reads "You cannont add or change a record because a
>related record is required in table tbl_parts".

It's not a relationship problem (at least I don't think so). I suspect it is
because
you are trying to enter part numbers into the junction table (tbl_OrderParts)
without having first entered them tblParts.

Another tip; As you learn more about Access, and begin to create queries,
forms,
and reports, and possibly even write some VB code, you will find certain
things
to be less of a hassle if you don't use spaces in your table and field names.

Here are some additional online resources where you can get some very
valuable information when it comes to learning Access;

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

And forums like this one are also a great resource. Post back when you have
more questions and good luck.

Signature

_________

Sean Bailey

> I've done more reading from www.profsr.com which is somewhat broken out.
>
[quoted text clipped - 104 lines]
> > > Sales Order #
> > > initials of person entering info.

Reply to this Message

Evi - 08 May 2008 23:46 GMT

Your Table Design looks perfectly sensible. I'd just suggest omitting the
spaces between your field names - it will make future queries etc easier to
build because you won't have to type square brackets around everything (your
labels can show whatever you want them to).

May I suggest that instead of trying to enter your data via those
Subdatasheets in tables, that you make a proper form. You'll be losing loads
of the advantages of using Access by working with tables besides being at
the mercy of Wizards.

Start off with a simple design (eventually you can make the 2 subform design
that you get in Northwind)

Have a main form based on TblClients (Insert, Form, Autoform, Columnar)

Open the form in Design View
Slide TblOrders from the main db window onto the form's Detail section.
The Wizard should kick in and allow you to link via Client ID.
Add a combo for tblCountry (I'd suggest having a proper table with a Primary
key field and then the Country name)

Make a query based on TblOrders. Add all the fields from that table.
Add TblClient to the query. Do not add ClientID from TblClient (that comes
from TblOrders)
Add the other fields you require.

Make a main form from this query, as before.
Slide TblOrderParts in to make the subform, linking by OrderID
Add a combo based on TblParts containing Part Number (close up that column)
and those fields you need to identify the part. Choose to have it store the
Value in the Part Number field. Add your partnumbers via this combo

Go back to your first (Client) main form and add a button to it to open up
your Orders Form
edit the code so that it filters the orders form to show the order selected
in your subform

DoCmd OpenForm "FrmOrders",,,"[OrderID]=" &
Me.YourSubformName.Form.[OrderID]

Evi

PS if any of that is unclear, please say.

> I've done more reading from www.profsr.com which is somewhat broken out.

> Based on that I've done the following:
> tbl_clients:
[quoted text clipped - 105 lines]
> > > Sales Order #
> > > initials of person entering info.

Reply to this Message

jenniferspnc - 09 May 2008 16:00 GMT

Hi Evi,

Thanks for the step by step instructions, but I need a little further help.
I had made changes as Beetle suggested before seeing your post.

I understand forms are used for data entry but I was testing to see if it
worked by entering via table which had me confused so I won't go down that
route anymore.

Based on Beetle's suggestions (and I took your advice also and removed
spaces) I did the following:

tbl_clients
Client ID (PK)
Client Name (predetermined list that I've already imported into that table)

tbl_personnel (realized that the client would have more than one order
person thus needing a separate table).
personnel_id (PK)
First_Name
Last_Name
Client_ID (FK)

tbl_Country
Country_ID (PK)
Country (already imported this list of all countries)

tbl_Manufacturer
Manufacturer_ID (PK)
Manufacturer

tbl_Parts
Part_Number (PK)
Manufacturer_ID (FK)
Product Description
ECCN

tbl_OrderParts
Part_NUmber (PK)
Sales_Order (PK)

tbl_SalesOrder
Sales_Order (PK)
Client_ID (FK)
Country_ID (FK)
Date_Approved
First_Shipped
Recent_Shipped
Denied_List
Status

My relationships are as follows now:
tbl_clients 1:M tbl_personnel (based on client_ID)
tbl_clients 1:M tbl_salesorder (based on client_ID)
tbl_country 1:M tbl_salesorder (based on Country_ID)
tbl_salesorder 1:M tbl_orderParts (based on Sales_order)
tbl_parts 1:M tbl_OrderParts (based on Part_Number)
tbl_manufacturer 1:M tbl_parts (based on Manufacturer_ID)

So I tried to follow along the form suggestions but got confused. When I do
the autoform based on tbl_clients do I add both the client_ID and client? In
preview it didn't show a drop-down so that the user could select or is that
too early to worry about now? How would I incorporate my new tables into
this form design you suggested (which again, I really appreciate you taking
the time to do)?

Thanks.
Have a good weekend!

> Your Table Design looks perfectly sensible. I'd just suggest omitting the
> spaces between your field names - it will make future queries etc easier to
[quoted text clipped - 170 lines]
> > > > Sales Order #
> > > > initials of person entering info.

Reply to this Message

Evi - 09 May 2008 23:47 GMT

Hi Jennifer,
If you are making an Autoform, as opposed to using a Wizard, it just adds
everything in the table. But yes, you would normally add Client Name and
ClientID

You can always use the Properties button to make clientID invisible but it
will almost certainly come in handy at some stage (eg you want to press a
button on your form and open a filtered report to show only the details of
the current client in your form)

I want to make sure that we are not talking at cross purposes. Which table
did you base your main (Autoform) form on?

Which table did you slide into the main form's Detail section in Design
view?

You say
"In preview it didn't show a drop-down so that the user could select "

Select what? Which stage of the procedure are you describing?

Evi

> Hi Evi,
>
[quoted text clipped - 239 lines]
> > > > > Sales Order #
> > > > > initials of person entering info.

Reply to this Message

Rate this thread: