Order progression - help!

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi there

I have a database and wish to update a customer order
form with a field for order progression details.

I need to have a block i can put a date/time, intials of
operator and an order progression details. Ideally i
would like to be able to be able to add mulitple
progressions to an order and have a seperate line for
each one, with the most recent progression at the top.

Hope that makes sense, Any help would be appriciated,
Thanks
Steve
 
Steve said:
Hi there

I have a database and wish to update a customer order
form with a field for order progression details.

I need to have a block i can put a date/time, intials of
operator and an order progression details. Ideally i
would like to be able to be able to add mulitple
progressions to an order and have a seperate line for
each one, with the most recent progression at the top.

Hope that makes sense, Any help would be appriciated,
Thanks
Steve
Click to expand...

You could create an extra couple of tables, one containing order
progressions linked to orders, another containing your progression types:

1st Table Name: OrderProgression
Columns:
OrderProgressionID (primary key)
OrderID (The order for which the progression record belongs)
OrderProgressionTypeID (The type of order progression type - foreign key to
OrderProgressionType)
ProgressionDate (Date of order progression)
UserID (Either user initials or foreign key to a user table)

2nd Table Name: OrderProgressionType
Columns:
OrderProgressionTypeID (Primary Key)
Description (Description of order progression)
CurrentYN (Used to determine whether an OrderProgressionType is currently
available to assign to orders)


To return all orders and their progressions, assuming user and order tables
are available:

SELECT o.OrderReference, opt.Description, op.ProgressionDate, u.UserName
FROM orders o
INNER JOIN OrderProgression op on op.orderid = o.orderid
INNER JOIN OrderProgressionType opt on opt.OrderProgressionTypeID =
op.OrderProgressionTypeID
INNER JOIN Users u on u.UserID = op.UserID
ORDER BY o.OrderReference desc, op.ProgressionDate asc


To provide the data for a progression type combo box:

SELECT OrderProgressionTypeID, Description
FROM OrderProgressionType
WHERE CurrentYN = 1

Hope the above is of use.

Chris
 
Back
Top