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