Group By and Having

  • Thread starter Thread starter CPutnam
  • Start date Start date
C

CPutnam

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
 
Carol,

I can't get past first base here, I'm afraid. I appreciate the extent
you have gone to, in explaining the situation, and it is great to have
examples, but I can't see what makes 6789 different from 12345 and 3579!
 
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
 
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 said:
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

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
Click to expand...
Click to expand...
 
Well, something happened between Friday evening and Monday morning. Now
this solution isn't working. And, looking at the code that you proposed,
Steve, I think I still need the "group by" and "having" clauses. Otherwise,
it's still just finding ALL the records where the ownerdatabase <> orgkeyid.
I'll keep working on it but if anyone has any ideas, I'd be very glad to
hear them.

Thanks again, Steve and everyone. Carol.

Steve Schapel said:
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

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
Click to expand...
Click to expand...
 
Carol,

Well, looks like I have again lost track of what you really want. On
the basis of the example data you supplied, i.e.

PBR_IFMS_ID Ownerdatabase
3579 650
6789 201
12345 500

PBR_IFMS_ID OrgKeyID
6789 10
3579 10
12345 30
6789 200
3579 200
12345 500
12345 501
3579 600
3579 650

.... the query I gave you returns:

PBR_IFMS_ID Ownerdatabase
6789 201

Isn't that correct?
 
Hi, Steve. I was trying to make this less complicated than reality when I
posted my first question but that may have led us down the wrong road.
Here's what's happening when I use your query: (PBR is the real name of the
Projects table.)

1. When I run your query as a query, it returns 61 records (which is a good
number). Here's the SQL of the query I used:
SELECT PBR.PBR_IFMS_ID, PBR.Ownerdatabase
FROM PBR LEFT JOIN PBR_Org ON (CInt(PBR.Ownerdatabase) =
CInt(PBR_Org.OrgKeyID))
AND (PBR.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID)
WHERE PBR_Org.PBR_IFMS_ID Is Null

However, when I try to go to Design view of the query I get a message that
the expression "CInt(PBR.Ownerdatabase) = CInt(PBR_Org.OrgKeyID)) can't be
represented in Design View".

2. My actual code is trying to insert records into new tables It basically
reads database names and ID numbers from another table, plugs those values
into a SQL statement, creates a new table and inserts the records that the
query returns. BUT when I put the same statement into the VBA code, it
inserts 11183 records into the newly created table. Here is the code I just
used:

strSQL = "SELECT PBR.PBR_IFMS_ID, PBR.Ownerdatabase " & _
"FROM PBR LEFT JOIN PBR_Org ON ((PBR.Ownerdatabase) =
CInt(PBR_Org.OrgKeyID)) " & _
"AND (PBR.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID) " & _
"WHERE PBR_Org.PBR_IFMS_ID Is Null;"


ShowMsg = MsgBox(strSQL, vbOKOnly) 'this is just so I can check the
SQL and correct syntax errors

dbs.Execute "Create Table tblPBRNoOrgOwner (PBRIFMSID Text, Owner
Text)"

strSQLInsert = "Insert INTO tblPBRNoOrgOwner (PBRIFMSID, Owner) " &
strSQL
dbs.Execute strSQLInsert

I'm wondering if the CInt conversion has something to do with this because
that's the only thing I can see that is any different than regular query
expressions.. PBR.Ownerdatabase is representing a Sybase database but looks
like Text in Access. And PBR_Org.OrgKeyID is a number in Access.

I haven't tried converting OrgKeyID to Text; maybe that's the way to go.
But if you have any other ideas, I'd be glad to hear them. Thanks for all
your hard work and sorry if I haven't made myself clear in earlier posts.
Carol.
 
Well, here's an interesting development -- When I just did a CInt conversion
on PBR.Ownerdatabase (instead of PBR_Org.OrgKeyID, my code inserted 61
records into the newly created table! i.e.
strSQL = "SELECT PBR.PBR_IFMS_ID, PBR.Ownerdatabase " & _
"FROM PBR LEFT JOIN PBR_Org ON (CInt(PBR.Ownerdatabase) = (PBR_Org.OrgKeyID)) " & _
"AND (PBR.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID) " & _
"WHERE PBR_Org.PBR_IFMS_ID Is Null;"
Click to expand...
<snip>

Does this make sense? Thanks. Carol.


CPutnam said:
Hi, Steve. I was trying to make this less complicated than reality when I
posted my first question but that may have led us down the wrong road.
Here's what's happening when I use your query: (PBR is the real name of the
Projects table.)

1. When I run your query as a query, it returns 61 records (which is a good
number). Here's the SQL of the query I used:
SELECT PBR.PBR_IFMS_ID, PBR.Ownerdatabase
FROM PBR LEFT JOIN PBR_Org ON (CInt(PBR.Ownerdatabase) =
CInt(PBR_Org.OrgKeyID))
AND (PBR.PBR_IFMS_ID = PBR_Org.PBR_IFMS_ID)
WHERE PBR_Org.PBR_IFMS_ID Is Null

However, when I try to go to Design view of the query I get a message that
the expression "CInt(PBR.Ownerdatabase) = CInt(PBR_Org.OrgKeyID)) can't be
represented in Design View".

2. My actual code is trying to insert records into new tables It basically
reads database names and ID numbers from another table, plugs those values
into a SQL statement, creates a new table and inserts the records that the
Click to expand...
,> query returns. BUT when I put the same statement into the VBA code, it
 
Even more interesting -- The numbers I am getting for my queries looks good
(i.e. 1000 records out of 58801 rather than 11000 out 58801). BUT when I
double check my results (i.e. to make sure that the records in PBR don't
have a record in PBR_ORg where PBR_org.OrgKeyID <> PBR_Ownerdatabase) then I
CAN'T find any records in PBR with the PBR_IFMS_ID that is in my query
result. And that ID number isn't in PBR_Org either.

I am completely stumped... ;-0

Any ideas? Thanks. Carol.
 
Never mind. I've checked out a few more of my tables and I am getting valid
results. My brain must have been having spasms.

Sorry. Carol.
 
Carol,

I am happy to hear that you have made progress, and that persistence
seems to be a winning formula. Please keep us posted on any further
developments :-)
 
Back
Top