473,320 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding Multiple records using Relational Tables

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
1 2071
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Steve Jorgensen | last post by:
Terminology question: Is there a term for a set of records related directly or indirectly by key value in several tables? For example, a single invoice record and its line item records -or- a...
8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
2
by: IanCraft | last post by:
I am new to Access 2002 and have a questions regarding using a form for data entry. I have 15 items I need to keep track of that are listed individually on a form as follows: Item#1Name ...
4
by: Gobi | last post by:
Hello, I have a Database with lists of Clients in each. Every year a new tables is created with the naming convention "CloseYear" ie close1999, close2000 There are tables from this year back to...
20
by: p175 | last post by:
Hi people, I have a stored procedure that creates many Global temporary session tables. Into each of these tables go the results of various processing using relational division all keyed and...
4
by: bpneary1 | last post by:
First of all, I want to thank everyone who posts messages in this group. I am very new to VB and developing full-blown database apps, and I have learned a great deal by reading these posts, so I...
8
by: Mark Welch | last post by:
I am at a complete loss on how to do this. I am using Microsoft Access 2000, to implement a preliminary version of a reasonably modest relational database. I have two Access Tables (imported...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.