By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Adding Multiple records using Relational Tables

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQREX64echKqOuFEgEQJgJACguTuktJ+U4zSJPtldZBaQ8t N0Ir0AoKkp
9cbjs5cibfSwpDsFf6b+YjS7
=AlWo
-----END PGP SIGNATURE-----
Alec Christie wrote:
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?


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.