[thelist] SQL question

[Ken Schaefer]

It is always helpful if you can post some sample data, and also what you want
the final output to be.

That said, from what I understand of your query:

a) you have projects
b) you have generic Event Types (such as "lease signed")
c) you have a table that links Projects to Event Types, along with the date
that the event occurred.

However, there can be multiple records in (c) for a given project and a given
Event Type, because the date can be revised. You think you can't use MAX()
because the latest date for the event occurring might not be the most
up-to-date estimate of the date the event will occur on. However, given that
you have input the date that the record was created, I think you should use
MAX() on that field:

SELECT
	MAX(a.DateCreated)
	, a.EventDateForecast
	, b.EventType
FROM
	ProjectsEvents AS a
INNER JOIN
	EventTypes AS b
GROUP BY
	a.EventTypeID


Cheers
Ken

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On
: Behalf Of lists
: Sent: Monday, 24 January 2005 3:46 PM
: To: thelist at lists.evolt.org
: Subject: [thelist] SQL question
: 
: I am building a database (in Access), and I am really deficient in creating
: anything more complicated than the simplest of queries, so I hope to get
: some suggestions or assistance if possible.
: 
: I am building an application that tracks about 40 different project dates.
: We want to track the revisions of each date. We enter forecast dates long
: before the event occurs, and change this as our forecasting changes, or if
: the actual varies from our plan. Revised dates can be earlier than original
: forecast dates.
: 
: I have created two tables. The "Project" table has columns for project
: number and other basic project information.
: The "Event" table has has an auto-incrementing number column as a unique
: key, a project number column as foreign key, a column to hold the date we
: input, a column containing the date the record was created, and a date type
: column.
: 
: The date type column is a foreign key linking to the "Event Type" table
: which lists all the types of event for which we need to enter dates. These
: are things like, Lease Signed, Documents Created, etc. There are about 40
: different types of dates.
: 
:  I also created a column that contains one of the following values
: (original/revised/actual) I am starting to think this last column is
causing
: some of my confusion.
: 
: I want to be able to write a query that gives me the last date entered for
: each of the various date types for each project. I can't use the max of the
: date in the date event field, because the last entered date might be an
: earlier date than the original date we entered.
: 
: I hope this is a clear enough explanation of my question.
: 
: Thanks in advance for any suggestions.
: 
: Bettina May