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