[AccessD] Staging/Queueing Ideas

I've built several of these systems. The last one that I built has been the
easiest to maintain. It also allows growth (what if a new step/process is
created?).

I take it that you have an RGA header and detail table. The detail table is
all the information related to the parts/items themselves. The header table
is everything related to the RGA itself (bill to, ship to, RGA#). I tend to
put in entryDate (default = now() )and entryBy fields into just about every
table.

I have codes set for each stage of the way:
	When the call is being entered, the code is considered a failure code:
		RSU - Returned status unknown
		MLF - Unit malfunction
		RPM - Returned due to missing part
		USE - User caused fault
	When the unit is received:
		DMG - Received damaged
		RAE - Received as expected
		SEE - See notes
	When the unit is in disposition:
		RTV - Return to vendor
		CNR - Could not repeat problem
		RBD - Rebuild
		SEE - See Notes
	When sent to accounting:
		WAR - Warranty
		NOC - No Credit
		OOW - Out of warranty

I have a table (tblRGAActionTypes):
RGAACtionID (AN PK)
RGAACtion (text 20)
entryDate (datetime - Now() )
entryUserID (integer)

This table simply has my four steps as records: Entry, Receive, Disposition,
Closed

I join that table to another table (one to many) called tblRGAActionCodes:
RGAActionCodeID (AN PK)
RGAActionID (Integer FK)
ActionCode (Text 3)
ActionDescription (Text 50)
entryDate (datetime - Now() )
entryUserID (integer)

A final table , a junction table is created, which links tblRGAHeader and
tblRGAActionCodes with a many to many relationship, I call it
tblRGAHeaderActionJunct :
RGAHeaderActionJunctID (AN PK)
RGAid (Integer FK)
ActionNotes (Memo)
RGAActionCodeID (Integer FK)
entryDate (datetime - Now() )
entryUserID (integer)

I have one form for all of these steps. I have a tab control which has 3
tabs, one for all of the steps, plus two additional tabs. One for
replacement parts used to repair the item and another for the history of the
RGA. I pass a code to the form as an openarg and call a function on the
forms load event which formats the tab's controls for the step that it is
being called for.  so for instance, if I open the form with a 1 (entry mode)
my combo box's label displays the text "Failure Type" and the combo box's
rowsource is:
	SELECT
		RGAActionCodeID,
		ActionCode,
		ActionDesc
	FROM tblRGAActionCodes
	WHERE RGAActionID = Forms!frmRGA!txtformMode

The "Notes" box is there for them to type a detailed description of the step
(if needed) then a command button simply inserts the data into the
appropriate tables.

The History tab is easy, since this is a self auditing design. I have a list
box which simply selects from the tables:

SELECT
	P.MocSN AS UnitID,
	A.ActionCode AS Occurrence,
	H.RGAID AS RGA,
	HAJ.RGAActionCodeID AS Code,
	HAJ.ActionNotes AS Notes,
	HAJ.EntryDate AS [Date],
	U.FullName AS [User],
	HAJ.RGAHeaderActionID
FROM
	dbo.tblRGAHeader AS H
	INNER JOIN dbo.tblRGAHeaderActionJunct AS HAJ
		ON H.RGAID = HAJ.RGAID
	INNER JOIN dbo.tblProduct AS P
		ON H.ProductID = P.ProductID
	INNER JOIN dbo.tblRGAactionCodes AS A
		ON HAJ.RGAActionCodeID = A.RGAActionCodeID
	INNER JOIN dbo.tblRGAactionTypes AS acT
		ON A.RGAActionID = acT.RGAActionID
	LEFT OUTER JOIN dbo.tbl_sysUsers AS U
		ON HAJ.EntryUserID = U.UserID
WHERE H.RGAID = Forms!frmRGA!txtRGAnum
ORDER BY HAJ.EntryDate ASC

I have some safeguards built into the main menu buttons which check the max
status of a given RGA, so if someone tries to open an RGA in the incorrect
order, they are not allowed. This is a business rule that states the RGA
must process through all steps.

Let me know if you need any further help with this.

David McAfee

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Lavsa, Rich
Sent: Friday, November 04, 2005 7:15 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Staging/Queueing Ideas


 Hello all,

I have been throwing around the notion of building some sort of queuing
ability into an application I built.

It is an RGA (Return Goods Application).  This application tracks items
returned to the manufacturing plant, for whatever reason.  Through this
process, the RGA can be initiated by Customer service or Shipping.
Shipping will only initiate an RGA if material shows up on the dock
without any prior notification.

Anyway, the Flow is as follows..
First Customer Service gets a call from customer to return material.
They enter this request into the system, and generate a number.  This
information is then Faxed or emailed to the customer.
Second they send this material to our Warehouse (Shipping).  Shipping
then verifies what was sent back, how much was good, scrapped, and
re-inventoried.
Third Manager of Shipping reviews the returns to validate any wrong
doings by our customers (tries to avoid larger customers getting free
material) and gives their approval.
Fourth, goes back to Customer Service who then requests a credit to
customer.
Fifth, goes to accounting where another quick review takes place, and
the actual credit is applied.

So I have 5 Stages/queues.  Currently there is no way in the application
for Shipping to know what to expect, or for Customer Service to know
what shipping has passed on to Manager of Shipping for approval, etc..
I did attempt to put a RoutingStatus field in there, but this gets
filled in the first time because it's required, then after that it is
over looked..

So what I am looking for is an idea or 2 that might be effective in
producing some sort of staging or queuing ability within this
application.  I do not believe a single field with "who's next" is
adequate.  I have thought about having a date at each stage to be filled
in at the time they completed their part, but does that seem overkill?
Am I making this part way too complicated?

Thanks in advance for any ideas,

Rich
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com