Hi Jane,
I think that this will do it,
SELECT DISTINCTROW qLateReason.CustName, Max(qLateReason.CountOfLateReason)
AS [Max Of CountOfLateReason], Max(qLateReason.LateReason)
AS MaxOfLateReason
FROM qLateReason
GROUP BY qLateReason.CustName
ORDER BY Max(qLateReason.LateReason) DESC;
qLateReason is the earlier query that I posted.
The DISTINCTROW is the key to doing it. It just
takes the first record of the Input Query and as
that is ordered by Max value of LateReason DESC
it gives the result you wanted. It still means
that information is lost where there are matching
counts for any customer.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, "Jane" <jcross@...> wrote:
>
>
> Hi Clive
> I think that you can use 'TOP 1' in a subquery, but I'm struggling to get anywhere with it.
> Thanks for your help.
> Jane
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> >
> > Hi Jane,
> >
> > I was aware that you could get more than one result
> > for a given customer.
> >
> > If you take only one of the multiple values then
> > you are actually losing information.
> > (With the >1 operator you are also losing information
> > but I figured that it was a trivial case and probably
> > not required by you, or whover wants the result).
> >
> > There is a TOP N sql operator that return the first
> > N records but you would have to use it on a per
> > customer basis because if you set N to 1, it returns
> > just one record, so you would have to do it for each
> > customer.
> >
> > I think that the only way that you could do want you
> > want is to write some VBA code to process the recordset
> > of the query you now have and make your choice in the
> > code.
> >
> > John Viescas would know if it is possible with SQL but
> > he is not online so frequently thes days.
> >
> > What are you going to do with result when you get it?
> > If is for a manager then maybe he/she/it could advise.
> >
> > Hope that helps.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Jane" <jcross@> wrote:
> > >
> > > Thanks Clive
> > > That mostly works for what I need. My query is now:
> > >
> > > SELECT [qryYTD Customer Late Reasons].CustomerName, [qryYTD Customer Late Reasons].LateReason, Count(LateReason) AS CountOfLateReason1
> > > FROM [qryYTD Customer Late Reasons]
> > > GROUP BY [qryYTD Customer Late Reasons].CustomerName, LateReason
> > > HAVING (((Count([qryYTD Customer Late Reasons].LateReason))>1));
> > >
> > > But in some cases where there maybe 2 or more reasons equally as popular, so then I'm getting each customer name with both (or all) of the 'equal' reasons. I need it to only return 1 top reason per customer. I'm not really bothered which one, it can be the first alphabetically, but it can only be one, otherwise it'll screw up a subsequent query.
> > >
> > > Many thanks
> > > Jane
> > >
> > > If there are more than 1 top reason
> > > --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> > > >
> > > > Hi Jane,
> > > >
> > > > You need a Select Query along the lines of,
> > > >
> > > > SELECT tCustomer.CustName, tOrders.LateReason,
> > > > Count(tOrders.LateReason) AS CountOfLateReason1
> > > > FROM tCustomer INNER JOIN
> > > > tOrders ON tCustomer.IDCust = tOrders.IDCust
> > > > GROUP BY tCustomer.CustName, tOrders.LateReason
> > > > HAVING (((Count(tOrders.LateReason))>1));
> > > >
> > > > That should give you output looking like,
> > > > CustName, LateReason, CountOfLateReason1
> > > > Arthur Askey, 1, 2
> > > > Charles Chaplin, 4, 2
> > > >
> > > > That shows the late reason as a number but it
> > > > could be replaced by a TextValue.
> > > >
> > > > Also notice the HAVING clause to exclude the
> > > > cases where all the LateReasons are different,
> > > > otherwise you will get a line for every order.
> > > >
> > > > Hope that helps.
> > > >
> > > > Regards, Clive.
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "Jane" <jcross@> wrote:
> > > > >
> > > > > Hi
> > > > > I have an Orders table with CustomerIDFK, OrderNo, LateReason.
> > > > >
> > > > > I'm trying to get the the top (ie most popular) late reason for each customer, using sub queries & I'm really strugging with the syntax.
> > > > > Can anyone help?
> > > > >
> > > > > Many thanks
> > > > > Jane
> > > > >
> > > >
> > >
> >
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar