[AccessD] Query Help Needed

[John Bodin]

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