[AccessD] Query Help Needed

[David McAfee]

You need to figure out a field that will let you determine order, then use
the query example that I gave you.

HTH
D

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

> Yes, using date.  Records unfortunately are not chronological so the
> PKID's will not correspond to newer records always.  Will keep trying.
> Thanks for the help.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> David McAfee
> Sent: Friday, March 13, 2015 5:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Help Needed
>
> 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
> >
> --
> 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
>