[AccessD] Query Help Needed

[David McAfee]

If you're using date, it won't work as both values would be the same.

I usually datestamp everything with NOW() and format the display to only
show the date.

If the records are entered chronologically, and you are certain that people
aren't entering older records after newer records, you can use the PKiD.

That is of course, if you are using an autonumber PK :)

Assuming your PKID is named PKID:


SELECT B.CallNo, B.AssignedDate, B.TechID
FROM (SELECT CallNo, MIN(PKID) AS MinID FROM  tblCallsTechs GROUP BY
CallNo) AS A
INNER JOIN tblCallsTechs AS B ON A.MinID= B.PKID;


On Fri, Mar 13, 2015 at 2: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
>