[AccessD] Min Date in a query

David McAfee davidmcafee at gmail.com
Mon Mar 30 15:17:31 CDT 2009


SELECT
    tblCompany.CompanyID,
    tblCompany.CompanyName,
    tblEvents.EventShowCode
FROM tblEvents
    INNER JOIN tblEventReg
        ON tblEvents.EventID =tblEventReg.EventID
    INNER JOIN tblCompany
        ON tblCompany.CompanyID = tblEventReg.CompanyID
    INNER JOIN (SELECT TOP 1 EventID, MIN(EventStartDate) FROM tblEvents
WHERE EventStartDate > DATE() GROUP BY EventID) C
        ON tblEvents.EventID = C.EventID
WHERE
    tblCompany.AdClass <>"X" AND
    tblEventReg.ParticipantType IN ("Exhibitor", "Exhibitor/NA/PSC") AND
    tblEventReg.PrimaryContact = True AND
    tblEvents.EventName = "JIS"
ORDER BY tblCompany.CompanyName;


On Mon, Mar 30, 2009 at 1:08 PM, William Hindman <
wdhindman at dejpolsystems.com> wrote:

>
> ...only one event on any given date
> ...no, I've not tried Top 1 ...I need the first date of several after
> today's date
> ...the SQL is copied from the QBE
> ...of course its contrived! ...I wrote it! :)
>
> William
>
> --------------------------------------------------
> From: "Max Wanadoo" <max.wanadoo at gmail.com>
> Sent: Monday, March 30, 2009 3:53 PM
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Min Date in a query
>
> >> or return each future event date
> >
> > How do you want to handle multiple events on the same date?
> >
> > Have you tried Top 1 ?
> >
> > Have you also posted the query into the QBE form?  This is very
> contrived.
> > Why don't you break it out a bit?
> >
> >
> > Max
> >
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
> Hindman
> > Sent: 30 March 2009 20:18
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Min Date in a query
> >
> > ...I'm trying to eliminate the user having to select the next event from
> a
> > combo.
> >
> > ...I have an tblEvents with a DateStart field formatted as Date
> >
> > ...from a query, I need to find the first date in that field greater than
> > Date()
> >
> > ...I've tried dozens of variations on Min but they all come back either
> > with
> >
> > an aggregate error or return each future event date
> >
> > ...this is of course where'd with a couple other ANDs
> >
> > INSERT INTO tblExhibitorWeb ( CompanyID, CompanyName, EventShowCode) IN
> > 'F:\JEMS\XPData\jemdataxp.mdb'
> > SELECT tblCompany.CompanyID, tblCompany.CompanyName,
> > tblEvents.EventShowCode
> >
> > FROM tblEvents INNER JOIN (tblCompany INNER JOIN tblEventReg ON
> > tblCompany.CompanyID = tblEventReg.CompanyID) ON tblEvents.EventID =
> > tblEventReg.EventID
> > WHERE (((tblCompany.AdClass)<>"X") AND
> > ((tblEventReg.ParticipantType)="Exhibitor" Or
> > (tblEventReg.ParticipantType)="Exhibitor/NA/PSC") AND
> > ((tblEventReg.PrimaryContact)=True) AND ((tblEvents.EventName)="JIS") AND
> > ((Min([tblEvents]![EventStartDate]))>Date()))
> > ORDER BY tblCompany.CompanyName;
> >
> > ...the error says I can't have and aggregate in the where clause ...help!
> > ...ps I dropped a lot of fields from the sample to shorten it.
> >
> > William
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list