Relationship question (1 Viewer)

[Kenln]

And the newbie says: "I'm new to this so I apoligize up front."

I wish (I think) to have two tables:

tbl_Order_Header:
Unique records although the values in the fields will not be unique, i.e. only one (Customer_No + Order_No + Order_Revision), thought the customer, order number and order revisions can be used multiple times just not in the same combination again.

tbl_Order_Header:
- Customer_No
- Order_No
- Order_Revision

tbl_Order_Details:
Nothing unique here, I could autonumber, multiple line items each needing to reference to the Customer_No + Order_No + Order_Revision

tbl_Order_Details:
- Customer_No
- Order_No
- Order_Revision
- Item
- Cost

How do I build the relationship (One [tbl_Order_Header] to Many [tbl_Order_Details] with integrity)?
Which, where do I use the Primary Keys?

I thought of using a query to create a COR (Customer_No + Order_No+Order_Revision) Field as a primary key (tbl_Order_Header) but I can't get the query to build it i.e. I can't press enter and have a null COR field.

sigh....

I'm new to coding (though I've got that working elsewhere okay) but I am still having relationship problems (hehe, so it's not a new joke).

I apreciate any help you can give me,

 

[Pat Hartman]

tbl_Order_Details: 
- Customer_No <-----remove
- Order_No 
- Order_Revision <----- remove
- Item 
- Cost

You only need the Order_No in the details table. That will be the foreign key to the Order table where you can get the customer information, etc.

Take a look at Northwind to see a simple order entry application schema.

 

[Kenln]

Unfortunaltely the Order_No is not unique

Unfortunaltely the Order_No is not unique. Customers #1 - #10 could have Orders #1 - #10 and each order could have revision #1 - #10. Orders are only unique with respect to the Customers and revision.

I did get semething to work using:

tbl_Order_Header: 
- Customer_No - Primary Key
- Order_No -Primary Key
- Order_Revision  -Primary Key

and

tbl_Order_Details: 
-Autonumber - Primary Key
- Customer_No 
- Order_No 
- Order_Revision 
- Item 
- Cost

Using a 1 (tbl_Order_Header) to Many (tbl_Order_Details) relationship with referential integrety. Should I reverse the Primary Keys?

This duplicates information but I don't know how else to do it. If I understand right part of our goal is to normalize tables i.e. don't duplicate info.

I stumped...

 

[KeithG]

You can use CustomerNumber and OrderNumber as primary keys to uniquely identify your records.

 

[grnzbra]

Are you dealing with a database that is already using the COR field? If not, try setting the primary key equal to all three keys (in table design mode, highlight all three fields and then hit the key button). Then include the three fields in the details table. Then join on the three fields. (If this won't work with a null in the revision field, default that field to 0 for no revisions)

If you are truely adding the field values (instead of concatinating them) you could very easily end up with duplicates because two different combinations could easily add up to the same numbers.

 

[Kenln]

There is no COR field it was just my idea to save data fields. I was going to concatinate them into one field. Wasn't working well at all.

Setting the three fields in tbl_Order_Header to pkey and the tbl_Details pkey to autonumber seems to work.

I didn't know if I was doing it right. I'm still very weak on the referencial integrety and relationships.

I think I may have been on the right path then???

 

[grnzbra]

Why are you going with each customer have his own sequence of order numbers? The order numbers are yours, not the customer's so there's no need to include the customer number in the order number. It might make some sense to have each of several stores have its own series of order numbers. However, you will find life to be much simpler if you just have one sequence of order numbers with separate field for customer numbers and revision numbers. If nothing else, you can set the numbers with an autonumber field and not worry about complex joins or parsing concatinated fieds in queries.

 

[Kenln]

Oh yes, I agree completely. However in this case it is not my call since I am trying to automate an already existing process. In some cases I can make suggestions and implement new changes, though I do not think so in this case.

Another question, related but may belong in the VBA section, is there an easy way to create an automated sequence numbering field other than autonumber.

i.e. If I am able to go down the path of unique Change Order Numbers is there an easy way of saying, 'New CO_No.Value = (Last CO_NO.Value + 1)'. The last, highest, next, etc...

 

[grnzbra]

You would run a select top query to get the max order number for a given customer, add 1 and insert it in the new record. (you need to check that you get anything. If it is a new customer, you will not get anything and it will blow up when you try to add 1. If you don't get any records, set the order number to 1.

 

[Kenln]

Thanks,

I'll give it a try.

 

[gemma-the-husky]

I thnik the underlying practical problem is that you are storing the order revisions.

"Normally" you would (say) store the customers order (header details) and line by line order details. If you want to store order revision histories then it becomes much more complex.

eg - Does adding an extra item to the order constitute an order revision or not. Does modifying a price constitiute an order revision. I am well used to a system where orders remain open for a long period, and are subject to price changes during that period. I maintain within the order line a history of the last 10 prices and effective dates. Obviously you could instead store a price history in separate table.

It all depends on how complex you want or need to make this, and that very general concept "enterprise rules" but if you want to be able to produce a diary (say) of every change that ever took place, then you are looking at a very complex beast indeed.

 

[gemma-the-husky]

Incidentally, your keys are fine - I can see that you need all 3 bits to construct the key. Its just that you still may find it easier to store an (internal) autonumber ref for the orders, and use that as the FK for the order lines, as otherwise managing the syntax of queries can become quite laborious. Each time you need a left join say, you have to drag and set the properties on three lines.

 

[Kenln]

I called them order for convienence. Actually they are request and the resulting quotes. Any change to a quote will yield a quote revision and all details have to be kept. Well at least kept so far. It would be nice to only keep the actual or current.

If I pkey(AutoNumber) the Header table how do I reference that in the Details table?

I know that FK means Foreign Key so... that's the one in the Details Table.

 

[gemma-the-husky]

use the autonumber of the order instead of the 3 segments you are currently using - as I say it doesn't change the logic - and may make some things easier to code. so your details just includes

OrderHeader ID
Part No
Qty
Price etc

Is this a new system you are developing?

 

[Kenln]

I am trying to automate and existing system and I need to maintain as much of how they use it as possible.