Another relationship Q

[stillwillyboy]

We have three branches and each Part can be, but isn’t always, in each branch. A Part number can not be used by more than one customer.

I have the following tables and fields:

TblCustomers
CustomerID (PK)
Name, Address, etc.

TblParts
PartID (PK)
CustomerID (PK)
BranchID (PK)
Description, Category, etc.

TblOrders
OrderID (PK)
PartID
CustomerID
BranchID

TblOrdersDetail
PartID (PK)
OrderDate
ShippingTicket, etc.

One part can have many orders, but at the same time, one order may have more than one part (i.e. a specific part plus UPS, postage, etc. (UPS and postage are set up as parts with no price. The amount of UPS, postage is manually entered at the time of ticket entry.)

Whenever I try to establish a relationship b/w the Parts and Orders table using the PartID field, the Edit Relationship box shows the Parts table as primary and the Orders as secondary (which is correct). But it also tells me that the relationship is indeterminate. The one-to-many should be for the Parts to the Orders.

When I try to establish a relationship b/w the Parts and OrdersDetail tables, using the PartID field, the Edit Relationship box shows the OrdersDetail table as primary (which is not correct) but it tells me that there is a one-to-many relationship.

When I try to establish a relationship b/w the Orders and OrdersDetail tables using the PartID field, the Edit relationship box tells me that the OrdersDetail table is primary, which I do not believe it should be because one order can have a number of lines of detail.

I would like to have Parts to Orders with Parts the primary table and a one-to-many relationship.

I would like to have Orders to OrdersDetail with Orders and the primary table and a one-to-many relationship.

TIA.

Bill

 

[lespaul]

Ok, check out

http://www.alphabetcitydataworks.com

in the developers section a paper 'Fundamentals of Database Design'

read that.

Then

A typical Orders and Detail needs to be set up like:

tblOrders
OrderID (PK)
OrderDate
CustomerID
etc.

tblOrderDetails
OrderID (composite PK with DetailID)
DetailID
LineNumber
Qty
ItemNumber

tblOrders:
OrderID OrderDate CustomerID
1001 4/8/04 AND123
1002 4/5/04 JOE321


tblOrderDetails
OrderID DetailID LineNumber Item Qty
1001 74 1 P5525 3
1001 75 2 P6625 1
1002 76 1 Q1121 1
1002 77 2 A1234 6

So, now that we have MANY order details (since it's a composite PK and it HAS ORDERID in the table, Access should set up your relationships properly.

HTH

leslie

 

[stillwillyboy]

Leslie, I have the document that you told me about. It has been helpful, but...

Please let me clarify some info from my previous post. I am still having trouble getting the relationships to work. The tables are the same, but I have added some info to this post to try to figure this out.

TblCustomers
CustomerID (PK) Req:Yes, Index:Yes, No Dups
Name, Address, etc.

TblParts
PartID (PK) Req:Yes Index: Yes Dups OK
CustomerID (PK) Req:Yes Index: No
BranchID (PK) Req: Yes Index: No
Description, Category, etc.
One part can be for the same customer in more than one branch, but one part cannot be for more than one customer.

TblOrders
OrderID (PK) Req: Yes Index: Yes No Dups
PartID Req:Yes Index: Yes Dups OK
CustomerID
BranchID

TblOrdersDetail
OrderID (PK) Req: yes Index: yes No Dups
OrderDate
ShippingTicket, etc.

That’s what I have for tables. Now…

One part can be on many orders. One order can have many lines of detail.

If I have the form Orders, with the subform, OrdersDetail, I don’t believe that I should have to reenter either the Part Number or the OrderID Number into the Detail part of the form.

The Order number is NOT the same as an Invoice number, though both should be generated by the system. The order number, I believe, is for internally tracking of the orders entered into the system and maybe for lookup. The order number is NOT the same as a Shipping Ticket number. The Shipping Ticket Number is entered manually by the user. It is taken off of the shipping ticket.

Whenever I try to link PartID from the parts table to PartID in the Orders tables, I get the Indeterminate message in the Edit Relationships box. It should be a one-to-many. Parts is listed as the primary table and orders is the secondary table, as they should be.

Whenever I try to link OrderID from the Orders table to OrderID in the Detail table, I get a one-to-one relationship, even though one order can have many lines of detail. Orders is primary to Orderdetail.

I believe that some of the trouble is with indexing, but at this point have not been able to get this to work.

Any help will be greatly appreciated.

Bill

 

[stillwillyboy]

OK, I can get the one-to-many if I link the fields PartID, CustomerID and BranchID from the Parts table to the Orders table. Their are three lines and they are all one-to-many.

At this point, the Orders to OrdersDetail is still giving me a one-to-one. I don't think that this is proper because one customer could have more than one order for the same part in the same branch in the same week. Thus, one order could have many lines of detail. Not to mention that the same order could have lines of detail for the actual part and then another line for shipping, whcih is also billed as a part.

TIA