help with nestled query

[Guest]

hi

I am new to access and struggling a bit with a query.

I have three tables, one containing members with details (phone etc)
next table(meetings) contains all the available meetings that have been held
this year and the location of those meetings

The third one(meeting details) contains details on the meetings, such as who
attended and how many questions they asked

Some people have attended all meetings, some have attended none, some have
attended some

The members table is linked via memberId(Unique) to the "Meeting details"
table and the "meetings" table is linked via MeetingId to the "meeting
details" table

I want to create a query that results in a list of all members (regardless
if they have attended a meeting or not). At which meeting they asked the most
questions, how many questions that was and where that meeting was held

I'm stuck; I hope I've made my question clear, any suggestions on how to go
about getting the reult I want?

appreciate any suggestions on how to solve this, or where to find an answer

 

[John Spencer]

Perhaps something like the following.

SELECT Members.LastName, Members.FirstName,
Meetings.Location,
MeetingDetails.NumberOfQuestions

FROM Members LEFT JOIN MeetingDetails
ON Members.MemberID =MeetingDetails.MemberID
LEFT JOIN Meetings ON MeetingDetails.MeetingID = Meetings.MeetingID
WHERE MeetingDetails.NumberofQuestions =
(SELECT Max(MeetingDetails.NumberOfQuestions)
FROM MeetingDetails
WHERE MeetingDetails.MemberID = Members.MemberID)

You could also do this with a series of queries. If you need instructions for
this, can you post back with whether or not you can build your query in SQL text
or are you only able to use the query grid (at this time).

FirstQuery - Get the maximum number of questions asked by each member
Second query - Use first query and the meetingDetails table to get the other
information you need from Meeting details
Third Query - Use the second query along with the members and meetings table to
build the final query. Your joins should show ALL records in the Members table
and only those matching in the other tables.

Join Members to second query and second query to meetings.

 

[Marshall Barton]

I have three tables, one containing members with details (phone etc)
next table(meetings) contains all the available meetings that have been held
this year and the location of those meetings

The third one(meeting details) contains details on the meetings, such as who
attended and how many questions they asked

Some people have attended all meetings, some have attended none, some have
attended some

The members table is linked via memberId(Unique) to the "Meeting details"
table and the "meetings" table is linked via MeetingId to the "meeting
details" table

I want to create a query that results in a list of all members (regardless
if they have attended a meeting or not). At which meeting they asked the most
questions, how many questions that was and where that meeting was held

Try something like this air code:

SELECT Members.membername,
Meetings.location,
MeetingDetails.questions,
IIf(MeetingDetails.questions =
(SELECT Max(X.questions)
FROM MeetingDetails As X
WHERE Meetings.MeetingID = X.MeetingID),
"Most", "") As MostIndicator
FROM (Members
LEFT JOIN MeetingDetails
ON Members.MemberID = MeetingDetails.MemberID)
LEFT JOIN Meetings
ON Meetings.MeetingID = MeetingDetails.MeetingID

 

[Guest]

Thank you so much for your help and quick reply,
(I got Marshalls code to work fine, Johns though, complained about an
operator missing, so instead of trying to figure out why, I went with
Marshalls solution)

this is almost perfect, one slight snag though
I realise I wasnt clear enough in my original post

The result I need
is a list of ALL UNIQUE Membernames, next column to contain the place of the
meeting where they asked the most questions and in the next column, the
number of questions at that particular meeting, if they have asked the same
amount of questions in several meetings, any meetinglocation would do

Sorry to bug you, but, with your help, I am so close to solving something
that has bugged me for weeks

Thanks

 

[Marshall Barton]

Thank you so much for your help and quick reply,
(I got Marshalls code to work fine, Johns though, complained about an
operator missing, so instead of trying to figure out why, I went with
Marshalls solution)

this is almost perfect, one slight snag though
I realise I wasnt clear enough in my original post

The result I need
is a list of ALL UNIQUE Membernames, next column to contain the place of the
meeting where they asked the most questions and in the next column, the
number of questions at that particular meeting, if they have asked the same
amount of questions in several meetings, any meetinglocation would do

Now I am confused. What does "most" mean? I thought it was
where the member asked more questions than anyone else at a
meeting. Now, it sounds like you what to know which meeting
a member asked more questions than he ask at other meetings,
even if someone else asked more questions at all the
meetings.

I think John's query was aimed more at the latter and may
already be the answer you are looking for.