Need Suggestions, brain gone blank

[Gina Whipp]

Hello All,

Looking for suggestions, ideas, thoughts...

I have 5 tables included in my query:

tblCoilReceiving PK
tblCoilRun (related to tblCoilReceiving 1 to 1)
tblCoilRunScrap (related to tblCoilReceiving 1 to many)
tblInvoiceTo
tblMidwestPriceLess

My problem is the MidwestPrice has price increases/decreases but by
year/months not by Order; but I need to store it for historical Invoice
data. SO that I do not want to store it with the Order (table not included
in query) because it would be too much redundant data. I think I should
store it in tblCoilRunScrap but once again I dealing with redundant data AND
since I could have 10 coils on one Order I thought okay not good there
either. Then I thought of an extra table that includes CustomerID,
InvoiceID, MidwestPriceLess and just connect the price to the Invoice (I
like that one the best.) BUT I wanted to hear other opinions...

Oh and a little more history... there is a MidwestPrice but Customers pay,
let's say, less .50 the Midwest Price. It is the less .50 that needs to be
stored because the Midwest Price is always the same for the month/year.

Thanks,
Gina Whipp

 

[Lynn Trapp]

Gina,
I'm not quite sure why storing the variable prices with the individual order
lines is a problem. If your MidwestPrice table is being used for a lookup
then the order lines could store the price on a given order without any
redundancy.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Gina Whipp]

Lynn,

Pardon the delay got involved in something else...

....but the MidwestPrice really shouldn't be looked up with each Order as the
price only changes about once a year, so my thought was to do it another way
because why should you have to look up the price with every Order when the
price will be the same all year.

I hope that made sense.

 

[BruceM]

Is the price associated with some particular line item? If so, why can't it
just be part of that item's information? You can either link to a price
field or store the actual price. In either case you are storing a single
field. If the user is looking up the price from a separate table for each
order then something is not right with your design.
You say the price will remain the same all the year. OK, but what happens
next year? Next year your records from this year should presumably contain
the price at the time, not the current price.
Perhaps an explanation of your database's purpose and structure would help.
The purpose of your query is not clear; nor are the relationships between
the tables. Typically an order record contains customer, date, invoice
number, and other details specific to a particular order. Line items
(including price) are stored in a separate order details table. There is a
one-to-many relationship between the orders table and the order details
table. How does your model differ from that?

 

[Gina Whipp]

Answers in-line

Is the price associated with some particular line item? Yes

If so, why can't it just be part of that item's information? Because the

line item is a calculated field and the price for it will change once a
year.

You can either link to a price field or store the actual price. In either
case you are storing a single field.

I believe it is going to come down to storing the actual price with each
coil, prices are not stored per Order as there can be 10 coils on one Order.

If the user is looking up the price from a separate table for each order
then something is not right with your design.

Nothing wrong with the design prices are not looked up with each Order.

You say the price will remain the same all the year. OK, but what happens
next year? Next year your records from this year should presumably
contain the price at the time, not the current price.

That is exactly my problem and what I as looking for is another way of doing
it or ideas on how someone else would do it.

Perhaps an explanation of your database's purpose and structure would
help.

It is a tolling database. Simply, we get coils in and cut them to sheets
and reship them to our customers end-customer. But during the tolling
process scrap is generated from the head and tail, mostly.

You know this whole problem started because my boss wouldn't listen to me in
the first place, oh well, venting...

The purpose of your query is not clear; nor are the relationships between
the tables. Typically an order record contains customer, date, invoice
number, and other details specific to a particular order. Line items
(including price) are stored in a separate order details table. There is
a one-to-many relationship between the orders table and the order details
table. How does your model differ from that?

No difference. The problem is scrap which is calculated but I'm thinking
I'm going to have to store the price in that table.

 

[BruceM]

Responses inline

Answers in-line


the line item is a calculated field and the price for it will change once
a year.

Do you have a separate table for line items? That is to say, is there a
Line Items table that is separate from the Order table? From what data are
the prices calculated? Unit price, perhaps?

I believe it is going to come down to storing the actual price with each
coil, prices are not stored per Order as there can be 10 coils on one
Order.

You would store the prices with the line item. Each line item would be a
separate record in the Line Items table. If you have one price for
everything, then perhaps you could use code to insert a default value into
each record. Bear in mind that we don't all know what tolling (your word)
is, at least not in your context. From what I can tell you have sheets of
metal or something, and your company cuts them to order into smaller pieces
that are sold to customers. Even if the price only changes once a year,
your database needs a way to "remember" last year's price. There may be
several ways of doing so, but storing the price is probably the most direct.

Nothing wrong with the design prices are not looked up with each Order.

From an earlier posting: "why should you have to look up the price with
every Order when the price will be the same all year". Sounded to me like
looking up the price was a problem. Regarding the database's design, you
have not posted any details, or even a summary (e.g. Orders and Line Items
are related one-to-many), which makes it difficult to offer targeted
suggestions.

That is exactly my problem and what I as looking for is another way of
doing it or ideas on how someone else would do it.

You most likely need an Orders table and a Line Items table. There would be
a one-to-many relationship between them.

It is a tolling database. Simply, we get coils in and cut them to sheets
and reship them to our customers end-customer. But during the tolling
process scrap is generated from the head and tail, mostly.

Does the customer get charged for scrap? If so, it could just be another
line item. If the price for the scrap is different from the price for the
other pieces, no problem. Or scrap could be in a separate table, if for
some reason that is preferred.

You know this whole problem started because my boss wouldn't listen to me
in the first place, oh well, venting...

What would you have done differently were it entirely up to you?

No difference. The problem is scrap which is calculated but I'm thinking
I'm going to have to store the price in that table.

So you have a Line Items table that is separate from the Orders table? Yes,
you will have to store the price. That is not really even a question.
You have listed five tables, but their purpose is still not clear.
tblCoilReceiving seems to be the main table, since other tables are related
to it. What is stored in tblCoil Receiving? I would have guessed that it
serves as the Orders table, except you have already said that the Orders
table is not in the query. I would guess further that the next two tables
are for the order items and for scrap, but they are unrelated to the Orders
table, so that's not it. The meaning of the midwest price table is unclear.
This is why I keep asking about the structure and purpose. Even knowing the
general purpose does not necessarily describe a particular table's function.

 

[Gina Whipp]

Bruce,

First of all thanks for taking the time... a BIG THANKS!

Okay, was trying to keep it simple because this database is kinda
complicated. Picture a roll of paper towels, uncut. You have to send it
out to a company to cut them to the length you specify because you already
sent me the width. Only difference is these are steel, copper, alumimum
coil

tblCoilReceiving - coils (paper towels) received
tblOrders - Orders received
tblOrderDetails (related to Order)
tblCoilRun - the cutting of the coils to the Order specified length (related
to tblCoilReceving 1-to-many as the length can vary per coil)
tblCoilRunScrap - bad parts usually from the head and the tail of the coil
(related to tblCoilReceiving)

tblLifts - this is where the tblCoilReceiving-tblOrderDetail-tblCoilRun all
finally come together and get assigned a lift(skid) number and will later
each lift will be placed on a Bill of Lading to be shipped. Okay new part,
you can only put so many sheets of material on a lift(skid).

It's got lots more tables but I think this is all that you were asking
about??? Tolling is the process of cutting the coil to length.

Some customers pay for scrap and some don't, some want a seperate invoice,
some don't, got those parts worked out.

Oh, and yes I would have done it differently. As it was I inherited this
database and I've been cleaning it up for a year (example tblOrders did NOT
have and tblOrderDetails when I got it, don't ask) I would have preferred
the scrap be stored in the tblCoilRun, because of the way the owner INSISTED
he wanted it done, I was left with no choice, well very little choice. I
pick the fights I can win and I've won most of them but waiting for
something to crop up and saying... "Oh, can we do it my way now???". He
read ONE book on Access and insists he knows what he's doing... okay venting
again but to task...

I do like that suggestion about a LineItem table

 

[BruceM]

So the customer sends you a roll (or coil) of sheet metal, which you cut to
the specified lengths and send back to the customer? In that case it seems
like a link between tblCoilReceiving and tblOrders may make sense. If this
can be done, perhaps a query based on the two tables could be used as a
form's record source. Is it the case that a customer sends a coil or coils,
and that you process them in a single order and send them back? In other
words, does each record in tblCoil Receiving correspond to a single Orders
record? If so, a one-to-one relationship between the tables should
accomplish what you need. A subform based on OrderDetails could still be
used in this scenario, as well as one based on the Scrap table. If I
understand the situation correctly, and if you were starting from scratch, I
might suggest one table for both CoilReceiving and Orders. The coil comes
in, you cut it to length and send it back. The incoming coil and the
outgoing order are closely related.
By the way, my suggestion about a LineItems table was because I did not know
you had an OrderDetails table. I don't think you would need both.
Is a single lift ever used for several customers' orders? Is a single
customer's order sometimes sent out on two lifts?
I'm still not sure how the pricing fits into this. Are customers charged
per cut, or what exactly?
I will wait to find out if I understand the situation correctly. If I am
incorrect in my understanding of the situation then any suggestions I make
will be pointless.

 

[Gina Whipp]

Answers in-line

So the customer sends you a roll (or coil) of sheet metal, which you cut
to the specified lengths and send back to the customer?
Yes
In that case it seems like a link between tblCoilReceiving and tblOrders
may make sense. If this can be done, perhaps a query based on the two
tables could be used as a form's record source. Is it the case that a
customer sends a coil or coils, and that you process them in a single
order and send them back? In other words, does each record in tblCoil
Receiving correspond to a single Orders record? If so, a one-to-one
relationship between the tables should accomplish what you need. A
subform based on OrderDetails could still be used in this scenario, as
well as one based on the Scrap table. If I understand the situation
correctly, and if you were starting from scratch, I might suggest one
table for both CoilReceiving and Orders.

Could not combine tblCoilReceiving with tblOrders because it's could be 1
coil for 3 orders OR 1 order for 10 coils. Orders (and coils) actually come
to you in alloy/temper/poundage so that you must find the ones that that
particular customer sent that match to the order, that part, at least, works
beautifully. SO that, I can evenfind orphaned coils or orphaned orders.

The coil comes in, you cut it to length and send it back. The incoming
coil and the outgoing order are closely related.

We actually send it back to our customers end-customer. So the customer is
say Gina but you would send it to Midnight because that is who I am cutting
it to length for. However, I am paying the bill.

By the way, my suggestion about a LineItems table was because I did not
know you had an OrderDetails table. I don't think you would need both.

Oh, okay good no extra work then.

Is a single lift ever used for several customers' orders? Is a single
customer's order sometimes sent out on two lifts?

Yes to both.

I'm still not sure how the pricing fits into this. Are customers charged
per cut, or what exactly?

Customers are charged per poundage/alloy/temper/packaging all stored in
their prospective table and I have a TollPrice() plus PackagingPrice() , oh
and an OrderExtras() that takes care of that.

I was drawing a blank because of the scrap but after 'talking' with you I
believe the most logical way is to have my MidwestPrice in the table it's
in, this is a monthly price and stays the same per month no matter what. and
store the MidwestPriceLess which is how much less a customer pays for scrap.
For instance, scraps going rate is 1.09 a pound (tblMidwestPrice) but our
customer has agreed to sell us the scrap for .59 because they will pay us
..50 less (tblMidwestPriceLess) then the going rate, which is okay because
they still end up paying a tolling charge because scrap still runs thru the
machine it's just not good for anything when it comes out.

So what it ends up is I wanted a creative way to store the
tblMidwestPriceLess without storing it in every record of the
tblCoilRunScrap but I believe what I have just described says that is where
it has to go.

 

[BruceM]

Answers in-line


Could not combine tblCoilReceiving with tblOrders because it's could be 1
coil for 3 orders OR 1 order for 10 coils. Orders (and coils) actually
come to you in alloy/temper/poundage so that you must find the ones that
that particular customer sent that match to the order, that part, at
least, works beautifully. SO that, I can evenfind orphaned coils or
orphaned orders.

I'm not in your line of business, so I don't understand the jargon.
Presumably an orphaned coil is one that has become disconnected from an
Order, and vice versa. It looks like there is a many-to-many relationship
between Coils and Orders: One coil could be associated with many orders,
and one order with many coils. That would be resolved by a junction table
linked to the PKs from the other two tables. Also, it appears that an
incoming shipment could contain coils that are associated with several
different orders, suggesting a possible need for a CoilReceiving table
(which you have) and a ReceivingDetails table. But if what you are doing
works, that's what counts.

We actually send it back to our customers end-customer. So the customer
is say Gina but you would send it to Midnight because that is who I am
cutting it to length for. However, I am paying the bill.

Here's a new twist. Why are you paying the bill? I thought you were the
vendor. I am also in manufacturing (a value-added service, as yours seems
to be). The way we do it is that the customer is the one placing the order
and paying the bill. The billing contact may be different from the ordering
contact, but that doesn't change the financial information. The end user is
not a consideration for purposes of billing.

Oh, okay good no extra work then.


Yes to both.

Which suggests the need for a Shipment table and a ShipmentDetails table.
Maybe you already have that.

Customers are charged per poundage/alloy/temper/packaging all stored in
their prospective table and I have a TollPrice() plus PackagingPrice() ,
oh and an OrderExtras() that takes care of that.

When you mentioned OrderDetails I had assumed that each detail (line item)
has a price. It now appears that there are at least some parts of the bill
that are charged for the entire order (PackagingPrice, for instance?).
Those would appear in the Orders table, or maybe an OrderExtras table to
give yourself flexibility. My suggestions have been based on the assumption
that each line item has a price field.

I was drawing a blank because of the scrap but after 'talking' with you I
believe the most logical way is to have my MidwestPrice in the table it's
in, this is a monthly price and stays the same per month no matter what.
and store the MidwestPriceLess which is how much less a customer pays for
scrap. For instance, scraps going rate is 1.09 a pound (tblMidwestPrice)
but our customer has agreed to sell us the scrap for .59 because they will
pay us .50 less (tblMidwestPriceLess) then the going rate, which is okay
because they still end up paying a tolling charge because scrap still runs
thru the machine it's just not good for anything when it comes out.

Please understand that a statement such as "our customer has agreed to sell
us the scrap for .59 because they will pay us .50 less (tblMidwestPriceLess)
then the going rate" is puzzling, because in the same sentence the customer
is selling scrap to you and paying you. I suppose you mean that you knock
something off of the bill for any scrap that you keep.

So what it ends up is I wanted a creative way to store the
tblMidwestPriceLess without storing it in every record of the
tblCoilRunScrap but I believe what I have just described says that is
where it has to go.

I expect you are correct. If the price or the data from which it is
calculated ever change, and if you want old records to reflect the price at
the time of the order, you need to store either the price or the variables
from which it is calculated. You may be able to link to a record that
contains pricing information, but you would still be storing something in a
field. To my way of thinking that single field may as well be the price
itself.
The Northwinds database (packaged with Access) has some examples of pricing
information and how it is stored.
Best of luck. I would be glad to take a stab at any other questions you may
have, but it sounds as if you have most of it worked out to your
satisfaction.

 

[Gina Whipp]

Bruce,

Thanks alot, your suggestions/thoughts/comments/analysis actually helped me
get back to my original thought of where and how to store the prices which I
doubted. And more especially, thanks for taking the time to understand what
I was doing and what I was trying to accomplish.

Hopefully, this was my last hurdle but if not, I'll be back!!!

Thanks again!
Gina Whipp

 

[BruceM]

You're welcome. Good luck with the project.