WHERE statement

S

Stephanie

Hi. I'm struggling to get my query to bring back the information I want it to:
Here's the fields with the criteria
Contacts.MemberOption Contacts.MemberStatusID Contacts.MemberOption
1 AND 1 AND 1 OR
1 AND 6 AND 5 OR
1 AND 1 AND 5

And heres the big OR at the end
sqry.GroupName="Board of Directors"

So I have:
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=1)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID)=6) AND ((Contacts.MemberTypeID)=5)) OR
(((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=5))

But can't seem to get the correct information returned when I try to throw
OR sqry.GroupName="Board of Directors"
into the mix. Anyway I try to add the last OR, EVERY record is returned.

I appreciate a little syntax help.
Thanks.
 
M

MGFoster

Stephanie said:
Hi. I'm struggling to get my query to bring back the information I want it to:
Here's the fields with the criteria
Contacts.MemberOption Contacts.MemberStatusID Contacts.MemberOption
1 AND 1 AND 1 OR
1 AND 6 AND 5 OR
1 AND 1 AND 5

And heres the big OR at the end
sqry.GroupName="Board of Directors"

So I have:
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=1)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID)=6) AND ((Contacts.MemberTypeID)=5)) OR
(((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=5))

But can't seem to get the correct information returned when I try to throw
OR sqry.GroupName="Board of Directors"
into the mix. Anyway I try to add the last OR, EVERY record is returned.

I appreciate a little syntax help.
Thanks.
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

WHERE Contacts.MemberOption=1
AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5)

OR sqry.GroupName="Board of Directors")

It may be that you need AND sqry.GroupName="Board of Directors" instead
of OR sqry.GroupName="Board of Directors", in which case the WHERE
clause would look like this:

WHERE Contacts.MemberOption=1 AND sqry.GroupName="Board of Directors"

AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxiZAoechKqOuFEgEQIlpwCggVVJvHiIPfh4A367aEPk4+sq8+AAoO0E
vpJjmtKbZxuT8yZAWZCW7gQd
=dk1g
-----END PGP SIGNATURE-----
 
S

Stephanie

Close- thanks!

I've gone with:
WHERE Contacts.MemberOption=1 AND ((Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=1) OR (Contacts.MemberStatusID=6 AND
Contacts.MemberTypeID=5) OR (Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=5) OR [BOD List Query].[GroupName]="Board of Directors")

I had 2 records returned that should not have been and 1 not returned that
should have been:

Should not be returned:
MemberOption =1, MemberStatusID=1, MemberTypeID=2 (MemberTypeID should only
be 1 or 5)

MemberOption=1, MemberStatusID=2 (Member Status should only equal 1, unless
the person is Board of Directors, which she's not), MemberTypeID=1

Should have been included:
MemberOption=3 (normally should only =1, but Board of Directors trumps all),
MemberStatusID = 1, MemberTypeID=4 (but ditto of BOD trumping all).

Although I can see why the record that should have been included wasn't, I
don't know how to fix it. And I can't see why the 2 records that were
included but shouldn't have been showed up.

I'd appreciate any more WHERE sql suggestions. Thanks!
 
M

MGFoster

Can you state, in English, the criteria? Like "I want all records w/
MemberOption of 1 and if the MemberTypeID = ? then ... etc." It's
usually best to start from basics on a complex Boolean evaluation.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Close- thanks!

I've gone with:
WHERE Contacts.MemberOption=1 AND ((Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=1) OR (Contacts.MemberStatusID=6 AND
Contacts.MemberTypeID=5) OR (Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=5) OR [BOD List Query].[GroupName]="Board of Directors")

I had 2 records returned that should not have been and 1 not returned that
should have been:

Should not be returned:
MemberOption =1, MemberStatusID=1, MemberTypeID=2 (MemberTypeID should only
be 1 or 5)

MemberOption=1, MemberStatusID=2 (Member Status should only equal 1, unless
the person is Board of Directors, which she's not), MemberTypeID=1

Should have been included:
MemberOption=3 (normally should only =1, but Board of Directors trumps all),
MemberStatusID = 1, MemberTypeID=4 (but ditto of BOD trumping all).

Although I can see why the record that should have been included wasn't, I
don't know how to fix it. And I can't see why the 2 records that were
included but shouldn't have been showed up.

I'd appreciate any more WHERE sql suggestions. Thanks!






:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

WHERE Contacts.MemberOption=1
AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5)

OR sqry.GroupName="Board of Directors")

It may be that you need AND sqry.GroupName="Board of Directors" instead
of OR sqry.GroupName="Board of Directors", in which case the WHERE
clause would look like this:

WHERE Contacts.MemberOption=1 AND sqry.GroupName="Board of Directors"

AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxiZAoechKqOuFEgEQIlpwCggVVJvHiIPfh4A367aEPk4+sq8+AAoO0E
vpJjmtKbZxuT8yZAWZCW7gQd
=dk1g
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
S

Stephanie

Maybe that's why I can never figure them out ;-)

My [BOD List Query] brings back all Board Members that show
[GroupName]="Board of Directors" and [GroupEnd] is null. That gives me all
current Board Members. And even if Board Members don't meet any of the other
criteria in the WHERE statement, I still want Board Members. They trump all.

Contacts.MemberStatusID=1 or 6 (that's "Current-Active" or "Lifetime")
Contacts.MemberTypeID=1 or 5 (that's "Handler" or "Local Support")
Contacts.MemberOption=1 (that's a "true" member).

Next after Board Members, I want all members where Contacts.MemberOption=1
AND Contacts.MemberStatusID=1 or 6 AND Contacts.MemberTypeID=1 or 5. That
will give me Board Members no matter what, as well as "true" members that are
have either a Handlers or Local Support membership with a status of
Current-Active or "Lifetime".

Basically what has happened is that I had a nice MemberListQuery and then
discovered that we needed Board Members included in the Member List. Some
Board Members are "true" members (already on the list) and some need to be
added. I want Board Members added to the list if they weren't already on it,
without duplicating a listing if a person is both a "true" member and a Board
Member.

MemberListQuery:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID, Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
(And then I never really got the WHERE statement correct...)

[BOD List Query] sql:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Groups.GroupName, GroupMembers.GMemberEnd, GroupMembers.GroupID
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

I tried a union join but the tables in the 2 queries are different...

Thanks for the help!



MGFoster said:
Can you state, in English, the criteria? Like "I want all records w/
MemberOption of 1 and if the MemberTypeID = ? then ... etc." It's
usually best to start from basics on a complex Boolean evaluation.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Close- thanks!

I've gone with:
WHERE Contacts.MemberOption=1 AND ((Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=1) OR (Contacts.MemberStatusID=6 AND
Contacts.MemberTypeID=5) OR (Contacts.MemberStatusID=1 AND
Contacts.MemberTypeID=5) OR [BOD List Query].[GroupName]="Board of Directors")

I had 2 records returned that should not have been and 1 not returned that
should have been:

Should not be returned:
MemberOption =1, MemberStatusID=1, MemberTypeID=2 (MemberTypeID should only
be 1 or 5)

MemberOption=1, MemberStatusID=2 (Member Status should only equal 1, unless
the person is Board of Directors, which she's not), MemberTypeID=1

Should have been included:
MemberOption=3 (normally should only =1, but Board of Directors trumps all),
MemberStatusID = 1, MemberTypeID=4 (but ditto of BOD trumping all).

Although I can see why the record that should have been included wasn't, I
don't know how to fix it. And I can't see why the 2 records that were
included but shouldn't have been showed up.

I'd appreciate any more WHERE sql suggestions. Thanks!






:

Stephanie wrote:

Hi. I'm struggling to get my query to bring back the information I want it to:
Here's the fields with the criteria
Contacts.MemberOption Contacts.MemberStatusID Contacts.MemberOption
1 AND 1 AND 1 OR
1 AND 6 AND 5 OR
1 AND 1 AND 5

And heres the big OR at the end
sqry.GroupName="Board of Directors"

So I have:
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=1)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID)=6) AND ((Contacts.MemberTypeID)=5)) OR
(((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID)=1) AND
((Contacts.MemberTypeID)=5))

But can't seem to get the correct information returned when I try to throw
OR sqry.GroupName="Board of Directors"
into the mix. Anyway I try to add the last OR, EVERY record is returned.

I appreciate a little syntax help.
Thanks.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

WHERE Contacts.MemberOption=1
AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5)

OR sqry.GroupName="Board of Directors")

It may be that you need AND sqry.GroupName="Board of Directors" instead
of OR sqry.GroupName="Board of Directors", in which case the WHERE
clause would look like this:

WHERE Contacts.MemberOption=1 AND sqry.GroupName="Board of Directors"

AND ((Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=1)

OR (Contacts.MemberStatusID=6
AND Contacts.MemberTypeID=5)

OR (Contacts.MemberStatusID=1
AND Contacts.MemberTypeID=5))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxiZAoechKqOuFEgEQIlpwCggVVJvHiIPfh4A367aEPk4+sq8+AAoO0E
vpJjmtKbZxuT8yZAWZCW7gQd
=dk1g
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
M

MGFoster

Stephanie said:
Maybe that's why I can never figure them out ;-)

My [BOD List Query] brings back all Board Members that show
[GroupName]="Board of Directors" and [GroupEnd] is null. That gives me all
current Board Members. And even if Board Members don't meet any of the other
criteria in the WHERE statement, I still want Board Members. They trump all.

Contacts.MemberStatusID=1 or 6 (that's "Current-Active" or "Lifetime")
Contacts.MemberTypeID=1 or 5 (that's "Handler" or "Local Support")
Contacts.MemberOption=1 (that's a "true" member).

Next after Board Members, I want all members where Contacts.MemberOption=1
AND Contacts.MemberStatusID=1 or 6 AND Contacts.MemberTypeID=1 or 5. That
will give me Board Members no matter what, as well as "true" members that are
have either a Handlers or Local Support membership with a status of
Current-Active or "Lifetime".
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmm... With this re-wording of the criteria, this may be the solution

WHERE
GroupName = "Board of Directors" AND GroupEnd IS NULL
OR (Contacts.MemberStatusID IN (1,6)
AND Contacts.MemberTypeID IN (1,5)
AND Contacts.MemberOption = 1)

My translation of this is:

"If a member is on the Board of Directors and is still an active board
member (GroupEnd IS NULL?), or they have a status of Current-Active or
LifeTime and they have a MemberType of Handler or Local Support and they
are a True Member, then return the record."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxzVyIechKqOuFEgEQLEaQCeP8w4PzGBfBsz0YpQvHYlxBJ07M8AoJTk
HgLgocHneryBFNdFaWyagqjy
=SSL0
-----END PGP SIGNATURE-----
 
S

Stephanie

Hi, there! Your WHERE statement is a vast improvement!

This part of the WHERE statement brings back the correct ture members:
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))

for the first time, I'm getting the correct true member results!!! Thanks.

But when I change to:
WHERE ((([BOD List Query].GroupName)="Board of Directors") AND (([BOD List
Query].GMemberEnd) Is Null)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID) In (1,6)) AND ((Contacts.MemberTypeID) In (1,5)))

All members (true, not true, BOD, not BOD) are showing up. When I look at
the [GroupName] column, ALL members show "Board of Directors" when in
actually there are only a hand full that are BOD. And of course, since all
members are brought back, these members violate the rules of
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))

ARGGH!!!
I don't understand. Thanks for the help and I'd appreciate a bit more if
you have the time. Cheers!


MGFoster said:
Stephanie said:
Maybe that's why I can never figure them out ;-)

My [BOD List Query] brings back all Board Members that show
[GroupName]="Board of Directors" and [GroupEnd] is null. That gives me all
current Board Members. And even if Board Members don't meet any of the other
criteria in the WHERE statement, I still want Board Members. They trump all.

Contacts.MemberStatusID=1 or 6 (that's "Current-Active" or "Lifetime")
Contacts.MemberTypeID=1 or 5 (that's "Handler" or "Local Support")
Contacts.MemberOption=1 (that's a "true" member).

Next after Board Members, I want all members where Contacts.MemberOption=1
AND Contacts.MemberStatusID=1 or 6 AND Contacts.MemberTypeID=1 or 5. That
will give me Board Members no matter what, as well as "true" members that are
have either a Handlers or Local Support membership with a status of
Current-Active or "Lifetime".
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmm... With this re-wording of the criteria, this may be the solution

WHERE
GroupName = "Board of Directors" AND GroupEnd IS NULL
OR (Contacts.MemberStatusID IN (1,6)
AND Contacts.MemberTypeID IN (1,5)
AND Contacts.MemberOption = 1)

My translation of this is:

"If a member is on the Board of Directors and is still an active board
member (GroupEnd IS NULL?), or they have a status of Current-Active or
LifeTime and they have a MemberType of Handler or Local Support and they
are a True Member, then return the record."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxzVyIechKqOuFEgEQLEaQCeP8w4PzGBfBsz0YpQvHYlxBJ07M8AoJTk
HgLgocHneryBFNdFaWyagqjy
=SSL0
-----END PGP SIGNATURE-----
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, now I think we're getting into the area of data integrity and/or
INNER/OUTER JOINs. Data integrity - 'cuz some records may have too much
info indicating that a member could be both a BOD AND not a BOD!
INNER/OUTER JOINs - 'cuz you may be using an INNER JOIN in your query
when you should be using a LEFT/RIGHT (OUTER) JOIN.

Could you post your query's SQL again, and some example records,
indicating which records should be selected & which should not be
selected?

Thanks,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQx4DoYechKqOuFEgEQKgSgCg9oUUc5q1isa0Ibo1e+8DaLezx0oAoJ4U
XWxxyraDPclGBBixvM52Z+ZV
=XLbW
-----END PGP SIGNATURE-----
Hi, there! Your WHERE statement is a vast improvement!

This part of the WHERE statement brings back the correct ture members:
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))

for the first time, I'm getting the correct true member results!!! Thanks.

But when I change to:
WHERE ((([BOD List Query].GroupName)="Board of Directors") AND (([BOD List
Query].GMemberEnd) Is Null)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID) In (1,6)) AND ((Contacts.MemberTypeID) In (1,5)))

All members (true, not true, BOD, not BOD) are showing up. When I look at
the [GroupName] column, ALL members show "Board of Directors" when in
actually there are only a hand full that are BOD. And of course, since all
members are brought back, these members violate the rules of
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))

ARGGH!!!
I don't understand. Thanks for the help and I'd appreciate a bit more if
you have the time. Cheers!


:

Stephanie said:
Maybe that's why I can never figure them out ;-)

My [BOD List Query] brings back all Board Members that show
[GroupName]="Board of Directors" and [GroupEnd] is null. That gives me all
current Board Members. And even if Board Members don't meet any of the other
criteria in the WHERE statement, I still want Board Members. They trump all.

Contacts.MemberStatusID=1 or 6 (that's "Current-Active" or "Lifetime")
Contacts.MemberTypeID=1 or 5 (that's "Handler" or "Local Support")
Contacts.MemberOption=1 (that's a "true" member).

Next after Board Members, I want all members where Contacts.MemberOption=1
AND Contacts.MemberStatusID=1 or 6 AND Contacts.MemberTypeID=1 or 5. That
will give me Board Members no matter what, as well as "true" members that are
have either a Handlers or Local Support membership with a status of
Current-Active or "Lifetime".
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmm... With this re-wording of the criteria, this may be the solution

WHERE
GroupName = "Board of Directors" AND GroupEnd IS NULL
OR (Contacts.MemberStatusID IN (1,6)
AND Contacts.MemberTypeID IN (1,5)
AND Contacts.MemberOption = 1)

My translation of this is:

"If a member is on the Board of Directors and is still an active board
member (GroupEnd IS NULL?), or they have a status of Current-Active or
LifeTime and they have a MemberType of Handler or Local Support and they
are a True Member, then return the record."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxzVyIechKqOuFEgEQLEaQCeP8w4PzGBfBsz0YpQvHYlxBJ07M8AoJTk
HgLgocHneryBFNdFaWyagqjy
=SSL0
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
S

Stephanie

Thanks for hanging in there! I don't think I have a data integrity issue.
My straight query for BOD (without any of the true member issues) returns the
correct data.

Joins. Not at all good at them, innies, outies.... Thanks for the help!
Here's my table setup and my queries so far:

Contacts
ContactID
LastName
MailingAddress1...
MemberStatusID (1 or 6 -that's "Current-Active" or "Lifetime")
MemberTypeID (1 or 5 -that's "Handler" or "Local Support")
MemberOption (1= true member)

GroupMembers
GroupMemberID
ContactID
GroupID
GMemberEnd (this is the ending date of the Contact from a given Group)

Group
GroupID
GroupName (such as "Board of Directors")

BOD List Query:

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Groups.GroupName, GroupMembers.GMemberEnd, GroupMembers.GroupID
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie...Address stuff... Phone stuff... Spouse name... Animal
name... Board Of Directrors and a blank GMemberEnd date and GroupID=5 (BOD).

Member List Query brings back true members that meet the requirements for
MemberStatusID and MemberTypeID (so far I'm skipping BOD members):

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID, Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie Address stuff... Phone stuff... Spouse name... Animal
name... MemberOption=1, MemberStatusID=1, MemberType=1.

As you can see, I have Susie Anderson as both a true member and a Board
Member.

Here's the query where I tried to pretend that I knew what I was doing and
there is no join involved so that's probably not good.
Member List Query Criteria Combo:
SELECT DISTINCT Contacts.ContactID, [LastName] & ", " &
Nz([NickName],[FirstName]) AS [Member Name], Contacts.MailingAddress1,
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address,
Contacts.HomePhone, [WorkPhone] & (" x"+[WorkExtension]) AS WorkPhExt,
Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Contacts.MemberOption, Contacts.MemberStatusID, Contacts.MemberTypeID,
[BOD List Query].GroupName, [BOD List Query].GMemberEnd
FROM Contacts, [BOD List Query]
WHERE ((([BOD List Query].GroupName)="Board of Directors") AND (([BOD List
Query].GMemberEnd) Is Null)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID) In (1,6)) AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

This one brings back all Contact records even if they don't meet any of the
criteria and is the slowest query ever.

Can I be salvaged? Thanks for your help!
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ahh... Actually, it looks like you have to have 2 different queries to
get what you want, 'cuz one query gets Membership info & the other query
gets Group association info. These are 2 distinct things and to combine
them would be like trying to match apples and oranges. If you wanted to
combine them in one output list you could get rid of:

Groups.GroupName,
GroupMembers.GMemberEnd,
GroupMembers.GroupID

from the BOD list query and

MemberStatus.MemberStatusID,
MemberType.MemberTypeID,
Contacts.MemberOption

from the Member list query and UNION the 2 queries. The above columns
are really part of the criteria and should only be included if you are
going to ORDER BY or GROUP BY them (IOW, have an subset inside the
result set).

UNION will get rid of duplicates. UNION ALL will show duplicates.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQx9E54echKqOuFEgEQK1OACg+YfKe3tPAnlfGstIlDQKPj6DVSEAnA3I
6jpiFP+gsAKE5eIyp9mx/Bip
=xYjj
-----END PGP SIGNATURE-----
Thanks for hanging in there! I don't think I have a data integrity issue.
My straight query for BOD (without any of the true member issues) returns the
correct data.

Joins. Not at all good at them, innies, outies.... Thanks for the help!
Here's my table setup and my queries so far:

Contacts
ContactID
LastName
MailingAddress1...
MemberStatusID (1 or 6 -that's "Current-Active" or "Lifetime")
MemberTypeID (1 or 5 -that's "Handler" or "Local Support")
MemberOption (1= true member)

GroupMembers
GroupMemberID
ContactID
GroupID
GMemberEnd (this is the ending date of the Contact from a given Group)

Group
GroupID
GroupName (such as "Board of Directors")

BOD List Query:

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Groups.GroupName, GroupMembers.GMemberEnd, GroupMembers.GroupID
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie...Address stuff... Phone stuff... Spouse name... Animal
name... Board Of Directrors and a blank GMemberEnd date and GroupID=5 (BOD).

Member List Query brings back true members that meet the requirements for
MemberStatusID and MemberTypeID (so far I'm skipping BOD members):

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID, Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie Address stuff... Phone stuff... Spouse name... Animal
name... MemberOption=1, MemberStatusID=1, MemberType=1.

As you can see, I have Susie Anderson as both a true member and a Board
Member.

Here's the query where I tried to pretend that I knew what I was doing and
there is no join involved so that's probably not good.
Member List Query Criteria Combo:
SELECT DISTINCT Contacts.ContactID, [LastName] & ", " &
Nz([NickName],[FirstName]) AS [Member Name], Contacts.MailingAddress1,
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address,
Contacts.HomePhone, [WorkPhone] & (" x"+[WorkExtension]) AS WorkPhExt,
Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Contacts.MemberOption, Contacts.MemberStatusID, Contacts.MemberTypeID,
[BOD List Query].GroupName, [BOD List Query].GMemberEnd
FROM Contacts, [BOD List Query]
WHERE ((([BOD List Query].GroupName)="Board of Directors") AND (([BOD List
Query].GMemberEnd) Is Null)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID) In (1,6)) AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

This one brings back all Contact records even if they don't meet any of the
criteria and is the slowest query ever.

Can I be salvaged? Thanks for your help!
Click to expand...
 
S

Stephanie

HUGE lightbulb moment!!! I always wondered about Union queries- I've used
them, but didn't realize how huge they could be. I didn't realize that I
could leave off fields in the SELECT statement but still have them listed in
the WHERE clause.

Most excellent. Thanks for your time and all the help! Cheers!

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ahh... Actually, it looks like you have to have 2 different queries to
get what you want, 'cuz one query gets Membership info & the other query
gets Group association info. These are 2 distinct things and to combine
them would be like trying to match apples and oranges. If you wanted to
combine them in one output list you could get rid of:

Groups.GroupName,
GroupMembers.GMemberEnd,
GroupMembers.GroupID

from the BOD list query and

MemberStatus.MemberStatusID,
MemberType.MemberTypeID,
Contacts.MemberOption

from the Member list query and UNION the 2 queries. The above columns
are really part of the criteria and should only be included if you are
going to ORDER BY or GROUP BY them (IOW, have an subset inside the
result set).

UNION will get rid of duplicates. UNION ALL will show duplicates.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQx9E54echKqOuFEgEQK1OACg+YfKe3tPAnlfGstIlDQKPj6DVSEAnA3I
6jpiFP+gsAKE5eIyp9mx/Bip
=xYjj
-----END PGP SIGNATURE-----
Thanks for hanging in there! I don't think I have a data integrity issue.
My straight query for BOD (without any of the true member issues) returns the
correct data.

Joins. Not at all good at them, innies, outies.... Thanks for the help!
Here's my table setup and my queries so far:

Contacts
ContactID
LastName
MailingAddress1...
MemberStatusID (1 or 6 -that's "Current-Active" or "Lifetime")
MemberTypeID (1 or 5 -that's "Handler" or "Local Support")
MemberOption (1= true member)

GroupMembers
GroupMemberID
ContactID
GroupID
GMemberEnd (this is the ending date of the Contact from a given Group)

Group
GroupID
GroupName (such as "Board of Directors")

BOD List Query:

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Groups.GroupName, GroupMembers.GMemberEnd, GroupMembers.GroupID
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie...Address stuff... Phone stuff... Spouse name... Animal
name... Board Of Directrors and a blank GMemberEnd date and GroupID=5 (BOD).

Member List Query brings back true members that meet the requirements for
MemberStatusID and MemberTypeID (so far I'm skipping BOD members):

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID, Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
WHERE (((Contacts.MemberOption)=1) AND ((Contacts.MemberStatusID) In (1,6))
AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

A returned record shows:
Anderson, Susie Address stuff... Phone stuff... Spouse name... Animal
name... MemberOption=1, MemberStatusID=1, MemberType=1.

As you can see, I have Susie Anderson as both a true member and a Board
Member.

Here's the query where I tried to pretend that I knew what I was doing and
there is no join involved so that's probably not good.
Member List Query Criteria Combo:
SELECT DISTINCT Contacts.ContactID, [LastName] & ", " &
Nz([NickName],[FirstName]) AS [Member Name], Contacts.MailingAddress1,
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address,
Contacts.HomePhone, [WorkPhone] & (" x"+[WorkExtension]) AS WorkPhExt,
Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Contacts.MemberOption, Contacts.MemberStatusID, Contacts.MemberTypeID,
[BOD List Query].GroupName, [BOD List Query].GMemberEnd
FROM Contacts, [BOD List Query]
WHERE ((([BOD List Query].GroupName)="Board of Directors") AND (([BOD List
Query].GMemberEnd) Is Null)) OR (((Contacts.MemberOption)=1) AND
((Contacts.MemberStatusID) In (1,6)) AND ((Contacts.MemberTypeID) In (1,5)))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

This one brings back all Contact records even if they don't meet any of the
criteria and is the slowest query ever.

Can I be salvaged? Thanks for your help!
Click to expand...
Click to expand...
 
Top