Pivot / Cross Table Style Data Input Form

See more:

VB

SQL-Server

ADO.NET

forms

, +

Hello all,

I hope somebody may be able to help me with my problem.

In a nutshell I am required to develop a Pivot / Cross Table Style Data Input Form.

The description above might be enough for a response but if not i have tried to explain the problem in detail below.

The Tables in question are:

Table 1 = ValveItem(ValveId, Qty, etc....)
Table 2 = ExtraRequirement(ExtraRequirementID, Qty, IsKeyRequirement, etc....)
Table 3 = KeyRequirement_Valve_ExtraItems(ValveID_FK, ExtraRequirementID_FK, Qty)

A ValveItem is a line item within a contract and contains detail about the Valve and Qty of valves ordered.

ExtraRequirements are additional items that MAY be related to the ValveItem. For example special testing, Delivery conditions etc. ExtraRequirements have a QTY.

Of all the ExtraRequirements for that CONTRACT.. SOME MAY be flagged as a KeyRequirement.
i.e ExtraItem.isKeyRequirement = True

ALL ValveItems within a Contract have the KeyRequirement associated with it. A Qty value for the KeyRequirement must be entered even if it is Zero (0).

ExtraRequirement QTY and KeyRequirement QTY may not be the same value. (Not ideal but nothing i can do about that :) )

A Pivot / Cross Table Style Data Input Form is required to list all Valves as a ROWS against the ExtraItem(KeyRequirements) as a columns.

The quantities QTY required for the KeyRequirements is the Input Data.

I have tried to illustrate this below.

___________¦ ExtraItem (KeyRequirement)
ValveItem__¦ ExtraItem1 ¦ ExtraItem2 ¦ ExtraItem3 ¦

ValveItem1_¦_____5______¦_____1______¦_____5______¦

ValveItem2_¦_____3______¦_____3______¦_____1______¦

ValveItem3_¦_____5______¦_____5______¦_____5______¦

The KeyRequirment_Valve_ExtraItems link table stores the unqiue Qty's for the KeyRequirement.


I hope i have been able to explain the problem in enough detail but not without confusing the hell out anybody :)

I look forward to your responses.

Thank you all for your time in advance

Best Regards

Alex