DATABASE ACCESS

Discussion in 'ASP General' started by IC1(SW/AW), Oct 31, 2006.

  1. IC1(SW/AW)

    IC1(SW/AW) Guest

    I am trying to create a unique type of registration form for my job
    upcoming Holiday Christmas
    Party. There will be 60 tables for seatings with 10 people max at
    table.

    How can I write some codes to support this? I need to be able to see
    the number of
    people who are seating and any available seats at a given table. How
    can I set my database
    and pull the information to give what I want.
    IC1(SW/AW), Oct 31, 2006
    #1
    1. Advertising

  2. With a lookup table.

    DDL:
    CREATE TABLE Person (
    PersonID smallint primary key identity,
    PersonName varchar(100) NOT NULL DEFAULT ''
    )

    CREATE TABLE PartyTable (
    PartyTableID tinyint primary key identity,
    TableName varchar(20) NOT NULL DEFAULT ''
    )


    CREATE TABLE PTLookup (
    PersonID smallint foreign key references Person(PersonID),
    PartyTableID tinyint foreign key references PartyTable(PartyTableID)
    )
    GO
    SET IDENTITY_INSERT PartyTable ON
    DECLARE @i tinyint; SET @i = 1
    WHILE @i < 11
    BEGIN
    INSERT INTO PartyTable (
    PartyTableID, TableName
    ) VALUES (
    @i, 'Table ' + CAST(@i as char)
    )
    SET @i=@i+1
    END




    DIAGRAM:

    Table:
    PartyTableID (PK)--.
    PartyTableName `.
    \ PTLookup:
    `-----PartyTableID (FK)
    ,--------PersonID (FK)
    /
    PersonID (PK)-----'
    PersonName



    QUERIES:
    Find people sitting at a specified table:

    SELECT PersonName FROM Person
    INNER JOIN PTLookup
    ON Person.PersonID = PTLookup.PersonID
    INNER JOIN PartyTable
    ON PTLookup.PartyTableID = PartyTable.PartyTableID
    WHERE
    PartyTable.PartyTableID = 3


    Get a count at each table:
    SELECT
    PartyTable.TableName, COUNT(PTLookup.PersonID)
    FROM
    PartyTable INNER JOIN PTLookup
    ON PartyTable.PartyTableID = PTLookup.PartyTableID
    GROUP BY
    PartyTable.TableName


    Ray at work




    "IC1(SW/AW)" <> wrote in message
    news:...
    >I am trying to create a unique type of registration form for my job
    > upcoming Holiday Christmas
    > Party. There will be 60 tables for seatings with 10 people max at
    > table.
    >
    > How can I write some codes to support this? I need to be able to see
    > the number of
    > people who are seating and any available seats at a given table. How
    > can I set my database
    > and pull the information to give what I want.
    >
    Ray Costanzo [MVP], Oct 31, 2006
    #2
    1. Advertising

  3. IC1(SW/AW) wrote:
    > I am trying to create a unique type of registration form for my job
    > upcoming Holiday Christmas
    > Party. There will be 60 tables for seatings with 10 people max at
    > table.
    >
    > How can I write some codes to support this? I need to be able to see
    > the number of
    > people who are seating and any available seats at a given table. How
    > can I set my database
    > and pull the information to give what I want.


    If Ray's DDL generates errors when you try it, you should tell us what
    database you are using. He provided Transact-SQL code for SQL Server. it
    won't work for other types of databases.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Oct 31, 2006
    #3
  4. IC1(SW/AW)

    Slim Guest

    "IC1(SW/AW)" <> wrote in message
    news:...
    >I am trying to create a unique type of registration form for my job
    > upcoming Holiday Christmas
    > Party. There will be 60 tables for seatings with 10 people max at
    > table.
    >


    Thanks but I'm going away at Christmas.


    > How can I write some codes to support this? I need to be able to see
    > the number of
    > people who are seating and any available seats at a given table. How
    > can I set my database
    > and pull the information to give what I want.
    >
    Slim, Oct 31, 2006
    #4
  5. IC1(SW/AW)

    IC1(SW/AW) Guest

    I will be using microsoft access as my database. Will these code work
    with access.

    Bob Barrows [MVP] wrote:
    > IC1(SW/AW) wrote:
    > > I am trying to create a unique type of registration form for my job
    > > upcoming Holiday Christmas
    > > Party. There will be 60 tables for seatings with 10 people max at
    > > table.
    > >
    > > How can I write some codes to support this? I need to be able to see
    > > the number of
    > > people who are seating and any available seats at a given table. How
    > > can I set my database
    > > and pull the information to give what I want.

    >
    > If Ray's DDL generates errors when you try it, you should tell us what
    > database you are using. He provided Transact-SQL code for SQL Server. it
    > won't work for other types of databases.
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    IC1(SW/AW), Oct 31, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MSDN
    Replies:
    0
    Views:
    662
  2. Fernando Lopes
    Replies:
    0
    Views:
    839
    Fernando Lopes
    Mar 29, 2005
  3. JC
    Replies:
    3
    Views:
    563
    =?ISO-8859-2?Q?Dra=BEen_Gemi=E6?=
    Jun 19, 2006
  4. Sree
    Replies:
    8
    Views:
    294
    Netocrat
    Dec 19, 2005
  5. Tony Johansson
    Replies:
    7
    Views:
    475
    Gregory A. Beamer
    Dec 23, 2009
Loading...

Share This Page