[AccessD] Query Help Needed

[John Bodin]

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