-----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?