I'm not sure if I've made myself clear, but I have 3 tables, of which A is a primary key for B and C, and B is a primary key for C. That is, I have companies (A), which are the organisers of events (B), which have speakers/performers (C), and each speaker works for any of the companies in A (often not the one that organises the event). Each company also has an integer column indicating which industry it is in. What I want to do is execute a query which provides all the info from table B (events), as well as a count of how many of the speakers (C) work for companies in a given industry (let's say a constant 1)
I'm testing in Access 2003 for the time being, with the goal to putting it into MySQL. If possible I want to avoid joins as I think they are ugly and clumsy. I currently have the following:
SELECT Events.Title, Company.[Company Name] AS "Organis", People.Surname, Company.[Company Name] AS "Speak"
FROM Events, Company, People
WHERE Events.Organiser = Organis.ID AND
Events.ID = People.Last_speaker AND
Speak.Company = Company.ID
ORDER BY Events.Organiser;
But the second column of this is always listing the same company (also, Access prompts me for two numbers). Obviously once I get the speaker's company and the organiser's company, it will probably be straightforward to get the speaker's industry and count those up for each event, but I seem to be stuck with a syntax error whenever I try to proceed.
Do I just have a bad database design?
Thanks for any help.
[edited by: R1chard at 4:51 pm (utc) on Jan. 25, 2007]
