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