[thelist] SQL question

lists lists at ravenstudioz.com
Sun Jan 23 22:46:04 CST 2005


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





More information about the thelist mailing list