[AccessD] Query Help Needed

David McAfee davidmcafee at gmail.com
Fri Mar 13 17:51:53 CDT 2015


You still need to be able to figure out which was the original tech for the
call using the temp table.

Or am I missing something?

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

> Looking at my table, I do not have one single field that will do this.  I
> think I'll just resort to writing a little loop to create a temp table and
> run the report off of that.  Was just trying to avoid that.  Will keep your
> suggestions for down the road.    Go out and have a pint and thanks a lot
> for the effort!
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> David McAfee
> Sent: Friday, March 13, 2015 6:06 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Help Needed
>
> 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
> >
> --
> 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