I am making a system that agendizes meetings. Each meeting can have up to three items agendized.
I want to use a combobox to select the meeting date because these meetings are held relatively sporadically.
I have a one column table for the meeting dates. I'm working with highly confidential information, so I am masking to the bare minimum.
It's structured like:
Meeting Date
[Date (short date format)]
In the agenda item table, there is a Meeting Date combobox that is fed by the Meeting Date table. This works as expected, users click in the combobox and dates appear in the list.
I want to make two improvements.
Improvement 1:
When the date is in the past from the meeting date database, it is removed from the combobox selection. (I'm okay if the date is just deleted from the meeting date database since the dates will be stored in the agenda item database, but I would prefer to hide in case I need to refer back for reporting).
Improvement 2:
If there are three items agendized for the date (i.e., the date exists three times in the agenda item table - meeting date column), the date is not selectable (hidden/disabled) in the combobox. This way if we cancel an item, the date reappears in the list.
I would provide code, but I don't know where to start. I considered a query for the count part, but I don't know how to link that back.
Advise not to use spaces in naming convention.
only list dates that are on or after current date?
only select dates that don't already have 3 agenda items?
Need to build an aggregate query that counts agenda items and use that query in a LEFT JOIN to Meetings table.
SELECT * FROM Meetings LEFT JOIN (SELECT MeetingDate, Count(*) AS Cnt FROM AgendaItems GROUP BY MeetingDate) AS C
ON Meetings.MeetingDate=C.MeetingDate
WHERE MeetingDate>Date() AND Nz(Cnt,0)<3;