Multiple Tables Confusion

[jenniferspnc]

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.

 

[Jerry Whittle]

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.

 

[jenniferspnc]

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.

 

[Beetle]

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)
Client_ID (FK)
Country - looks up from a table called countries (to avoid mistypes)
Date Approved
First Shipped
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,
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".

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.

 

[Evi]

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.

 

[jenniferspnc]

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
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:
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.

 

[Evi]

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,

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
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:
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?

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.
--
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

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.

 

[jenniferspnc]

Morning Evi,
I based the autoform off the table Clients (tbl_clients). I was hoping that
client would be a drop down so that the user could select the client (as the
list is predetermined and loaded into the table).

I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID."

So this is where I'm stuck. Is this right? I ask because I wonder would
this be where a user enters information? Or still too early on?

Can't thank you enough for your patience. Tried searching for an in depth
article on forms but usually find one on how to create a form based off a
single table, and obviously that isn't fitting here. Thanks.

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,

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
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:
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?

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.
--
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
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

 

[Evi]

To search for a particular client in your client mainform, in design view,
add combo box to the form header. One of the options which the wizard will
usually provide for a table with a primary key is the option to Find a
Record in this form...
Select the ClientId field and client surname (for now -we'll change that to
a whole name later)
Click on ClientID as the unique field.

You say:

I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID." >
So this is where I'm stuck. Is this right? "

Yes, that's right. How are you stuck? Is the form working? As you turn from
one client to next, can you enter and see their different sales orders
(though not the parts for that sales order yet - that will come when we get
this bit working)
Open your subform in Design view. Add a combo based on tblCountry, having
CountryID and Country (let the Wizard hide the CountryId). Choose to Store
the Value in Country ID.

You should be able to fill in all the sales orders for that client (though
not the Parts yet)



When you've got that working, use the same technique to create a similar
form with TblSalesOrder as the Main form and TblOrderParts as the subform
with a combo based on TblParts in the subform.

Open your main form in Design view.
Click on Properties when you are clicked in a grey area, off the form's
grid. The Property box should say Form in the Title Bar.

Click on the Data tab.

Click just right the Record Source.

Say Yes you want to invoke the Query Builder.

A query will open in Design view, based on TblSalesOrder.
Add all its fields to the query
Click on the Add Tables button.
Add TblClient to the query and TblCountry.

From those tables, DONT add the Primary Key fields. Add those fields which
you want to see in your form (eg ClientName, address etc and CountryID from
TblClient, Country from TblCountry) Only add one Primary key field to your
query.

Close the query and choose to Save the Sql.

Click the field list button and slide those field which you want to see,
onto your form's grid.

See if this form is working (can you add Parts to an existing Sales order)



Evi

Morning Evi,
I based the autoform off the table Clients (tbl_clients). I was hoping that
client would be a drop down so that the user could select the client (as the
list is predetermined and loaded into the table).


I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID."

So this is where I'm stuck. Is this right? I ask because I wonder would
this be where a user enters information? Or still too early on?

Can't thank you enough for your patience. Tried searching for an in depth
article on forms but usually find one on how to create a form based off a
single table, and obviously that isn't fitting here. Thanks.

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,

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

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:
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?

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.
--
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
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

 

[jenniferspnc]

Sorry I'm now just having time to work on this again. But I've made
progress. I've followed all the steps but still have a few questions.

On the final section you mentioned, " See if this form is working (can you
add Parts to an existing Sales order)" is where I'm having trouble. I get
the message "You cannot add or change a ecord because a related record is
required in table "tbl_parts". I don't have parts uploaded into the
database, is this the issue? Or did I miss something you told me along the
way?

And how do I fit the personnel table into this scenario? But honestly once
I get the form working that should make sense I'm hoping.

Thanks again for all your patience.

To search for a particular client in your client mainform, in design view,
add combo box to the form header. One of the options which the wizard will
usually provide for a table with a primary key is the option to Find a
Record in this form...
Select the ClientId field and client surname (for now -we'll change that to
a whole name later)
Click on ClientID as the unique field.

You say:

I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID." >
So this is where I'm stuck. Is this right? "

Yes, that's right. How are you stuck? Is the form working? As you turn from
one client to next, can you enter and see their different sales orders
(though not the parts for that sales order yet - that will come when we get
this bit working)
Open your subform in Design view. Add a combo based on tblCountry, having
CountryID and Country (let the Wizard hide the CountryId). Choose to Store
the Value in Country ID.

You should be able to fill in all the sales orders for that client (though
not the Parts yet)



When you've got that working, use the same technique to create a similar
form with TblSalesOrder as the Main form and TblOrderParts as the subform
with a combo based on TblParts in the subform.

Open your main form in Design view.
Click on Properties when you are clicked in a grey area, off the form's
grid. The Property box should say Form in the Title Bar.

Click on the Data tab.

Click just right the Record Source.

Say Yes you want to invoke the Query Builder.

A query will open in Design view, based on TblSalesOrder.
Add all its fields to the query
Click on the Add Tables button.
Add TblClient to the query and TblCountry.

From those tables, DONT add the Primary Key fields. Add those fields which
you want to see in your form (eg ClientName, address etc and CountryID from
TblClient, Country from TblCountry) Only add one Primary key field to your
query.

Close the query and choose to Save the Sql.

Click the field list button and slide those field which you want to see,
onto your form's grid.

See if this form is working (can you add Parts to an existing Sales order)



Evi

Morning Evi,
I based the autoform off the table Clients (tbl_clients). I was hoping that
client would be a drop down so that the user could select the client (as the
list is predetermined and loaded into the table).


I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID."

So this is where I'm stuck. Is this right? I ask because I wonder would
this be where a user enters information? Or still too early on?

Can't thank you enough for your patience. Tried searching for an in depth
article on forms but usually find one on how to create a form based off a
single table, and obviously that isn't fitting here. Thanks.

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,

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
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:
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

 

[Evi]

I'm not sure what you mean by 'I don't have parts uploaded into the
database' but if you mean that it is a linked table (something you didn't
mention) then, yes, it is an issue.
Or do you mean that the part had not been added to your Parts table yet so
was not available in the combo box when you wanted to add a part to a sales
order?


Evi

Sorry I'm now just having time to work on this again. But I've made
progress. I've followed all the steps but still have a few questions.

On the final section you mentioned, " See if this form is working (can you
add Parts to an existing Sales order)" is where I'm having trouble. I get
the message "You cannot add or change a ecord because a related record is
required in table "tbl_parts". I don't have parts uploaded into the
database, is this the issue? Or did I miss something you told me along the
way?

And how do I fit the personnel table into this scenario? But honestly once
I get the form working that should make sense I'm hoping.

Thanks again for all your patience.

To search for a particular client in your client mainform, in design view,
add combo box to the form header. One of the options which the wizard will
usually provide for a table with a primary key is the option to Find a
Record in this form...
Select the ClientId field and client surname (for now -we'll change that to
a whole name later)
Click on ClientID as the unique field.

You say:

I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID." >
So this is where I'm stuck. Is this right? "

Yes, that's right. How are you stuck? Is the form working? As you turn from
one client to next, can you enter and see their different sales orders
(though not the parts for that sales order yet - that will come when we get
this bit working)
Open your subform in Design view. Add a combo based on tblCountry, having
CountryID and Country (let the Wizard hide the CountryId). Choose to Store
the Value in Country ID.

You should be able to fill in all the sales orders for that client (though
not the Parts yet)



When you've got that working, use the same technique to create a similar
form with TblSalesOrder as the Main form and TblOrderParts as the subform
with a combo based on TblParts in the subform.

Open your main form in Design view.
Click on Properties when you are clicked in a grey area, off the form's
grid. The Property box should say Form in the Title Bar.

Click on the Data tab.

Click just right the Record Source.

Say Yes you want to invoke the Query Builder.

A query will open in Design view, based on TblSalesOrder.
Add all its fields to the query
Click on the Add Tables button.
Add TblClient to the query and TblCountry.

From those tables, DONT add the Primary Key fields. Add those fields which
you want to see in your form (eg ClientName, address etc and CountryID from
TblClient, Country from TblCountry) Only add one Primary key field to your
query.

Close the query and choose to Save the Sql.

Click the field list button and slide those field which you want to see,
onto your form's grid.

See if this form is working (can you add Parts to an existing Sales order)



Evi

Morning Evi,
I based the autoform off the table Clients (tbl_clients). I was

hoping
that

client would be a drop down so that the user could select the client

(as
the

list is predetermined and loaded into the table).


I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID."

So this is where I'm stuck. Is this right? I ask because I wonder would
this be where a user enters information? Or still too early on?

Can't thank you enough for your patience. Tried searching for an in depth
article on forms but usually find one on how to create a form based off a
single table, and obviously that isn't fitting here. Thanks.

:

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,

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
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:
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

 

[Evi]

I see that you have a joint primary key for TblOrderParts. It may make
things simpler if you use a separate single autonumber primary key just for
ease of maintance and then have PartNumber and SalesOrder as simple foreign
key fields.
Look up Unique Index in Google for this newsgroup. I've described in detail
how you can set one up which ensures that you don't add the same part to the
same order twice.
You say: 'how do I fit the personnel table into this scenario'
It depends how the Personnel fit into your business? ie does a Person 'own'
a client or does he own an order? Or does he own a Part? Or do several
Persons own a client/an order/a part?

The concept is like this

for instance:

If only person is counted as 'taking an order' and you need to record which
person took that particular order so that he can be paid commission, then
PersonID would be a foreign key field TblOrders ie 1 person, 1 order.

If several people could be involved and you need to record all personnel
involved in an order, then this would require a seperate table
TblOrderPerson, with its own Primary key then OrderID PersonID as foreign
key fields.

If one person is responsible for supplying a part for an order and you need
to list that and, if another person takes over, you don't need to keep a
record of the previous person, then PersonID will be a foreign key field in
TblOrderParts

If more than one person is responsible for supplying a part and you need to
list all the people involved then you need a TblPartsPersonnel table with
PartID and PersonID as Foreign Key fields.
If a part is always supplied by a particular person for all orders and all
you have to record is that John Smith is responsible for Widgets and you
don't need to keep this on record if Bill Smith takes over the job, then
PersonID can be a foreign key field in TblParts.
If several people are responsible for supplying parts then you need to have
TblPeopleParts with PartID and PersonID as Foreign Key fields

If you need to record that a part supplied by Bill Smith is the one what was
added to a particular order, then the Primary Key for TblPeopleParts is the
one that has to be the foreign key in TblOrderParts. If all you need to
record is that a Part added to an order then PartID can be the foreign key
field.

Do you see how it works?

Evi

I'm not sure what you mean by 'I don't have parts uploaded into the
database' but if you mean that it is a linked table (something you didn't
mention) then, yes, it is an issue.
Or do you mean that the part had not been added to your Parts table yet so
was not available in the combo box when you wanted to add a part to a sales
order?


Evi

Sorry I'm now just having time to work on this again. But I've made
progress. I've followed all the steps but still have a few questions.

On the final section you mentioned, " See if this form is working (can you
add Parts to an existing Sales order)" is where I'm having trouble. I get
the message "You cannot add or change a ecord because a related record is
required in table "tbl_parts". I don't have parts uploaded into the
database, is this the issue? Or did I miss something you told me along the
way?

And how do I fit the personnel table into this scenario? But honestly once
I get the form working that should make sense I'm hoping.

Thanks again for all your patience.

that

to

we

get

Name

and

invisible

but

select

"

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.


message
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