[AccessD] Query Help Needed

[John Bodin]

Yes, I thought the same but that caused a "Reserved Word" error.

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

I think Access SQL likes to see the key word AS when aliasing tables and columns.  Other flavors of SQL are not so picky.

So the line where your arrow is should be ". . . CallNo) AS A . . ."

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

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