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 (3) |
Tidak ada komentar:
Posting Komentar