DATABASE ACCESS

I

IC1(SW/AW)

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.
 
R

Ray Costanzo [MVP]

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
 
B

Bob Barrows [MVP]

IC1(SW/AW) said:
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.
 
S

Slim

IC1(SW/AW) said:
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.
 
I

IC1(SW/AW)

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top