Database Program

[Guest_imported]

Hello All -

I'm rather new so please excuse what may be a simple question, but we all start somewhere.

I'm trying to put together a program in VB that will view and edit a database. The database contains information from my church on what message was spoken when and who delivered the message. The problem is that a message may have been delivered by more than one person, sometimes up to three in fact.

I have seperated the message information in one table and the speaker information in another. I've assigned each speaker a number that should be related to that speaker.
In the message table there are fields for "SpeakerNum", "Speaker2Num", and "Speaker3Num". The problem come in when I'm coming up with an SQL statement that will join all three of the speakers with numbers.

The Data Controls RecordSource property is now, "SELECT OML.[Date], OML.[Notes], OML.[MessageNum], OML.[MessageTitle], SpeakerNameNum.[Speaker] FROM OML INNER JOIN SpeakerNameNum ON SpeakerNameNum.[SpeakerNum] = OML.[SpeakerNum]". This will get me the first speaker, but not the second or third.

Does anyone know of any ways to join one field of a table to THREE fields in another.

Thanks A Ton!!!!

 

[williamu]

Yes this is correct so you need to join on all three columns.


William
Software Engineer
ICQ No. 56047340

 

[BobWman]

I can't test it without your data, but the solution is something like this:

SELECT OML.[Date], OML.[Notes], OML.[MessageNum], OML.[MessageTitle], SN1.[Speaker],SN2.[Speaker],SN3.[Speaker]
FROM OML
INNER JOIN SpeakerNameNum SN1 ON SN1.[SpeakerNum] = OML.[SpeakerNum]
INNER JOIN SpeakerNameNum SN2 ON SN2.[SpeakerNum] = OML.[SpeakerNum2]
INNER JOIN SpeakerNameNum SN3 ON SN3.[SpeakerNum] = OML.[SpeakerNum3]

 

[Guest_imported]

Would it work the same if I added two more Data Controls set for the different speakers? If so, how would I keep all of three of the Data controls on the same record?

I tried the above code and still received errors.

 

[BobWman]

This time I have verified that the sql statement is correct. I have also verified that it will work with an ADO data control. It will return the 3 speakers into textboxes, but you cannot update them. The textboxes are tied to NAME1-3. A left join is used instead of an inner join.

If you want to make the names updateable, you will probably need to use a (data bound) combo box. Better yet, learn how to code without the data control.

Hope this helps.

SELECT OML.[Date], OML.[Notes], NAME1 = SN1.[SpeakerName],NAME2 = SN2.[SpeakerName],NAME3 = SN3.[SpeakerName]
FROM OML
Left JOIN SpeakerNameNum SN1 ON SN1.[SpeakerNum] = OML.[SpeakerNum]
Left JOIN SpeakerNameNum SN2 ON SN2.[SpeakerNum] = OML.[SpeakerNum2]
Left JOIN SpeakerNameNum SN3 ON SN3.[SpeakerNum] = OML.[SpeakerNum3]

 

[AdamIT]

This might be a little bit over-doing it, but:

You should setup a Header-LineItem structure. This would involve three tables. A Header table, a LineItem table, and a Speaker table. Here is an example of the fields in each table:

------------------------------
Header.ID
Header.Date
Header.Notes
Header.MessageNum
Header.MessageTitle

LineItem.ID
LineItem.HeaderID
LineItem.SpeakerID

Speaker.ID
Speaker.Name
Speaker.Position
Speaker.Age (etc. etc)
------------------------------

Field Descriptions:
All the .ID fields are unique AutoNumber fields, meaning that it is just a number that increments each time you add a record. This is where all your linking will take place.

Links:
LineItem.HeaderID = Header.ID
LineItem.SpeakerID = Speaker.ID

How It Works: For each message that is delivered, you would add an entry to your Header table with the appropriate information. Then, for each Speaker that is delivering that message, add an entry to your LineItem table with that Speaker's ID.

Benifits: This gives you the ability to have an infinit number of speakers associated with a single message, otherwise known as a one-to-many relationship. It also saves space when you only have 1 speaker, since you wouldn't need the extra 2 columns.

Downfalls: Need to use a grid control for the list of speakers rather than three simple text boxes, which can get tricky.

If you're really interested, do some research about Database Normalization. This will explain in more detail what I have attempted to layout.

Take care,
Adam

PS: why not just put their names, rather than their numbers, into the OML table. then, you wouldn't need any linking

 

[williamu]

BTW The proper term for the above is called an Intersection Table.

William
Software Engineer
ICQ No. 56047340

 

[Guest_imported]

What is the VB6 syntax when you want to convert a t-sql statement of this kind to msaccess2000 using ADODB?

SELECT title_ID, title, publishers.pub_id, pub_name
FROM publishers INNER JOIN titles
ON publishers.pub_id = titles.pub_id