Good morning,
I have a database (Tests) that has the following fields:
Doc_ID (Text)
Doc_Title (Text)
Test1 (yes/no)
Test2 (yes/no)
Test3 (yes/no)
Test4 (yes/no)
Test5 (yes/no)

Here is my dilema, I do not want to put the same Doc_ID (eg. doc_123) for 3 different tests to be run. (eg. Doc_123 needs Test 1, Test 3, and Test 5 to be run) Is there a SQL statement or maybe a script I can write to call to the Database for that one record to display the results 3 times on my webpage? (Instead of me creating a new Doc_Id for the same record 3 times in the Database) Does this make any sense?
thank you for your help.

Yes, there is way and it mostly has to do with the way you design the database. You could create separate tables for each type of data, one table for docs, one table for tests and an intermediary table to hold docs/tests relationships. You'll be able to have a scalable application and be able to add tests to the tests table without having to add another column to the docs table everytime there is a new test.


CREATE TABLE [Docs] (
    [DocID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Doc_Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Status] [bit] NOT NULL CONSTRAINT [DF_Docs_Status] DEFAULT (1),
    CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
    (
        [DocID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Tests] (
    [TestID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Test_Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Status] [bit] NOT NULL CONSTRAINT [DF_Tests_Status] DEFAULT (1),
    CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED
    (
        [TestID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [DocTests] (
    [DocID] [int] NOT NULL ,
    [TestID] [int] NOT NULL ,
    CONSTRAINT [FK_DocTests_Docs] FOREIGN KEY
    (
        [DocID]
    ) REFERENCES [Docs] (
        [DocID]
    ),
    CONSTRAINT [FK_DocTests_Tests] FOREIGN KEY
    (
        [TestID]
    ) REFERENCES [Tests] (
        [TestID]
    )
) ON [PRIMARY]
GO

Populate the Docs and Tests tables and then you can populate the Doctests table using the primary keys/ID column info. Then use the query below:


SELECT Docs.Doc_Title, Tests.Test_Title
FROM DocTests INNER JOIN
                      Docs ON DocTests.DocID = Docs.DocID INNER JOIN
                      Tests ON DocTests.TestID = Tests.TestID
--To look at all of the Docs and Tests or add the WHERE statement
WHERE (Docs.DocID = 1)

perfecto!!! thanks for your help