Re: 2nd try, left join

From: Jeff Uchtman (uchtman_at_megavision.com)
Date: 04/09/04 

Date: Thu, 8 Apr 2004 22:32:59 -0500

  Thank Bob. I hope the following is the information needed.

  Upon insertion to the verified table, the Cir_ID number is inserted into the corsponding record. I would like to count the occurances of each Cir_ID and associate then to the name of the Cir_ID listed in the Cir_ID table. The PetACirc, PetBCirc, PetCCirc, and PetDCirc my or may not have all the same Cir_ID.

  I have tried Greg's query:
  SELECT p.Cir_ID, p.First_Name, p.Last_Name,
  COUNT(a.PetACirc) AS ACount,
  COUNT(b.PetACirc) AS BCount,
  COUNT(c.PetACirc) AS CCount
  FROM CirID p
   LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
  LEFT OUTER JOIN Verified b ON p.Cir_ID = a.PetBCirc
  LEFT OUTER JOIN Verified c ON p.Cir_ID = a.PetCCirc
  GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
  ORDER BY p.Cir_ID
  and it seems to loop forever and return results in the millions when the highest result doing the query on Pet column at a time is less then 15000.

  Verified
        ID Last_Name First_Name Middle_Name PetACirc PetBCirc PetCCirc PetDCirc
        1 LESSIG DONNA M 101 100 122 112
        2 ZASTROW SUE D 122 123 112 123
        3 CHRISTENSEN TODD D 122 122 115 122
        4 MCLAUGHLIN ROLAND E 102 144 122 122
        5 MCLAUGHLIN JOLENE F 122 122 122 122
        6 GREENWOOD BRANDON C 122 112 122 122
        7 ALGYA ROBERT A 122 122 169 122
        8 MILLS JON H 122 132 122 122
        9 HULTQUIST MICHAEL J 111 122 102 122
        10 SHEWAN ROBERT L 112 122 100 105

  Cir_ID

        Cir_ID
       Last_Name
       First_Name
       
        100
       Cornelius
       Tony
       
        101
       DeKeyser
       Paul
       
        102
       Andrews
       Mary
       
        103
       Backhaus
       Clint
       
        105
       Brummont
       Roger
       
        106
       Corbino
       Theresa
       
        107
       Cornelius
       Andrew
       
        108
       Cramer
       Paul
       
        109
       Cronin
       John
       

  Result Table I am after Cir_ID First_Name Last_Name ATotal BTotal CTotal DTotal
      100 Tony Cornelius 752 744 736 700
      101 Paul DeKeyser 570 560 550 515
      102 Mary Andrews 35 30 32 31
      103 Clint Backhaus 1484 1477 1466 1463
      105 Roger Brummont 546 533 544 533
      106 Theresa Corbino 1430 1420 1415 1411
      107 Andrew Cornelius 35 30 27 19
      108 Paul Cramer 28 22 25 27
      109 John Cronin 769 777 782 750
      110 Darrell Davis 0 5 0 3

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%23%23SqMCXHEHA.2260@TK2MSFTNGP09.phx.gbl...
> Jeff Uchtman wrote:
>
> > of me. If I can explane further, let me know what I am misssing.
> >
> All I can do is repeat my original reply:
> > You're going to need to provide some sample data and desired results
> > so we can test our solutions before providing them to you.
>
> OK, I'll amend the above to further explain what I am after:
>
> A few rows of sample data (in tabular format) and desired results from that
> sample data (also in tabular format). Only show the relevant columns.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Relevant Pages

  • Re: 2nd try, left join
    ... > Upon insertion to the verified table, ... > when the highest result doing the query on Pet column at a time is ... Am I correct that given the sample data, ... Bob Barrows ...
    (microsoft.public.inetserver.asp.general)
  • Re: Error: Data type mismatch in criteria expression
    ... >> be aware that currency is a numeric datatype. ... (Simpler still would be a saved parameter query, ... >> Bob Barrows ... >> a quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Get Value from Next Record for this record
    ... -some sample data ... "Bob" wrote in message ... >>This might be doable in a query. ... >>MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: How do I give same value as primary key to other field
    ... > I also have a Field called ParentID ... After you run the above query you can get the value by ... Bob Barrows ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Dates between
    ... This is done in the query, ... >> Bob, ... >> Please only reply to this newsgroup. ... >> I do not reply to personal email. ...
    (microsoft.public.access.gettingstarted)