Duplicating names???

[cheyenne2002]

I have a linked table in my dbase that lists all the information about my members. My second table uses a look up into the linked one to pull out the name and address of certain members who are on committies.

I have a query based on these two tables where I am trying to pull out only those who are on the Board of Directors. I have a problem with two people. They happen to be father and son (with the same last name). They appear twice in the query and I can't figure out why.

The query also picked a member who is NOT on any committee. and if there are two members from the same company it chooses both even if only one is on a committee.

I need help. Can anyone assist in this crazy mess???

Thanks in advance, Sharon (a newbie)

 

[Melagan]

Can you go to SQL view for the query in question and copy-paste the code?

~Melagan
______
"It's never too late to become what you might have been.

 

[cheyenne2002]

Here is the code: (not sure what it all means)

SELECT [First Name] & " " & [Last Name] AS Member, [NPC Comm Members New].[Board of Directors], [NPC Comm Members New].[Board Title], Members.[Company Name], Members.Address, Members.Address1, Members.City, Members.State, Members.Zip, Members.[Business Phone], Members.Extension, Members.[Home Phone], Members.[Mobile Phone], Members.Fax, Members.Email, Members.[First Name]
FROM Members INNER JOIN [NPC Comm Members New] ON Members.[Contact Name] = [NPC Comm Members New].Member
GROUP BY [First Name] & " " & [Last Name], [NPC Comm Members New].[Board of Directors], [NPC Comm Members New].[Board Title], Members.[Company Name], Members.Address, Members.Address1, Members.City, Members.State, Members.Zip, Members.[Business Phone], Members.Extension, Members.[Home Phone], Members.[Mobile Phone], Members.Fax, Members.Email, Members.[First Name]
HAVING ((([NPC Comm Members New].[Board of Directors])=Yes));


Thanks for any help and advise you can give me.
Sharon

 

[Melagan]

I'm not seeing any aggregate functions, like COUNT, SUM, MIN, MAX, AVG, etc., so there really isn't need for a GROUP BY clause; and the HAVING clause needs to be a WHERE clause.

SELECT m.[First Name] & " " & m.[Last Name] AS Member
, NPC.[Board of Directors]
, NPC.[Board Title]
, m.[Company Name]
, m.Address
, m.Address1
, m.City
, m.State
, m.Zip
, m.[Business Phone]
, m.Extension
, m.[Home Phone]
, m.[Mobile Phone]
, m.Fax
, m.Email
, m.[First Name]

FROM Members as m INNER JOIN [NPC Comm Members New] as NPC ON m.[Contact Name] = NPC.Member

WHERE NPC.[Board of Directors]=Yes

Can you share some of the results of this query, including the discrepancies you described above?


~Melagan
______
"It's never too late to become what you might have been.

 

[cheyenne2002]

This is what I get from the code I posted....I only included 4 fields. Hope this makes sense.
---------
Member Board of Directors Board Title Company Name
Alan Smith -1 Board Chair Elect Alan Smith Pool Plastering
Charles Barber -1 Board Member Wylie Plastering Inc.
Dana Anderson -1 Past Board Chair Anderson Poolworks
David Cooke -1 Chair Membership Committee David Cooke Plaster Co., LLC
Glenn Gunderson -1 Board Member Gunderson Pool Plastering
Jana Auringer -1 Co-Chair Research Committee The Pool Lady
Jay Eaton -1 Board Chair Cal Plastering
Jon Temple -1 Board Member Tempool, Inc.
Kirk Chapman -1 Board Member Gemstone Pool Surfaces, Inc
Kirk Chapman -1 Board Member Poolscape Unlimited, Inc.
Kirt Campbell -1 Board Member Bakersfield Pool Plastering
Lalo Garcia -1 Chair National Conference Committee Cen-Tex Plaster
Michael Kohlhas -1 Secretary/Treasurer Leisure Craft Pools
Randy Dukes -1 Board Member Aquavations Corporation
Rey Perales -1 Board Member Quality Pool Plastering
Robert Burkett -1 Co-Chair Research Committee Burkett's Pool Plastering, Inc.
Scott McKenna -1 Chair Technical Advisory Committee Gardner Pool Plastering
Shawn Still -1 Chair Education Committee Olympic Pool Plastering
Skip Hawkins -1 Board Member Kerber Brothers
--------

 

[cheyenne2002]

Taking off the group did not change anything.

 

[cheyenne2002]

Below is part of the table (the non linked one) that I'm pulling info from. Note that I should have 21 people in the report (query) for Board of Directors. I only get 19 now and one is a duplicate.

------
Company FirstName LastName Member Board of Directors Board Title
Apec Waterworks Christopher Miles 0
Applied Materials Technologies Greg Garrett 0
Lehigh Cement Co. Larry Rowland 0
American Concrete Institute Jonathan Dongell 0
Cal Plastering Jay Eaton Jay Eaton -1 Board Chair
Alan Smith Pool Plastering Alan Smith Alan Smith -1 Board Chair Elect
Leisure Craft Pools Michael Kohlhas Michael Kohlhas -1 Secretary/Treasurer
Anderson Poolworks Dana Anderson Dana Anderson -1 Past Board Chair
Gardner Pool Plastering Scott McKenna Scott McKenna -1 Chair Technical Advisory Committee
The Pool Lady Jana Auringer Jana Auringer -1 Co-Chair Research Committee
Olympic Pool Plastering Shawn Still Shawn Still -1 Chair Education Committee
Wylie Plastering Inc. Charles Barber Charles Barber -1 Board Member
Burkett's Pool Plastering, Inc. Robert Burkett Robert Burkett -1 Co-Chair Research Committee
Poolscape Unlimited, Inc. Kirk Chapman Kirk Chapman -1 Board Member
David Cooke Plaster Co., LLC David Cooke David Cooke -1 Chair Membership Committee
Aquavations Corporation Randy Dukes Randy Dukes -1 Board Member
Cen-Tex Plaster Lalo Garcia Lalo Garcia -1 Chair National Conference Committee
Gunderson Pool Plastering Glenn Gunderson Glenn Gunderson -1 Board Member
Kerber Brothers Skip Hawkins Skip Hawkins -1 Board Member
Bakersfield Pool Plastering Kirt Campbell Kirt Campbell -1 Board Member
Quality Builders, Inc. Rey Perales Rey Perales -1 Board Member
Oliveira Pool Plastering George Olivera George Olivera -1 Board Member
Tempool, Inc. Jon Temple Jon Temple -1 Board Member
Leisure Craft Pools Jeffrey Kohlhas Jeffrey Kohlhas -1 Board Member
Magic Marcite Inc. Bill Shrock Bill Shrock -1 Board Member

-----

 

[Melagan]

Who is the person in the query that is NOT a member of any committee? I see all -1 (true) for Board of Directors.

Kirk Chapman is showing up twice because he has two entries in one of your tables. Based on your criteria, your query only cares about one thing - the value for "board of directors" must equal -1. It will pull every single row in your joined tables that has a -1 in that field.

~Melagan
______
"It's never too late to become what you might have been.

 

[cheyenne2002]

I changed something last night which removed the unwanted person, but I'm still missing a few people, Jeff Kolhaus is one example. His father is listed but he is not.

I'm also missing Bill Schrock and George Oliveria.

How can I get the query to only give me one Kirk Chapman, the one with the correct company?

Sharon

 

[Melagan]

For Kirk - delete the record that is incorrect? =)

As for the others, I'd confirm that the data in the fields you're using to link the records across your tables are identical. I believe you're using the person's name as the fields to link by - check across your tables to make sure that those names are both spelled correctly.

ex:

George Oliveria will not match with "George Olveria"

~Melagan
______
"It's never too late to become what you might have been.