Re: Group By and Having

From: CPutnam (cputnam1nospam_at_nc.rr.com)
Date: 06/05/04 

Date: Sat, 05 Jun 2004 12:45:36 GMT

Thanks, Steve. Yes, I put the wrong sample data in my original
message...and to think I worked so hard at it...guess that's what I get for
working too many hours in a day. ;-)

I did some quick testing and it looks like your idea worked. And it was so
simple! Thanks very much.

Carol.

"Steve Schapel" <schapel@mvps.org.ns> wrote in message
news:OT2TXOpSEHA.240@TK2MSFTNGP11.phx.gbl...
> Carol,
>
> Going by your description, rather than your example, i.e. I am assuming
> the record:
> 6789
> 201
> ... was included by mistake in your example and should not be there,
> does this work?...
>
> SELECT Projects.PBR_IFMS_ID, Projects.Ownerdatabase
> FROM Projects LEFT JOIN PBR_Org ON (Projects.Ownerdatabase =
> PBR_Org.OrgKeyID) AND (Projects.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID)
> WHERE PBR_Org.PBR_IFMS_ID Is Null
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> CPutnam wrote:
> > Hi, All. I know this type of question has been asked many times and
I've
> > been browsing the newsgroup for about 2 hours trying to figure out how
to
> > solve my particular problem but I've had no luck. So I thought I'd get
> > personal. ;-) Here's what I need and the SQL I've been trying to use:
> >
> > I have one table that lists PROJECTS with a keyField called PBR_IFMS_ID.
> > This table also has a field called Ownerdatabase (among lots of others).
I
> > have another table that has a many to one relationship with the Projects
> > table. It's called PBR_Org. PBR_Org has two fields, PBR_IFMS_ID and
> > ORGKEYID. The two tables are linked via PBR_IFMS_ID. Here's some
sample
> > data:
> > Projects
> > PBR_IFMS_ID
> > Ownerdatabase
> >
> > 12345
> > 500
> >
> > 6789
> > 201
> >
> > 3579
> > 650
> >
> >
> >
> > PBR_Org
> > PBR_IFMS_ID
> > OrgKeyID
> >
> > 12345
> > 501
> >
> > 12345
> > 500
> >
> > 12345
> > 30
> >
> > 6789
> > 200
> >
> > 6789
> > 10
> >
> > 6789
> > 201
> >
> > 3579
> > 600
> >
> > 3579
> > 650
> >
> > 3579
> > 200
> >
> > 3579
> > 10
> >
> >
> >
> > I want my query to group all the records in PBR_ORG according to
PBR_IFMS_ID
> > and then figure out, within each group, where there isn't any record
with
> > PBR.Ownerdatabase = PBR_Org.OrgKeyID. So, for the above data, only the
> > Project with PBR_IFMS_ID = 6789 would be chosen because the others have
at
> > least 1 record where Ownerdatabase = OrgKeyID. I need the query to
return
> > PBR_IFMS_ID and Ownerdatabase (because ultimately I need to create new
> > records in PBR_Org with that information). (What's happening now is
that
> > I'm getting all the other records in PBR_Org where the
> > PBR_IFMS_ID.Ownerdatabase <> PBR_Org.OrgKeyID even if a record exists
within
> > the group where the two values are equal.) Here's my SQL:
> >
> > SELECT PBR.PBR_IFMS_ID, PBR.OwnerDatabase, PBR_Org.OrgKeyID
> > FROM PBR INNER JOIN PBR_Org ON PBR.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID
> > Group By PBR.PBR_IFMS_ID, PBR.Ownerdatabase, PBR_Org.OrgKeyID
> > Having (((PBR.Ownerdatabase) <> CInt([PBR_Org].[OrgKeyID])));"
> >
> > (I had to use the CInt conversion because Ownerdatabase is an Integer
and
> > OrgKeyID is a BigInt (from a Sybase database) and I'm using Access97. I
had
> > to add PBR_Org.OrgKeyID to the Select and the Group By statements
because
> > otherwise I got the "you tried to use ... and it's not part of an
aggregate
> > function".)
> >
> > Please let me know if you have any ideas about how I can get this to
work.
> > Thanks in advance. Carol
> >
> >

Relevant Pages