Re: Dazed and Confused

Hi Jeff,

Thanks for the clarification. I'm beginning to understand better.

The Part Detail Information that is being saved is Changes in Part Revision,
Alternate Part Numbers, Actionee, Planner, etc. The Part Details table is
being used mostly so we can keep track of changes in the above mentioned
fields.

There are also many Requisitions that will go to many Purchase Orders and
visa versa. After reading your comments, I'm sure this is another many to
many relationship.

Does the below Entity Relationship design sound correct?

Purchase Order Details Table (many) to a Purchase Order Table (one)
Purchase Order Table (one) to a Junction table(many) with ID numbers.
Junction table (many) to the Purchase Requisition table (many).
Purchase Requisition Table (one) to Part Details Table (many).
Purchase Requisition Table (one) to Purchase Requisition Details Table (many).

"Jeff Boyce" wrote:

Nanette

see comments in-line below...

"Nanette" <Nanette@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:34118449-ED0E-42AB-88BD-2272DEF0AF19@xxxxxxxxxxxxxxxx
One to Many or Many to Many Relationship, and how do I connect the data?

After many attempts, I'm still not sure, so...

I have a Requisition Table and a Reqisition Detail Table. Each Reqisition
will contain many parts numbers and many of the same part numbers will be
on
multiple Requisitions.

So, a given Requisition might have many Parts (i.e., part numbers), and a
given Part may show up in many Requisitions? If so, you need three tables:
Requisition, RequisitionDetail, Part.


I also have a Purchase Order Table and a Purchase Order Detail Table. Each
Purchase Order will contain many part numbers and many of the same part
numbers will be on multiple Purchase Orders. (The Purchase Order Table
contains a Line Item Number, that, when combined with the Purchase Order
Number creates a unique number for that specific part instance.)

I didn't get that last explanation, but this sounds analogous to the
previous ... 1 - m - 1 (three tables: PurchaseOrder, PurchaseOrderDetail,
Part)


I also have a Parts Table and a Part Details Table. This was created at
the
beginning after acceptance of the proposal. It contains all the part
numbers
and their details and is used as a baseline so we can track changes in
pricing and quantities in conjunction with the Purchase Orders and
Reqisitions.

I don't get this. What kind of "part detail" information are you saving?


The process is to create a reqisition first, followed by creating a
Purchase
Order second. Although, sometimes a Requisition is not created and there
will
only be a Purchase Order.

So, a given Requisition can have how many PurchaseOrders? None (it sounds
like), one, many? This will determine the relationship between these two.


Question One: Do I really have a many to many relationship between the
Requisition and Parts Tables and a many to many relationship between the
Purchase Order Table and the Parts Table?

It sure sounds like it.


Question Two: How do I connect/keep track of each Part Number in
conjunction
with its respective Requisition Number and Purchase Order Number?

If I understand your data/relationship, you could use a query that joins the
tables together on their respective IDs, then selects for PartNumber.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


.

Relevant Pages

  • Re: Dazed and Confused
    ... I have a Requisition Table and a Reqisition Detail Table. ... I also have a Purchase Order Table and a Purchase Order Detail Table. ... Microsoft IT Academy Program Mentor ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to link a column with the other file
    ... Adjust column to be extracted ... requisitions with the purchase requisitions details like purchase ... requisition number, purchase order number, purchase order date ete... ...
    (microsoft.public.excel.newusers)
  • How to link a column with the other file
    ... requisitions with the purchase requisitions details like purchase ... requisition number, purchase order number, purchase order date ete... ... corresponds to the site requisition number by putting formula?. ...
    (microsoft.public.excel.newusers)
Loading