Adding Multiple records using Relational Tables

[Alec Christie]

Hi All,

I currently have 3 tables, Clients, Events and Event Status as
follows:

Clients: ClientID, Name, Address
Events: EventID, Name, Date
Event Status: StatusID, EventID, ClientID, Attended

I can easily create new Events and Elients, however, when i create a
new Event, I want to add a whole load of records to the Event Status
table by counting the number of Clients and performing a While
statement. What code would I use to increment through each Client
record and add their ClientID along with the EventID to the Event
Status table?

Thanks In Advance.

Alec

[MGFoster]

Re: Adding Multiple records using Relational Tables

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off, I'd change the design of the EventStatus table to this:

EventStatus
EventID -> link to Events table
StatusID -> link to Statuses table (list of Status names)
Effective - Date data type - tells when the status took effect
Notes - Text data type - tells reason for status (if needed)

Then I'd create a new table that holds the attendees to each event:

EventAttendees
EventID -> link to Events table
ClientID -> link to Clients table
Attended - Yes/No data type

The reason I'd do the above is you were combining 2 states in 1 record
in the original EventStatus table (event status and client status at the
event). IOW, the client's attendance is not part of the event's status,
and, the event's status could change many times without the client's
status changing at all.

To populate the EventAttendees table you could run an INSERT INTO
(append) query:

PARAMETERS [Enter Event ID] Long;
INSERT INTO EventAttendees (EventID, ClientID)
SELECT E.EventID, C.ClientID
FROM Events As E, Clients As C
WHERE E.EventID = [Enter Event ID]

This will load the EventAttendees table w/ all the clients in table
Clients.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQREX64echKq OuFEgEQJgJACguT uktJ+U4zSJPtldZ BaQ8tN0Ir0AoKkp
9cbjs5cibfSwpDs Ff6b+YjS7
=AlWo
-----END PGP SIGNATURE-----


Alec Christie wrote:
[color=blue]
> Hi All,
>
> I currently have 3 tables, Clients, Events and Event Status as
> follows:
>
> Clients: ClientID, Name, Address
> Events: EventID, Name, Date
> Event Status: StatusID, EventID, ClientID, Attended
>
> I can easily create new Events and Elients, however, when i create a
> new Event, I want to add a whole load of records to the Event Status
> table by counting the number of Clients and performing a While
> statement. What code would I use to increment through each Client
> record and add their ClientID along with the EventID to the Event
> Status table?[/color]