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"
discussions.microsoft.com> wrote in message
news:85D317F2-7E9E-4158-A84A-3CCA15A5D810@microsoft.com...> 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.
>
>
> "Jerry Whittle" wrote:
>
>>>> 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.
>>
>>
>> "jenniferspnc" wrote:
>>
>>> 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.
