RE: How to make hierarchical structure

Valued Member

Points: 51

June 25, 2013 at 11:58 pm

excuse me the problem of ddl was about (IDENTITY) that used previous values when we inserting data, so i change the value to (1) and now is ok when we run DDL like below, sample data also is ok now.

now my question is how i can add fields of parent and position for this query with returning parent and position value of each field.

before i have seen a sample access database that uses parent & position of each field for populating tree view.

i asked my question in many forums about how i can have parent and position value of each field/node but i did not get answer.

"http://www.access-programmers.co.uk/forums/showthread.php?t=170577"

i read some where in google we have to use union query and after adding parent & position value but there is not any sample about how we have to do?

thanks in advanced.

CREATE TABLE [dbo].[tblDocuments](

[Originator] [nvarchar](255) NOT NULL,

[Document Type] [nvarchar](255) NOT NULL,

[Unit] [nvarchar](255) NOT NULL,

[EQ-IN Identify/Material Code] [nvarchar](255) NOT NULL,

[Sequential No] [nvarchar](50) NULL,

[DocId] [int] IDENTITY(1,1) NOT NULL,

[DocumentNo] [nvarchar](200) NOT NULL,

[TITLE] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_tblDocuments_1] PRIMARY KEY CLUSTERED

(

[DocId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[tbltransmittalNo] Script Date: 06/25/2013 23:56:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbltransmittalNo](

[TRANSMITTAL] [nvarchar](200) NOT NULL,

[TransID] [int] IDENTITY(1,1) NOT NULL,

[RevDATE] [datetime] NULL,

[Originator Code] [nvarchar](50) NULL,

[Originator Location] [nvarchar](50) NULL,

[Recipient Code] [nvarchar](50) NULL,

[Recipient Location] [nvarchar](50) NULL,

[Serial No] [nvarchar](50) NULL,

[Subject] [nvarchar](50) NULL,

[Originator] [nvarchar](50) NULL,

[Reciever] [nvarchar](50) NULL,

[Attention] [nvarchar](50) NULL,

CONSTRAINT [PK_tbltransmittalNo_1] PRIMARY KEY CLUSTERED

(

[TransID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[tblTransmittalls] Script Date: 06/25/2013 23:57:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblTransmittalls](

[DocID] [int] NOT NULL,

[REV] [nvarchar](255) NULL,

[TransId] [int] NOT NULL,

[Pages] [nvarchar](50) NULL,

[Class] [nvarchar](50) NULL,

[Size] [nvarchar](50) NULL,

[PurposeofIssue] [nvarchar](50) NULL,

[DefaultForDocument] [bit] NULL,

CONSTRAINT [PK_tblTransmittalls_1] PRIMARY KEY CLUSTERED

(

[DocID] ASC,

[TransId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblTransmittalls] WITH CHECK ADD CONSTRAINT [FK_tblTransmittalls_tblDocuments] FOREIGN KEY([DocID])

REFERENCES [dbo].[tblDocuments] ([DocId])

GO

ALTER TABLE [dbo].[tblTransmittalls] CHECK CONSTRAINT [FK_tblTransmittalls_tblDocuments]

GO

ALTER TABLE [dbo].[tblTransmittalls] WITH NOCHECK ADD CONSTRAINT [FK_tblTransmittalls_tbltransmittalNo] FOREIGN KEY([TransId])

REFERENCES [dbo].[tbltransmittalNo] ([TransID])

GO

ALTER TABLE [dbo].[tblTransmittalls] NOCHECK CONSTRAINT [FK_tblTransmittalls_tbltransmittalNo]

GO

ALTER TABLE [dbo].[tblTransmittalls] ADD CONSTRAINT [DF_tblTransmittalls_DefaultForDocument] DEFAULT ((0)) FOR [DefaultForDocument]

GO

/****** Object: Table [dbo].[tblTranstoCon] Script Date: 06/25/2013 23:59:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblTranstoCon](

[TRANSMITtoConId] [int] IDENTITY(1,1) NOT NULL,

[TRANSMITtoCon] [nvarchar](50) NOT NULL,

[Oldtransmittocon] [nvarchar](255) NULL,

[RevDATE] [datetime] NULL,

[Originator] [nvarchar](255) NULL,

[old Recipient] [nvarchar](255) NULL,

[Recipient] [nvarchar](255) NULL,

[Serial No] [nvarchar](255) NULL,

[Subject] [nvarchar](255) NULL,

[Type Code] [nvarchar](255) NULL,

[Copies] [nvarchar](255) NULL,

[Remark] [nvarchar](255) NULL,

CONSTRAINT [PK_tblTranstoCon_1] PRIMARY KEY CLUSTERED

(

[TRANSMITtoConId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[tblDocTranstoCon] Script Date: 06/26/2013 00:00:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblDocTranstoCon](

[DocID] [int] NOT NULL,

[TransId] [int] NOT NULL,

[TRANSMITtoConId] [int] NOT NULL,

CONSTRAINT [PK_tblDocTranstoCon] PRIMARY KEY CLUSTERED

(

[DocID] ASC,

[TransId] ASC,

[TRANSMITtoConId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblDocTranstoCon] WITH CHECK ADD CONSTRAINT [FK_tblDocTranstoCon_tblTransmittalls] FOREIGN KEY([DocID], [TransId])

REFERENCES [dbo].[tblTransmittalls] ([DocID], [TransId])

GO

ALTER TABLE [dbo].[tblDocTranstoCon] CHECK CONSTRAINT [FK_tblDocTranstoCon_tblTransmittalls]

GO

ALTER TABLE [dbo].[tblDocTranstoCon] WITH CHECK ADD CONSTRAINT [FK_tblDocTranstoCon_tblTranstoCon] FOREIGN KEY([TRANSMITtoConId])

REFERENCES [dbo].[tblTranstoCon] ([TRANSMITtoConId])

GO

ALTER TABLE [dbo].[tblDocTranstoCon] CHECK CONSTRAINT [FK_tblDocTranstoCon_tblTranstoCon]

GO

---------------------------------------------------------------

INSERT INTO [dbo].[tblDocuments]

([Originator]

,[Document Type]

,[Unit]

,[EQ-IN Identify/Material Code]

,[Sequential No]

,[DocumentNo]

,[TITLE])

VALUES

('Abad','dw','100','1441','001','doc-001','test1'),

('paya','dw','140','1441','005','doc-005','test2')

GO

INSERT INTO [dbo].[tbltransmittalNo]

([TRANSMITTAL]

,[RevDATE]

,[Originator Code]

,[Originator Location]

,[Recipient Code]

,[Recipient Location]

,[Serial No]

,[Subject]

,[Originator]

,[Reciever]

,[Attention])

VALUES

('tt-0002', null,null,null,null,null,null,null,null,null,null)

GO

-------------------------------------------------------------------

INSERT INTO [dbo].[tblTransmittalls]

([DocID]

,[REV]

,[TransId]

,[Pages]

,[Class]

,[Size]

,[PurposeofIssue]

,[DefaultForDocument])

VALUES

(1,'0',1,null,null,null,null,null),

(2,'0',1, null,null,null,null,null)

GO

------------------------------------------------------------

INSERT INTO [dbo].[tblTranstoCon]

([TRANSMITtoCon]

,[Oldtransmittocon]

,[RevDATE]

,[Originator]

,[old Recipient]

,[Recipient]

,[Serial No]

,[Subject]

,[Type Code]

,[Copies]

,[Remark])

VALUES

('con-0003',null,null,null,null,null,null,null,null,null,null),

('con-0005',null,null,null,null,null,null,null,null,null,null),

('con-0007',null,null,null,null,null,null,null,null,null,null)

GO

INSERT INTO [dbo].[tblDocTranstoCon]

([DocID]

,[TransId]

,[TRANSMITtoConId])

VALUES

(1,1,1),

(1,1,3),

(2,1,2)

GO

SELECT dbo.tblDocuments.DocumentNo, dbo.tbltransmittalNo.TRANSMITTAL, dbo.tblTranstoCon.TRANSMITtoCon

FROM dbo.tblDocuments

left JOIN dbo.tblTransmittalls ON dbo.tblDocuments.DocId = dbo.tblTransmittalls.DocID

left JOIN dbo.tbltransmittalNo ON dbo.tblTransmittalls.TransId = dbo.tbltransmittalNo.TransID

left JOIN dbo.tblDocTranstoCon ON dbo.tblTransmittalls.DocID = dbo.tblDocTranstoCon.DocID AND

dbo.tblTransmittalls.TransId = dbo.tblDocTranstoCon.TransId

left JOIN dbo.tblTranstoCon ON dbo.tblDocTranstoCon.TRANSMITtoConId = dbo.tblTranstoCon.TRANSMITtoConId