Query Help Again

[Stickman64]

I have posted before trying to figure this out so this time I will give more
info to help.

Say I have three programs: Alpha, Beta, Gamma. Each program has a program
manager: Ned, Red and Zed. So I set up a programs table like this:

Program Manager Bldg
Alpha Ned 1
Beta Red 2
Gamma Zed 3


So I also have a manager table as well:

Last First Position
Smith Ned Pres
Jones Red VP
Thomas Zed VP

Then I would like everyone to just be able to enter their upcoming meetings:

Program Mtg Date
Alpha PMR 8/7/08
Alpha Update 8/10/08
Beta QMR 8/1/08
Gamma PMR 7/1/08

I don't know if that has to have a separate table for each program's
meetings. I figured I could run a query to get that if I need to.

Now for the fun: I want to run a query where only the next meeting is
listed. If no meeting is scheduled, TBD would be displayed: So the end
result would look similar to this:

Program Meeting Date Manager
Beta QMR 8/1/08 Red
Alpha PMR 8/7/08 Ned
Gamma TBD TBD Zed

Is this possible? I think I may have volunteered for much more than I
wanted.

Thanks for all your help.

 

[KARL DEWEY]

First add a field to the manager table - MgrID - Autonumber - primary key.
Add a new field to program table - MgrID - Number - Long Integer - foreign
key.
Set a one-to-many relationship from manager table MgrID to the MgrID of
program table.
Fill in or run an update query for the MgrID of the program table.

Use these two queries unless you know subqueries and substitute your table
and field names --
SELECT ProgramMtg.ProjectNumber, Min(ProgramMtg.MtgDate) AS MinOfMtgDate
FROM ProgramMtg
WHERE (((ProgramMtg.MtgDate)>=Date()))
GROUP BY ProgramMtg.ProjectNumber;

SELECT PROJECT.ProjectNum, ProgramMtg.Mtg, ProgramMtg.MtgDate,
MANAGEMENT.Last, MANAGEMENT.First
FROM ((PROJECT LEFT JOIN MANAGEMENT ON PROJECT.MgrID = MANAGEMENT.MgrID)
LEFT JOIN ProgramMtg ON PROJECT.ProjectNum = ProgramMtg.ProjectNumber) LEFT
JOIN ProjNextMeet_1 ON (ProgramMtg.MtgDate = ProjNextMeet_1.MinOfMtgDate) AND
(ProgramMtg.ProjectNumber = ProjNextMeet_1.ProjectNumber)
WHERE (((PROJECT.ProjectNum)=[ProjNextMeet_1].[ProjectNumber]) AND
((ProgramMtg.MtgDate)=[ProjNextMeet_1].[MinOfMtgDate]));