Possible SQL Statement

[Guest_imported]

Hi -

I'm working on a program that pulls data from an Access program. It's a database of messages that were spoken at my church, and not every message has only one speaker. Some, for example, have up to three speakers. I've assigned all speakers a number (kept in a seperate table) in order to only keep the actual names in one place. So in my main table I have a field for SpeakerNum, Speaker2Num, and Speaker3Num.

What the statement needs to do is join the names to, not one field in the main table, but THREE.

I can get it to work with ONE name, where I lose it is coming up with the second and third names.

This is the SQL statement that I'm using now, and like I said it works with one name;

SELECT OML.[Date], OML.[MessageNum], OML.[MessageTitle], SpeakerNameNum.[Speaker] FROM OML INNER JOIN SpeakerNameNum ON SpeakerNameNum.[SpeakerNum] = OML.[SpeakerNum], OML.[Speaker2Num]

Any help would just make my entire week!...Thanks in advance

 

[crufty]

Simplest way to do this is like so:

Note: This is generic SQL, please translate into the SQL for your DB

SELECT t1.item1, table 2.column1, table3.column1
FROM tableA t1, tableB t2, tableB t3, tableB t4
WHERE t1.column1 = t2.column1
AND t1.column2 = t3.column1
AND t1.column3 = t4.column1

The key is to name the table that holds the names multiple times in the from clause (with different aliases, of course) and then join to each copy of the table to get the various different names.

If you have trouble translating the generic SQL into Access (I don't speak Access SQL), I'm sure someone in the Access forum will help.

 

[tlbroadbent]

I recommend posting Access query questions in forum701 -"Microsoft: Access Queries and JET SQL" because Access is not totlaly ANSI compliant.

You should modify your query as follows.

SELECT
OML.[Date], OML.[MessageNum],
OML.[MessageTitle],
s1.[Speaker] As Speaker1,
s2.[Speaker] As Speaker2,
s3.[Speaker] As Speaker3
FROM OML
INNER JOIN SpeakerNameNum s1
ON OML.[SpeakerNum]=s1.[SpeakerNum]
INNER JOIN SpeakerNameNum s2
ON OML.[SpeakerNum2]=s2.[SpeakerNum]
INNER JOIN SpeakerNameNum s3
ON OML.[SpeakerNum3]=s3.[SpeakerNum]

The database is not normalized. A table shouldn't have three speaker columns on a row. If you can, redesign the database. You should have tables similar to the following.

Table: OML
Columns: [Date], MessageNum, MessageTitle

Table: SpeakerNameNum
Columns: SpeakerNum, Speaker

Table OMLSpkrXref
Columns: XrfID, MessageNum, SpeakerNum

You could then create a JOIN queries to return all speakers by date, topic, keyword. You could also return a messages deleiverd by a particular speaker and so forth.

Example: Select all messages and speakers on the topic of "charity."

Select s.Speaker, o.[Date], o.MessageTitle
From OML As o
Inner Join OMLSpkrXref As x
On o.MessageNum=x.MessageNum
Inner Join Speakers As s
On x.SpeakerNum=s.SpeakerNum
Where o.MessageTitle Like "*Charity*" Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.