[thelist] SQL question

[lists]

Ken,

Thank you for looking at my question. Access wouldn't accept that query
syntax-it complained vaguely about an error in the syntax of the from
clause. I tried to reformulate my question, and in doing so, realised that
it was much simpler than I had thought, as you pointed out, I just really
needed to get the MAX on the date created field for each eventid for each
project number, and I only needed to query the Events table. I ended up with


SELECT tblEvent.ProjectNumber, tblEvent.EventTypeID,
Max(tblEvent.DateCreated) AS MaxOfDateCreated
FROM tblEvent
GROUP BY tblEvent.ProjectNumber, tblEvent.EventTypeID;

I then queried the Event table again using this query as a filter to get the
related date and the status assigned to that date. (Probably not technically
correct terminology but that's how I conceptualized what I was doing.)

SELECT qLastDate.ProjectNumber,
qLastDate.EventTypeID,qLastDate.MaxOfDateCreated, tblEvent.Date,
tblEvent.DateStatus
FROM qLastDate INNER JOIN tblEvent ON (qLastDate.MaxOfDateCreated =
tblEvent.DateCreated) AND (qLastDate.EventTypeID = tblEvent.EventTypeID) AND
(qLastDate.ProjectNumber = tblEvent.ProjectNumber)
ORDER BY qLastDate.ProjectNumber, qLastDate.EventTypeID;

This gave me the data I needed. If there is a more elegant way to do it, I
appreciate all corrections. But thanks for helping me think!

Bettina May

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org 
> [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Ken Schaefer
> Sent: Monday, January 24, 2005 12:18 AM
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] SQL question
> 
> 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
> -- 
> 
> * * Please support the community that supports you.  * * 
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip 
> Harvester and archives of thelist go to: 
> http://lists.evolt.org Workers of the Web, evolt ! 
> 
>