[AccessD] Query Help Needed

John Bodin jbodin at sbor.com
Fri Mar 13 17:57:41 CDT 2015 

It is OK to have two or more techs on the original call date.  I'd want all techs on the first call date to be considered.  After that, any other techs, including the same techs on that call on a later date, those I need to filter out.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Friday, March 13, 2015 6:52 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query Help Needed

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
>
--
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