[AccessD] Query Help Needed

[John Bodin]

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