[AccessD] Query Help Needed

[David McAfee]

Yeah, sorry. I work with TSQL more than Access.

This is a working query that I just tried with my data in Access:

SELECT B.*
FROM (SELECT Min([tbl Plans].PlanId) AS MinOfPlanId, [tbl Plans].PlanName
FROM [tbl Plans] GROUP BY [tbl Plans].PlanName) AS A
INNER JOIN [tbl Plans] AS B ON A.MinOfPlanId = B.PlanID


On Fri, Mar 13, 2015 at 2:51 PM, John Bodin <jbodin at sbor.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>