Order progression - help!

[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

 

[Chris Howarth]

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

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 rderReference, 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 rderReference 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