Min and Max Dates

[Guest]

Very new to Access.

I have a table named "tblOccurrances" which contains some summary info for
related information contained in a table named "tblTimeEvents". Each
occurrance consist of a number of time events. Each time event contains a
unique date in the field "EventDate".
the primary key to tblOccurrances is called OccurID. The same field is the
linking field between the two tables.
I'd like generate a simple query that would result in a data set that simply
had each OccurID with the first and the last date for that occurrance from
the tblTimeEvent.

Trying to figure it out, I've tried to use a simple two-column query to get
the start date. The first column is simply tbleOccurance.OccurID. for the
second column
I entered: Start Date: Min(tblTimeEvents.EventDate)
This generates an error for not using "OccurID" as part of an aggregate
function.

Is this something that can't be done with a query? I was hoping to build
forms or reports off this information, but I wonder if I need to go the other
way and generate the information off reports.

 

[Steve Schapel]

Sludge,

In design view of the query, make it a Totals Query by selecting Totals
from the View menu.

I don't see that the tblOccurance or tblOccurrances or whatever it's
called needs to be included in the query at this stage. Just base the
query on the tblTimeEvents table. Add the OccurID field to the query
design grid, and leave the default entry of 'Group By' in the Totals row
of the grid. Then add the EventDate field twice to the grid, and in one
case enter Min in the Totals row, and Max in the other. This should
give you the first and last dates for each occurrance.

 

[Guest]

That was it! Thanks. I was even able to build the rest of the query after
that bit of info. (After reading your suggestion I was afraid I wouldn't be
able to add the information from tblOccurrance or another table called
tblEmployee. But everything fell into place.)
I've tried to teach myself Access by reading Access 2003 Bible. it
mentioned the totals but I'm afraid it didn't stick when I needed to use it.

 

[Steve Schapel]

Great. Best wishes for the rest of the project,