[AccessD] Query Help Needed

[Susan Harkins]

I know GROUP BYs have some interesting rules, but I think -- and it's been
so, so long -- but I think if you use an aggregate such as MAX() in that
date field, it'll return the most recent date. Or, you could sort by the
field and use a Top value property??????

Susan H.

On Fri, Mar 13, 2015 at 5:11 PM, John Bodin <jbodin at sbor.com> wrote:

> Hi David, I am storing the dates using the DATE() and not NOW()
> functions.  I know it is "air code", but I'm a little confused as I tried
> substituting my actual table/field names in but I got a Syntax Error
> (missing operator):
>
> SELECT B.CallNo, B.DateAssigned, B.Technician (SELECT CallNo,
> Min(DateAssigned) AS MinDate FROM YourTable GROUP BY
> CallNo) A  <========(on the letter A is where the cursor ends up)
> INNER JOIN YourTable B ON A.CallNo = B.CallNo And A.MinDate =
> B.DateAssigned
>
> Am I opening my table "tblCallsTechs" up twice in the query grid,
> assigning the alias of A to one of the tables and B to the other?  So my
> partial query looks like:
>
> SELECT B.CallNo, B.AssignedDate, B.TechID
> FROM tblCallsTechs AS A INNER JOIN tblCallsTechs AS B ON A.CallNo =
> B.CallNo;
>
> The Select within the Select is something I've probably done once or twice
> in the past, but not sure how to get that to work.  Thanks for quick
> response.
>
> John
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> David McAfee
> Sent: Friday, March 13, 2015 4:45 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Help Needed
>
> if the time is stored in the date assigned (using NOW() instead of DATE()
> ), you can use a min:
> If you're not storing the time, maybe do a min on the PKID
>
> (Warning Air code)
>
> SELECT B.CallNo, B.DateAssigned, B.Technician (SELECT CallNo,
> Min(DateAssigned) AS MinDate FROM YourTable GROUP BY
> CallNo) A
> INNER JOIN YourTable B ON A.CallNo = B.CallNo And A.MinDate =
> B.DateAssigned
>
>
>
> On Fri, Mar 13, 2015 at 1:37 PM, John Bodin <jbodin at sbor.com> wrote:
>
> > Stuck on this reporting scenario.  I have service calls that are
> > assigned to one or more technicians on a particular day.  If the
> > technician(s) cannot fix the problem on that day, he/they may get
> > assigned again on a following day or another technician may get
> > assigned.  For the particular report, I only want to consider those
> > technicians that were assigned to the call on the first day and
> > exclude any other days the call may have been assigned.  I'm trying to
> > avoid writing out to a temp table and can't figure out the query for
> > the report.  Some sample data would be (I sorted by Call # in below
> example):
> >
> > Call #    Date Assigned    Technician
> > 1234       03/01/2015          Jim
> > 1234       03/01/2015          Tom
> > 1234       03/03/2015          Jim
> > 1234       03/03/2015          Steve
> > 2222       03/01/2015          Bill
> > 3333       03/02/2015          Mike
> > 3333       03/04/2015          Ben
> >
> > Results of query I would like
> > 1234       03/01/2015          Jim
> > 1234       03/01/2015          Tom
> > 2222       03/01/2015          Bill
> > 3333       03/02/2015          Mike
> >
> > Thoughts?  Thx.
> >
> > John
> >
> >
> > --
> > 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
>