many to many relationship?

[Guest]

Hi,
I have an order table which holds customer information
Then an order details table which holds the product/supplier information.
Now i've created a Load details table which holds information about the
shipment of each load(shipment) as one order can contain many shipments.

So far I think things are well set-up.

However I want to relate the product information to the load details because
I need my users to specify what product(s) will be in each load, but i don't
want them to be able to choose products that aren't on the order.

I hope this was clear, thanks alot!

 

[Steve Schapel]

Jae,

It seems to me that you need a LoadID field in the Order Details table,
so each item in the order details can be tagged as to the Load it
belongs to. Have I understood you correctly?

Or is it possible for a Load to contain Pruducts from more than one Order?

 

[Guest]

Well here is the issue:
One order can have multiple products, and multiple loads.
One product from one order is usually shipped in multiple loads.
And for the kicker, one load can have multiple products in it.

I hope that helped paint the picture a little bit better.
Thanks for the response.

 

[Steve Schapel]

Jae,

In the case of a product in a given order being split over more than one
Load, I would put each Load's worth of that product as a separate entry
in the Order Detail, identifying the load. Hope you understand what I
mean. Something like this...

Table: Customers
CustomerID
CustomerName
CustomerAddress
etc

Table: Suppliers
SupplierID
SupplierName
SupplierAddress
etc

Table: Products
ProductID
ProductName
SupplierID
etc

Table: Orders
OderID
OrderDate
CustomerID
etc

Table: Loads
LoadID
Description
etc

Table: OrderDetails
DetailID
OrderID
ProductID
LoadID
Quantity
etc

So, for example, if Order 99 is for various quantities of 3 products,
let's say products 123, 234, and 345, and it will be delivered in 2
loads, lets say load 111 and load 112, and suppose all of product 123
goes in load 111 and all of product 234 goes in load 112, and product
345 is split between the 2 loads, then the data in the OrderDetail table
will look like this:

770 99 123 111 5
771 99 234 112 10
772 99 345 111 7
773 99 345 112 2

Am I on the right track now?

 

[Guest]

An alternative approach would be to have an OrderDetailLoads table with
columns:

OrderID
ProductID
Quantity
LoadID

OrderID and ProductID would reference those columns in OrderDetails (where
they are a candidate key) and LoadID would reference the primary key of
Loads. If you have a DetailID surrogate primary key in OrderDetails then
you'd have this rather than OrderID and ProductID as the foreign key
referencing OrderDetails.

Whether you should adopt this model or Steve's depends on the modus operandi
of your business. If each order record and the order details records
referencing it are entered prior to the loads being determined then the above
model facilitates this, whereas if the processes are simultaneous then
Steve's suggested model is a more appropriate.

The quantity per order details item is something which needs special
attention if the above model is used. You clearly need Quantity columns in
both OrderDetails and OrderDetailLoads. You would need to incorporate a
constraint, however, so that the SUM(Quantity) in OrderDetailLoads WHERE
OrderID = OrderDetails.OrderID AND ProductID = OrderDetails.ProductID does
not exceed the Quantity in OrderDetails, i.e. you don't ship more of a
product than have been ordered. If it is less, of course, then that is
potentially a useful bit of information as it means the order is not yet
completely fulfilled as regards that product (or at least the shipping
schedule is not yet fully determined).

 

[Guest]

You're right on the money Steve.
So I would need a DetailID field to identify the "qty" for each load for
each product.
And then a simple Quantity field for each detailID?
Would my relationships be crucial?

So

 

[Guest]

Ken, your approach is excellent.
It makes sense because the sales person has to fill in the order table and
the order details before he can dissect his load quantities.
So would I be able to make a button on the order form like "Load Breakdown"
that opens the OrderDetailsLoads table as a continuous form?

Also I'm thinking if I use your approach, can't I eliminate the entire
LoadDetails table?

I would be able to specify the LoadID using the OrderDetailsLoads, or would
it be wiser to keep it in order to group the multiple products on one load
and view it more clearly?

I really appreciate your suggestions.

 

[Guest]

Firstly I would agree that a LoadDetails table is unnecessary. You can still
see the products per load if you wish (see below). You do need a Loads table
of course with columns for the attributes of each load, LoadID (the primary
key), LoadDate etc.

As regards data entry you have a number of options, again depending on your
business model, though the options are not mutually exclusive, so you can
have several and use them in conjunction. You could have a Loads table with
a LoadDetails subform linked to the parent form on LoadID. The subform would
have combo boxes bound to the OrderID and ProductID fields and a text box
bound to the Quantity field. This would enable you to build up a load from
various orders or you could just use the form for viewing a load built up per
Order as below..

To 'break down' and order into loads I'd envisage an Orders form with an
OrderDetails subform in the usual way (the sample Northwind databases
provides model for this). You could either have an OrderLoadDetails subform
in the OrderDetails subform, but this would mean the OrderDetails subform
would have to be in single form view.

Alternatively, and this would probably be my choice, would be to have a
correlated subform in the parent Orders form. The way to achieve this is to
a text box somewhere in the Orders form and make its ControlSource property a
reference to the ProductID control on the subform. That control would
normally be a combo box so the ControlSource of the text box in the parent
form would be something like this:

=[OrderDetailsSubformControl].Form![cboProductID]

Note that OrderDetailsSubformControl here is the name of the subform control
in the Orders form, i.e. the control which contains the subform. Set the
Visible property of this text box to False (No in the properties sheet) and
call it something like txtProductIDHidden.

To link the subform based on the OrderDetailLoads table you want the
LinkMasterFields property to be a combination of OrderID and ProductID, so in
the subform control's properties sheet eneter the following as the
LinkMasterFields property:

OrderID; txtProductIDHidden

Do this directly in the properties sheet not via the dialogue which pops up
when you click this property's 'build' button (the one to the right with 3
dots). For the LinkChildFields Property use the field names in the usual
way, so in the properties sheet enter:

OrderID;ProductID

The way this should work is, having entered the order details into the first
subform you can enter one or more rows per product for the current order into
the second subform by selecting the product row in the first subform, i.e.
you can assign product in the order to one or more loads. You can build in
validation into this subform to ensure that you don't ship more than the
total quantity for each product. I'd suggest dong it in the subform's
BeforeUpdate event procedure like so:

Dim strCriteria As String, strMessage As String
Dim intQuantShippedSoFar as Integer
Dim intQuantProposedForShipping As Integer

strMessage = "Ordered quantity for this product exceeded."

strCriteria = "OrderID = " & Me.OrderID & _
" And ProductID = " & Me.ProductID

If Me.NewRecord Then
intQuantShippedSoFar = DLookup("Quantity", "OrderDetails", strCriteria)
intQuantProposedForShipping = DSum("Quantity", "OrderDetailLoads",
strCriteria) _
+ Me.Quantity
Else
intQuantShippedSoFar = DLookup("Quantity", "OrderDetails", strCriteria)
& _
- Me.Quantity.OldValue
intQuantProposedForShipping = DSum("Quantity", "OrderDetailLoads",
strCriteria) _
+ Me.Quantity – Me.Quantity.Oldvalue
End If

If intQuantProposedForShipping > intQuantShippedSoFar Then
MsgBox strMessage, vbInformation, "Invalid Operation"
Cancel = True
End If

The user won't be able to save the subform record until a valid quantity has
been entered. If entering data via a subform in a Loads form you could
incorporate similar validation.

 

[Guest]

I'm gonna look over all this a few more times and try to really soak it in.
I appreciate this so much, I know it must have taken a lot of brain power
and time for you to come up with the solution to my problem.
Thank you so much,
Justin
P.S. I'd like to show you what I've got done so far, only if you have time
to take a look at it of course. If you can, my e-mail is (e-mail address removed)

Firstly I would agree that a LoadDetails table is unnecessary. You can still
see the products per load if you wish (see below). You do need a Loads table
of course with columns for the attributes of each load, LoadID (the primary
key), LoadDate etc.

As regards data entry you have a number of options, again depending on your
business model, though the options are not mutually exclusive, so you can
have several and use them in conjunction. You could have a Loads table with
a LoadDetails subform linked to the parent form on LoadID. The subform would
have combo boxes bound to the OrderID and ProductID fields and a text box
bound to the Quantity field. This would enable you to build up a load from
various orders or you could just use the form for viewing a load built up per
Order as below..

To 'break down' and order into loads I'd envisage an Orders form with an
OrderDetails subform in the usual way (the sample Northwind databases
provides model for this). You could either have an OrderLoadDetails subform
in the OrderDetails subform, but this would mean the OrderDetails subform
would have to be in single form view.

Alternatively, and this would probably be my choice, would be to have a
correlated subform in the parent Orders form. The way to achieve this is to
a text box somewhere in the Orders form and make its ControlSource property a
reference to the ProductID control on the subform. That control would
normally be a combo box so the ControlSource of the text box in the parent
form would be something like this:

=[OrderDetailsSubformControl].Form![cboProductID]

Note that OrderDetailsSubformControl here is the name of the subform control
in the Orders form, i.e. the control which contains the subform. Set the
Visible property of this text box to False (No in the properties sheet) and
call it something like txtProductIDHidden.

To link the subform based on the OrderDetailLoads table you want the
LinkMasterFields property to be a combination of OrderID and ProductID, so in
the subform control's properties sheet eneter the following as the
LinkMasterFields property:

OrderID; txtProductIDHidden

Do this directly in the properties sheet not via the dialogue which pops up
when you click this property's 'build' button (the one to the right with 3
dots). For the LinkChildFields Property use the field names in the usual
way, so in the properties sheet enter:

OrderID;ProductID

The way this should work is, having entered the order details into the first
subform you can enter one or more rows per product for the current order into
the second subform by selecting the product row in the first subform, i.e.
you can assign product in the order to one or more loads. You can build in
validation into this subform to ensure that you don't ship more than the
total quantity for each product. I'd suggest dong it in the subform's
BeforeUpdate event procedure like so:

Dim strCriteria As String, strMessage As String
Dim intQuantShippedSoFar as Integer
Dim intQuantProposedForShipping As Integer

strMessage = "Ordered quantity for this product exceeded."

strCriteria = "OrderID = " & Me.OrderID & _
" And ProductID = " & Me.ProductID

If Me.NewRecord Then
intQuantShippedSoFar = DLookup("Quantity", "OrderDetails", strCriteria)
intQuantProposedForShipping = DSum("Quantity", "OrderDetailLoads",
strCriteria) _
+ Me.Quantity
Else
intQuantShippedSoFar = DLookup("Quantity", "OrderDetails", strCriteria)
& _
- Me.Quantity.OldValue
intQuantProposedForShipping = DSum("Quantity", "OrderDetailLoads",
strCriteria) _
+ Me.Quantity – Me.Quantity.Oldvalue
End If

If intQuantProposedForShipping > intQuantShippedSoFar Then
MsgBox strMessage, vbInformation, "Invalid Operation"
Cancel = True
End If

The user won't be able to save the subform record until a valid quantity has
been entered. If entering data via a subform in a Loads form you could
incorporate similar validation.

Ken, your approach is excellent.
It makes sense because the sales person has to fill in the order table and
the order details before he can dissect his load quantities.
So would I be able to make a button on the order form like "Load Breakdown"
that opens the OrderDetailsLoads table as a continuous form?

Also I'm thinking if I use your approach, can't I eliminate the entire
LoadDetails table?

I would be able to specify the LoadID using the OrderDetailsLoads, or would
it be wiser to keep it in order to group the multiple products on one load
and view it more clearly?

I really appreciate your suggestions.

 

[Steve Schapel]

Jae,

Yes, if any given product for any given order is shipped across more
than one load, then you would have a separate record in the OrderDetails
table for each load-division of that product.

Whereas the advice that Ken is giving elsewhere is good, I personally
would not do it like that. To my mind, it is simplest to just keep the
order details to show the order details, which includes the Load
assignments. If the problem is that the product's load is not assigned
at the time of initial data entry of the products ordered, well, that's
fine, you can come back later to the order details subform to enter the
Load data for each product in the order. And if a product is split over
more than one load, the coding to run an Append Query to take care of
this is relatively simple.

As for your relationships, the purpose of them is to enable you to
enforce referential integrity. For example, to avoid an Order for a
non-existent Customer, or to avoid OrderDetails for a non-existent
Order. So yes, in that sense, defining the Relationships correctly is
important.

 

[Guest]

I'm having mixed opinions.
If i'm grasping these concepts correctly, using Steve's method, the order
details has to broken down to specify each product by which load it'll go
into and the qty of that product in that load.

If the problem is that the product's load is not assigned
at the time of initial data entry of the products ordered, well, that's
fine, you can come back later to the order details subform to enter the
Load data for each product in the order.

How would the person entering the load breakdown know how much of each
product was originally sold?

If I use Ken's method which involves adding a subform to the Load Details
subform, i could reference the orderid and productid from the order details
table, to specify only the products which were ordered, and use a constraint
to not allow users to ship more product then that which was sold.

But later on if a customer decides to add new products or increase/decrease
quantities purchased, that method will be trickier to update the load details
table?

Thanks for all the help,
Jae


And if a product is split over

 

[Guest]

I gave steve's way a try(it involved the least restructuring ) and I'm
please with how it works. The users cannot assign product to a load unless
the load exists, which is good. But they can also leave it blank if they are
unsure of the load assignment at the time of entry, which is also good...
The only drawbacks I see for this are the report generation.
When designing a sales contract, will I have to use formulas to tally the
quantities for each product? Because I don't want the load breakdown to be
present on the contract, however it CAN happen that we sell the 3 loads of
the same product to a customer at different prices based on time, in which
case the load breakdown WOULD be necessary...I see this causing a few
headaches, if you have any suggestions my eyes are glued to the screen.

Thanks again,
Jae

 

[Steve Schapel]

Jae,

It would appear that you could probably use a Totals Query as the basis
of your Sales Contract report... Group By Product, Group By UnitPrice,
Sum Of Quantity, Sum Of ItemPrice (hope you get the general concept of
what I am talking about here). This would be irrespective of Load...
If the same product was included in more than one load, but sold at the
same price, it would result in only one line in the sales contract.

 

[Guest]

Yes,
I think totals would be the only workaround, however I think in terms of
ease of use this method is going to be harder to learn for my employees.
Considering the salesperson HAS to know the products and quantities he is
selling at the time of entry, I think it'll be more logical to have the
salesperson or logistics person break down the load on a separate form. Ths
will also make logistics reports a lot easier to design as all the load
information will be stored together.

Thank you once more for the time you spent on answering my questions,
Jae

 

[Steve Schapel]

Jae,

As Ken pointed out, some of these decisions are related to the practical
details of how things actually get done in your business, e.g. at what
stage an ordered product is assgned to a load, and/or split over more
than one load. But here's how I would envisage it from the user
interface point of view. Your OrderDetails subform includes a combobox
for the entry of the Load. If the load assignment is known at the point
of initial data entry, you enter it then. If a product is split over
more than one load, you enter each as a separate line. If some of the
product is sold at one price, and some at another, you enter each as a
separate line. If the order is later modified, you just go back and
edit, or add new records as required. If the load assignment is made
later, you just go back and enter the load for each product in the order
details. As far as I can imagine what you're doing, this should be all
pretty standard data entry practice. So then, the only "complicated"
factor is if a product is initially entered as one line in the order
details, and it is later assigned to be split over more than one load.
Am I right? So, to streamline the process, you could have a little
command button on the detail section of the OrderDetails form, to Split
the order line. You could have a simple "holder" table, 3 fields:
Quantity, UnitPrice, LoadID, let's say we call the table SplitOrder. So
when you click the Split button, it runs a procedure to delete all
records from the SplitOrder table, and opens a continuous view form
based on the SplitOrder table. Use this form to enter the load
assignment for the product, and price breakdown if applicable, and click
a button to update your data. If you like, you could have a calculated
control in the footer of the SplitOrder form, to total the quantities
entered, and then validate this against the quantity entered in the
original order details record for this product. And then it is just an
Append Query to add the split records, based on the entries in the
SplitOrder table, to the OrderDetails, then delete the existing entry,
and Requery the order details subform. Code could be something like this...

Dim dbs As DAO.Database
Set dbs = CurrentDb
With Forms!Orders!OrderDetails.Form
dbs.Execute "INSERT INTO OrderDetails ( OrderID, ProductID, LoadID,
Quantity, UnitPrice )" & _
" SELECT " & !OrderID & ", " & !ProductID & ", LoadID,
Quantity, UnitPrice" & _
" FROM SplitOrder", dbFailOnError
dbs.Execute "DELETE * FROM OrderDetails WHERE DetailID=" & !DetailID
.Requery
End With
Set dbs = Nothing

 

[Steve Schapel]

Jae,

Yes,
I think totals would be the only workaround,

Interesting to use the word "workaround". To me, this is not a
workaround, this is just the way you do reports, standard procedure.

however I think in terms of
ease of use this method is going to be harder to learn for my employees.
Considering the salesperson HAS to know the products and quantities he is
selling at the time of entry, I think it'll be more logical to have the
salesperson or logistics person break down the load on a separate form.

Fair enough, I guess you know the abilities of your staff better than I
do

Ths
will also make logistics reports a lot easier to design as all the load
information will be stored together.

Well, I don't think this is true. Any load-based information that you
need to extract will be based on a query that selects/groups according
to the LoadID. If the load assignment is in the OrderDetails table,
this will be simple. If it is in a separate table, well, it will also
be pretty straightforward, just one more table to join into the query,
so one more level of complexity.

Thank you once more for the time you spent on answering my questions,

You're welcome. Hope you get it working well.

 

[Guest]

I went with Ken's method because I thought it would be easier on my employees
to have the load breakdown separate from the product entry form.
However, i'm running into the problem you foresaw happening.
Once the load breakdown is assigned, I cannot modify the product(ie. replace
peaches with apples) because of the referential integrity.
Is there any solution to this problem, or is my only option to undo all that
work and work on your method?

Thanks,
Justin

 

[Guest]

Hi Steve,
I'm trying to figure out this append query...I'm assuming it will know to
only insert the fields changed(which are in splitorder table) as well as use
the same remaining data for the additional entries it would create?

Using the code you provided, i get a debug error specifying that my db can't
find the field "OrderDetails" referred to in my expression.

Thanks so much,
Justin

 

[Steve Schapel]

Justin,

The code I gave was "air code", off the top of my head, to illustrate a
concept. I haven't tested it, nor can it be precise because I don't
know how you have named stuff in your application. As it is written, it
assumes an OrderDetails table, and also assumes the subform on the
Orders form is named OrderDetails. If this is not reality, you will
need to adjust the code accordingly.

The way I approached it was to append a new record for *all* split
items, and then delete the original entry. The fields that are affected
by the split (e.g. LoadID and Quantity in my sample code) are taken from
the SplitOrder table. The fields where the data is the same for all
records (e.g. ProductID and OrderID in my sample code) are taken from
the values in the existing current record. You can see the different
syntax applied to these fields in the code. Hope that makes sense.

 

[Guest]

Hi Steve,
Give me SOME credit
I did adjust your air code to suit the structure of my db.
However when I run it, i get the error that the field or form doesn't exist.
I double checked everything and the fields/forms the code is calling upon do
exist.

In any case, I am very thankful for your feedback.
You helped me get over a "major" obstacle in building my db.
Thanks again,
Justin